Sunday, April 15, 2012

SqlExpress - enable sa login

For some reason, I like to have ability to login with both windows and integrated authentication. Although, installing SqlExpress by default gives you only the second option. And naturally, some of us do not have Management Studio installed just here and now :)
It has been written enough about this already, so this is just a summary:

 1. Go to registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQLServer (in my case SqlExpress was installed by TFS 11,otherwise look for smth like MSSQL.x under Microsoft Sql Server) and set LoginMode to 2.

2. Start sqlcmd using integrated authentication:
sqlcmd .\SQLEXPRESS -E
ALTER LOGIN sa ENABLE
GO
ALTER LOGIN sa with password='your_sa_password'
GO
exit

3. Restart you SQL server instance, so it picks up changes you made in registry.

Now you can login with sa user, for example:
sqlcmd .\SQLEXPRESS -U sa -P your_sa_password

3 comments: