Monday, October 13, 2008

SQL Express 2005 - changing sa password from osql command

I installed MOSS 2007 on stand alone system, and by default it installed with SQL Express 2005. Now I wanted to connect to it from osql command using 'sa' account, I tried to connect to it using sa and 'null' password but failed with error 'Login failed for user 'sa''.
I googled and found very interesting support article here.

The steps to change the 'sa' password from command prompt is given here.
1. Goto Command Prompt.
2. Type: osql -E -S servername\instancename [and press Enter]. This option will work only if you are logged on as administrator on the machine or using the account that you used while installing sharepoint. Otherwise this option will not work. It worked for me because I am the administrator on my machine and same account was used to install MOSS.
3. After successfully connected to SQL instance you will see sql prompt as ">".
4. Type:
1>sp_password @old=null, @new='pass123#$', @loginame='sa'
2>go
1>
Important: @loginame (this is correct, there is no double "n" in login name... )
You must see 1> once password is changed successfully. Quit from windows authentication, and try connecting using the new 'sa' password.
1>quit
C:\>
C:\>osql -S servername\OfficeServers -U sa -P pass123#$ [and Press Enter]. You should be able to see the SQL Prompt which is:
1>

Hoping it is useful for someone as it was for me.