Modifying SQL Server to operate under Windows authentication + SQL...

[StevenK]

We currently have a SQL Server 2005 installation running on a client site.
We've recently encountered some issues (which we're still trying to resolve) whereby when we use the 'DOMAIN-NAME\Administrator' profile on the server - we are unable to add some user groups as logins via the SQL Server Management Studio tool as we'd expect to be able to.

Given this fact I was wondering whether or not we could change the SQL Server to now make use of both Windows Authentication and SQL Server authentication.
Currently it is set to use only Windows authentication.

Is this something that can be changed?
In which case will we be provided with the option to supply a password for the 'sa' profile?

Thanks in advance.

 

[gmmastros]

There is already a password for sa. That happened at the time you installed SQL Server.

To allow SQL Authentication....

Open SQL Server Management Studio
Right Click server name -> Properties
Click Security (Near upper left corner)
For 'Server Authentication', select 'SQL Server and Windows Authentication'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StevenK]

How can I tell what this password for the 'sa' user is?

If I change this security level will it have any impact on the existing security?
I don't want to make this change if it then causes problems for the domain users accessing the various databases - as given access to via an associated user group.

Thanks again.
Steve

 

[gmmastros]

>> How can I tell what this password for the 'sa' user is?

You can't. Period.

You can change it (but probably shouldn't).

To change it:
1. You must log in to the server with a Windows Authentication user account that is associated with system administrator role.

2. Then, in the object explorer, drill down to. Server -> Security -> Logins. Right click sa, then click properties.

3. On the properties form, you can change the password for sa.

This is not necessarily recommended. My suggestion would be to create a new login that has 'system administrator' priveleges. Then, you can log in with that account and be able to do anything that sa can do. You should not use a system administrator account to log in to an application. You should create another account for that. To simplify things, you could make the 'ApplicationUser' account NOT be a system administrator, but you could make it a DB_OWNER for the database.

>> If I change this security level will it have any impact on the existing security?

No. Changing the authentication mode to allow SQL Server authentication will not affect, in any way, user that connect with Windows Authentication.


Make sense.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[StevenK]

I'm unable to create another account with the one I'm logged on as (albeit it's the DOMAIN-NAME\Administrator profile).
I get a message indicating that I don't have the authorisation to do this based on the account I'm logged in under.
This is the reason I was hoping to enable the use of SQL Server authentication and then log in as 'sa' to resolve any issues like this....