Changing authentication mode and enabling sa using osql

Changing authentication mode and enabling sa using osql Few days back, I tried to install SQL Server Management studio express in my machine, because of some strange reasons, it is getting rollbacked everytime. I don’t know why I am getting that error. Then for some development purpose I have to use sa, or sql authentication in SQL 2005 instead of Windows. If I do have SQL Server Management studio, it is pretty easy job. But using osql and registery settings we can achive the same.

1. Changing SQL Server authentication mode. Note: Backup registry before making any changes.
   1. Open Registry editor using RegEdit command.
   2. Goto the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer, locate a subkey with name “LoginMode”.
   3. If LoginMode subkey is 1, then the SQL Server is confingured to Window authentication, and if it is 2 then it is Mixed mode authentication.
   4. Go to services and stop all the sql server related services, before making the change.
   5. Double click on the LoginMode subkey, in the DWORD Editor dialog, set the value as 2.
   6. Restart all the SQL related services

   This procedure will change the authentication mode to mixed mode, so that we can use “sa” user for login. But by default “sa” may not be enabled.

2. Enabling sa account
   1. Go to command promprt type “osql -S localhost\SqlExpress -E”
   2. This will authenticate you with windows authentication, to the local sql express. You will get 1> sign for accepting the Sql commands
   3. You need to give sa a stong password because of security reasons. You can do this by this was “sp_password @old = null, @new = ‘complexpwd’, @loginame =’sa’; ” and type “go”
   4. Type “ALTER LOGIN sa ENABLE” and “GO”, will enable the sa account.
   5. Type quit, and try login using sa, like this “osql -S localhost\SqlExpress -U sa -P mypassword”. If everything worked fine, you will get a prompt 1>

You can also get information on these link from Microsoft.

1. How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition
2. How to: Change Server Authentication Mode