SQL Server has two types of authentication namely,
- Windows authentication
- Mixed mode (Windows and SQL Server authentication).
If the Mixed mode authentication is not enabled on the server, there will be some issues.
- could not connect using SQL Server Authentication
- The ‘sa’ account will be disabled.
For security reasons, the ‘sa’ account is usually disabled, as it is a well-known SQL server account and it’s often targeted by malicious users. And so, be thoughtful while enabling the ‘sa’ account. And it’s recommended that you use a strong password.
Enable mixed-mode authentication:
- Login into the server using Windows authentication.
- Right-click your server and go to Properties, then select Security.
- Under the Server Authentication section, you will find that the “Windows Authentication Mode” radio button is selected.
- So, your work is to select the “SQL Server and Windows Authentication Mode” radio button.
- Then click Ok.
- Right-click the server again and select “Restart”
- That is it! You have now enabled mixed-mode authentication on your server.
Enabling the disabled account.
- Login using Windows authentication.
- Go to Security, then log in. You will find the disabled account with the small red arrow beside them
- Right-click the login name and go to Properties.
- You should see something like this with the “Disabled” radio button checked.
You need to check the Enabled radio button like this
Restart the server.
Another easier way of enabling ‘sa’ account and setting a password using a query as shown below.
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = ‘you new strong password’;
That’s it! You have Mixed mode authentication and account enabled.