Over the time, I have been using Windows Authetication to connect to my MSSMS (Microsoft Sql Server Management Studio). And since during installation I had not enabled Mixed mode authetication on the server, I got some issues.
- I could not connect using Sql Server Authetication
- The ‘sa’ account was disabled.
For security reasons, the ‘sa’ account is usually disabled, as it is a well known SQL server account and its often targeted by malicious users. And so, be thoughful while enabling the ‘sa’ account. And its recommended that you use a strong password.
I thought to summarize the short steps I gathered on the internet into one post. So let’s go.
Enable mixed mode authetication:
- Login in to the server using the Windows authetication.
- Right click your server and go to Properties, the select the Security
- Under Server Authetication section, You will find that “Windows Authetication Mode” radio button selected.
- So, your work is to select “Sql Server and Windows Authetication Mode” radio button
- Then click Ok
- Right click the server again and select “Restart“
- That is it! You have now enabled mixed mode authetication on you server.
Enabling the disabled account.
- Login using windows authetication
- Go to Security, then Login. You will find the disabled account with the a small red arrow beside them as below
- 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 ; GO ALTER LOGIN sa WITH PASSWORD = 'you new strong password' ; GO
Thats it! You have Mixed mode authetication and account enabled .