Change Authentication Mode on SQL Server 2012 Express

By default configuration, SQL Server allows only Windows authentication mode. However, if you have some legacy or custom application that needs to access SQL Server using ‘sa’ account, you have to change authentication mode on SQL Server to mixed mode (SQL Server and Windows authentication mode).

This post shows you how to change authentication mode on SQL Server 2012 Express by using SQL Server Management Studio (manually) and also by using T-SQL script.

Section

1. Change Authentication Mode manually
2. Change Authentication Mode using T-SQL

Step-by-step to change authentication mode on SQL Server 2012 Express

Change Authentication Mode manually

1. Open SQL Server Management Studio and connect to the SQL Server.
   
2. Right-click on server name and select Properties.
   
3. On Server Properties, select Security page and choose SQL Server and Windows Authentication mode on Server authentication and click OK.
   
4. You will see a pop-up window:

   Some of your configuration changes will not take effect until SQL Server is restarted.

   Some of your configuration changes will not take effect until SQL Server is restarted.

   Click OK to continue.
   

5. Right-click on the server name and select Restart to restart the SQL Server service.
   Note: If you’re connecting to remote SQL Server Express (not locally), the restart button will be disabled because SQL Server Agent isn’t available in Express edition so you have to restart the SQL Server service manually.
   
6. You will see a pop-up window:

   Are you sure you want to restart the MSSQL$SQLEXPRESS service on SQL01?

   Are you sure you want to restart the MSSQL$SQLEXPRESS service on SQL01?

   Click Yes for the confirmation.
   

7. On Service Control, the SQL Server service is restarting.
   
8. Now let’s check status of the ‘sa’ account.
   Expand server name -> Databases -> Security -> Logins -> right-click on sa and select Properties.
   
9. On Login Properties – sa, select Status and set Login to Enabled.
   
10. Select General page, change password of sa account if you would like and also other options (whether to enforce password policy and expiration). Once you’re finished, click OK.
    
11. Now let’s try to reconnect to the SQL Server, but this time change authentication to SQL Server Authentication with Login as ‘sa’ account.
    
12. Voila. You have connected to the SQL Server using ‘sa’ account.
    

Back to Section

Change Authentication Mode using T-SQL

1. You can use T-SQL script to change authentication mode of SQL Server. Connect to SQL Server and click New Query and type script below:

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

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

   Note: If you want to change to Windows Authentication mode, simply modify 2 to 1.
   

2. Click Execute to run the script. If there is no error, you should see a green icon at the bottom of output window.
   
3. You also need to restart SQL Server service. However, you cannot do that from T-SQL script. You can restart the service from command prompt. The command prompt may need to be elevated, see … for more information. The default command to start/stop SQL Server 2012 Express service would be:

   net start msssql$sqlexpress net stop msssql$sqlexpress

   net start msssql$sqlexpress net stop msssql$sqlexpress

   For SQL Server 2012 standard edition,

   net start msssqlserver net stop msssqlserver

   net start msssqlserver net stop msssqlserver

   Note: This is the default service name. If you have customize environment, the service name may varies, hence need to be modified from the above example.
   

Back to Section