SQL Server has two types of authentication namely,

  1. Windows authentication
  2. Mixed mode (Windows and SQL Server authentication).

If the Mixed mode authentication is not enabled on the server, there will be some issues.

  1. could not connect using SQL Server Authentication
  2. 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:

Enabling the disabled account.

SQL Server login failed for user. Reason: The account is disabled (Microsoft SQL Server, Error: 18470)
You need to check the Enabled radio button like this

SQL Server login failed for user. Reason: The account is disabled (Microsoft SQL Server, Error: 18470)
Restart the server.
Another easier way of enabling ‘sa’ account and set a password using a query as shown below.

ALTER LOGIN sa ENABLE;

GO

ALTER LOGIN sa WITH PASSWORD = ‘you new strong password’;

GO

That’s it! You have Mixed mode authentication and account enabled.

2 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *