How to connect to SQL Express 2005/2008/2008 R2 using SA account?

By default Microsoft will disable SA account and only Windows Authentication Mode is enabled. So when you try to connect to SQL Express instance using sa account it will not work. Also by default SA account is disabled in SQL Express instance

There are 2 things which we need to do to gain access using SA account

1. Enable SA account
2. Change authentication mode from Windows only to Mixed mode

And there are 2 ways to achieve this

1. GUI
1. Enable SA account

                                                               i.      Download SSMS Express for SQL Server Express (http://www.microsoft.com/downloads/)

                                                             ii.      Connect to SQL Express instance MACHINE\INSTANCE

                                                            iii.      Expand Security tab

                                                            iv.      Double click on SA account

                                                              v.      Under General page type in new password

                                                            vi.      Confirm the password

                                                           vii.      On Status page Enable the account

                                                         viii.      Click OK

2. Change authentication mode

                                                               i.      Right click on instance name in SSMS

                                                             ii.      Select Security page

                                                            iii.      Under Server authentication select “SQL Server and Windows Authentication mode”

                                                            iv.      Click OK

                                                              v.      Go to services.msc and restart SQL Express service

3. Connect using SA by SQLCMD or OSAL

                                                               i.      SQLCMD -S MACHINE\INSTANCE -U sa -P password

                                                             ii.      Osql –S MACHINE\INSTANCE –U sa –P password

2. Programmatically
1. Connect to SQL Express instance using NT authentication

                                                          i.      On command prompt use SQLCMD -S MACHINE\INSTANCE –E

2. Enable SA account

                                                          i.      Run following to change password and enable SA account
USE [master]

GO

ALTER LOGIN [sa] WITH PASSWORD=N'P@s$w0rd'

GO

ALTER LOGIN [sa] ENABLE

GO

3. Change authentication mode to mixed

                                                          i.      Using SQLCMD

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

GO

4. Restart services for SQL express instance
5. Connect to SQL express using SA

                                                               i.      SQLCMD -S MACHINE\INSTANCE -U sa -P password

                                                             ii.      Osql –S MACHINE\INSTANCE –U sa –P password

As a security practice Microsoft disabled SA and hope you find this article helpful in enabling and using it.