Wednesday, June 8, 2011

SQL Server Authentication enabling using Microsoft SQL Server Management Studio

17 comments  

If you install the Microsoft SQL Server using Windows Authentication mode, the "sa" account is disabled by default. So if you plan to use SQL Server Authentication, you have to enable the "sa" account. This tutorial tells you how to enable the "sa" account.

1. First, Login to the SQL Server Management Studio using Windows Authentication. Right-click on the database instance, and go to Properties.

2. Then on Properties page, click on Security and select SQL Server and Windows Authentication mode, and click on OK to close the Server Properties page.

3. Now you will get dialog box saying that you should restart your SQL Server to take the changes take effect. Is is not done yet, you have to done one more thing to enable the "sa" login.

4. Now expand Security folder and go to Logins. You can see the "sa" account is disabled when you install SQL Server using Windows Authentication mode.

5. Then right-click on the "sa" account and go to Login Properties. There you can set a password for the "sa" account.


6. Click on the Status page. There you can see the "sa" account is disabled by default. Click on the Enabled button to enable it. Then click on Ok to close the "sa" Login Properties.

Now "sa" account is enabled and you can login to the SQL instance using the "sa" account after restarting the SQL Server.

Note: SQL Server service needs to be restarted to make this change effective.

17 comments:

  1. AchiniNovember 5, 2011 at 11:17 PM

    useful article aiya...

    ReplyDelete
  2. Saranga RathnayakeNovember 6, 2011 at 10:46 AM

    @ Achini,
    Thanks for your comment nangi...

    ReplyDelete
  3. AnonymousJanuary 10, 2012 at 10:50 PM

    Well Job...!!!

    Really Nice Article...!!!!

    ReplyDelete
  4. RajaMay 1, 2012 at 8:55 AM

    Thank you so much, very useful article

    ReplyDelete
  5. rajkumarSeptember 6, 2012 at 12:25 PM

    i have done this one .. but while again am entering the SQL server authentication username and password field is empty.. again it won't entering.. What i have to do reply please..

    ReplyDelete
  6. ArslanDecember 11, 2012 at 6:30 PM

    Work well thanx

    ReplyDelete
  7. LenderboyDecember 17, 2012 at 9:51 PM

    Lost an entire weekend trying to get a test SQL server to work. This was the first site that enabled me to remote. Thank you for taking the time to make this available!

    ReplyDelete
  8. AshwiniApril 1, 2013 at 2:22 PM

    Thanks a lot. Its helped me :)

    ReplyDelete
  9. RiteshApril 6, 2013 at 3:08 PM

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to NITESH-PC\SQLEXPRESS.

    ------------------------------
    ADDITIONAL INFORMATION:

    Property Login was not set. (Microsoft.SqlServer.ConnectionInfo)

    getting this error.After doing the above activity still not able to login in SQL Server Authentication.Kindly help me..

    ReplyDelete
  10. RiteshApril 6, 2013 at 3:09 PM

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to NITESH-PC\SQLEXPRESS.

    ------------------------------
    ADDITIONAL INFORMATION:

    Property Login was not set. (Microsoft.SqlServer.ConnectionInfo)

    getting this error.After doing the above activity still not able to login in SQL Server Authentication.Kindly help me..

    ReplyDelete
  11. UnknownJune 3, 2014 at 4:19 PM

    this is great, i was searching for this info to train my students and it really worked!...
    thank you...
    Zanzibar University - Dept. of BIT ............

    ReplyDelete
  12. UnknownJune 3, 2014 at 4:20 PM

    This is more helpful, thank you.....
    Zanzibar University, Dept. of IT......

    ReplyDelete
  13. UnknownAugust 5, 2015 at 4:03 PM

    Can't connect using IP, but machine name it's working

    ReplyDelete
  14. indraOctober 8, 2015 at 12:04 PM

    Useful

    ReplyDelete
  15. AnonymousMay 9, 2017 at 12:41 PM

    thanks....article was very helpful while enabling SQL Server Authentication....

    ReplyDelete