- September 6, 2013
- Posted by: Anjana V K
- Category: Technology
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 in to the server using the Windows authentication.
- Right click your server and go to Properties, the select the Security.
- Under Server Authentication section, you will find that “Windows Authentication Mode” radio button selected.
- So, your work is to select “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 you server.
Enabling the disabled account.
- Login using windows authentication.
- Go to Security, then Login. You will find the disabled account with the a small red arrow beside them
- Right click the login name and go to Properties.
- You should see something like this with “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 set a password using 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.