SQL Server - Change Authentication mode from Windows to Sql Server Authentication

During SQL Server installation, the wizard allows to select the authentication mode. At that time you can pick the required authentication scheme as per your needs.

In case if you have selected the Windows Authentication, the server will not configure the SQL Authentication mode and will not setup the default 'sa' account.

At later time if you want to enable the SQL Authentication, following steps will be required:

1. Enable SQL Server Authentication Mode:

   • In SQL Server Management Studio Object Explorer, right-click the Server node, and then click Properties.

     

   • On the Security page, under Server authentication, select SQL Server and Windows Authentication mode. Then click OK.

     
   • When it asks you to restart the SQL Server, click OK.

2. Enable Existing Login account sa:

   • By default, the sa account remains disable (if Windows Authentication mode is selected during SQL Server installation).

     First step is to enable sa login.

     ALTER LOGIN sa ENABLE ;
     GO
     

   • Second step is to set strong password for login sa.

     ALTER LOGIN sa WITH PASSWORD = '[enter-strong-password-here]';
     GO