Sometimes when you install SQL Express 2005 as a component of another product, that application installation will configure the environment, effectively restricting your access to the SQL database. This is primarily due to the application setting the SA password and not telling you what it is.
With SQL Express 2005, the new command line is sqlcmd, which supposedly replaces osql.
Resetting the SA password
If you need access to the database, you can reset the SA password using the following commands from a command prompt (cmd):
sqlcmd –S SQLSERVER\INSTANCE
Once in the interface for sqlcmd, which is represented by a 1>, type the following on separate lines:
- sp_password @new = ’newpassword’, @loginame = ‘sa’
- go
- exit
Just so you know, the @loginame procedure does have only one “n”.
Unlocking the SA user
If you have tried to access the database too many times with the wrong password, the SA account may be locked out. Do the following to unlock the account from a command prompt (cmd):
sqlcmd –S SQLSERVER\INSTANCE
Once in the interface for sqlcmd, which is represented by a 1>, type the following on separate lines:
- ALTER LOGIN sa WITH PASSWORD = ‘newpassword’ UNLOCK
- go
- exit
Test It
You can test the user account, SA, by trying to connect with the SA account from whatever program you are using, installing, et cetera, or you can use OSQL to attempt a connection. From a command prompt (cmd), type the following:
osql –U sa –S SQLSERVER\INSTANCE
Enter your password
At the 1> prompt, type exit
Links
Microsoft has more information about the topic covered in this article at: http://support.microsoft.com/kb/322336
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=306429&SiteID=1
IT is not just a job but also a passion. Everything I have accomplished, both personally and professionally, has been generally entertaining, bordering on fun. Some of my projects, such as working with SharePoint Services workflow actions in Visual Studio or building a custom iSCSI SAN using the OpenSolaris, ZFS and COMSTAR, has been quite rewarding. You may think nerd...I think developing a new trend!
Good information. It help me resolve a locked “sa” account in an instance with no Management Studio on it.
Thanks
I see questions about resetting the sa password and enabling the account quite often.
If you are not comfortable with scripts, this can be done with a few simple clicks.
1) if you don’t have it yet, download sql 2005 management studio express
2) login as a windows administrator using win. integrated auth.
3) go to security, set a new sa password
4) uncheck ‘disabled’ in status.
Peter,
To be honest I am not certain why I chose to use osql. This was something I had needed a few years back; I am going to speculate that the Express of SQL Management Studio was not available or I had no idea it was available. Thank you for the tip.
-Aaron