Changing from NT only authentication to mixed mode

[jkhilton]

Hello again,

Can somebody tell me if there is any danger in changing from Windows NT only authentication to Mixed mode authentication on a live database in SQL2000.

I would like to change the way we authenticate to SQL, but at present we are only NT and I dont want to change it over and then find out that the permissions on the NT accounts that are set change.

I know how to change the method, but I can't find any info on whenther it is safe to do on a live database.

Thanks

John Hilton
(Struggling to learn SQL!!)

 

[osjohnm]

Hi

There are two authentication methods Windows and Mixed Mode.
Mixed mode supports SQL authentication and Windows authentication.

I don't think that there will be any issues as the NT users will still be able to connect and you will be able to setup sql logins. The sql logins will be th eonly major difference.

The only problem will be the 'SA' account's password. Windows authentication sets the password to 'NULL' and once you have changed it to Mixed Mode you can't set a sa password.

Before you change the authentication set the sa password using sp_password while still using windows authentication:

exec sp_password 'NULL', 'yourpassword', 'sa'

Then change the authentication mode.

Here is the Microsoft articel addressing this issue:

http://support.microsoft.com/default.aspx?scid=kb;en-us;274773

Supposedly this was fixed in Service Pack 1 but rather do it the longway round then to end up with a blank sa password.

Do you have a dev server to test this on first? Just to be safe. I have never needed to do this since we always use mixed mode but if you can't find any articles on the net warning you about this then there shouldn't be a problem.
If you can test it first then do so.

Hope this helps

John