How to change from Windows Authentication to SQL Server Authentication for SQLExpress 2005

While installing SQL Express, we have to specify Sql Authentication mode and specify password for sa otherwise Windows Authentication mode being enabled (it is a default mode). To change mode we have to perform the following steps to change to SQL Server Authentication, using the sa (system administrator) account

1) Enable SQL Server Authentication by executing: "ALTER LOGIN sa ENABLE", or by using the Microsoft SQL Server Management Studio Express application.

2) Change/set the password for the sa account by executing: "ALTER LOGIN sa WITH PASSWORD = 'yourpassword'"

3) Stop the SQL Server service

4) Open regedit, go to the "HKLMSoftwareMicrosoftMSSqlserverMSSqlServer" entry and set the LoginMode value to 2.

goto start->run-> type regedit
goto search window find LoginMode
it will show in sqlserver entry set LoginMode 2

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServerLoginMode 2

5) Restart the SQL Server service

No comments: