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.
    Connect to SQL Server using SQL Server Management Studio
  2. Right-click on server name and select Properties.
    Select Properties of SQL Server
  3. On Server Properties, select Security page and choose SQL Server and Windows Authentication mode on Server authentication and click OK.
    Select SQL Server and Windows Authentication mode
  4. You will see a pop-up window:
    Some of your configuration changes will not take effect until SQL Server is restarted.

    Click OK to continue.
    SQL Server need to restart

  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.
    Restart SQL Server Service
  6. You will see a pop-up window:
    Are you sure you want to restart the MSSQL$SQLEXPRESS service on SQL01?

    Click Yes for the confirmation.
    Restart SQL Server Service Confirmation Dialog

  7. On Service Control, the SQL Server service is restarting.
    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.
    Open "sa" Properties
  9. On Login Properties – sa, select Status and set Login to Enabled.
    Set Login to Enable
  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.
    Change Password of sa account
  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.
    Connecting to SQL Server using SQL Server Authentication mode
  12. Voila. You have connected to the SQL Server using ‘sa’ account.
    Connected to SQL Server using the "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

    Note: If you want to change to Windows Authentication mode, simply modify 2 to 1.
    Use T-SQL Script to Change Authentication mode on SQL Server

  2. Click Execute to run the script. If there is no error, you should see a green icon at the bottom of output window.
    Execute T-SQL Script
  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

    For SQL Server 2012 standard edition,

    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.
    Restart SQL Server Service from Command Prompt

Back to Section

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.