Sunday, July 4, 2010

How to change from Windows Authentication to SQL Server Authentication (2005)

Durng installation of SQL Server 2005 i did not care for SQL Server Authentication and installed it Windows Authentication.
And later other users in the network could not use it for the guest account due to some problem.

Here are the steps if you need to add SQL Server Authentication also for SQL Server.


1) Run the following query to enable SQL Server Authentication using MS SQL Server Management Studio (after login as windows authentication)

"ALTER LOGIN sa ENABLE".

2) Set the password for the sa account 
 "ALTER LOGIN sa WITH PASSWORD = 'sa1234"
Password should be strong enough else it will not be accepted and query will fail.

3) Stop the SQL Server service
 Right click on My Computer -> Manage , Computer Management window will open.
Select Service and Applications -> Services -> all the services which start from SQL and stop them.

4) Change in registry
click on Start  -> Run and Enter regedit press enter.
Registry Editor will be opened
Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
and change the value to 2 (Hexadecimal), if its not 2.


5) Restart all services of SQL Server 2005 or restart your machine.

No comments:

Post a Comment