Today, we will riffle through a very simple, yet common issue – How to unlock a locked “sa” login?
It is quite a common practice that SQL Server is hosted on a separate server than application server. In most cases, SQL Server ports or IP are exposed to the web, which makes them risk prone. For hackers, System Admin login “sa” is the preferred account which they use for hacking. In fact, a majority of hackers try to hack into SQL Server by attempting to login using “sa” account. Once hackers gain access to server using “sa” login, they get a complete control over the SQL Server.
Hackers apply Brute Force method to hack “sa” login. Brute Force method is an attempt to guess a password using every possible combination. Be it in your machine where SQL Server is hosted or in your domain, if you have a policy setting that disables any account after a certain number of unsuccessful attempts, it will also disable all your SQL Server logins including “sa” login. In this scenario, SQL Server will display the following error:
msg: 18486
Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.
Fix/Solution/Workaround:
1) Disable the policy on your system or on your domain level. However, this may not be the most appropriate option as it will adversely affect your security protection level.
2) If this is a one-time issue, enable “sa” login WITH changing password of “sa” login.
ALTER LOGINÂ sa WITH PASSWORD = 'yourpass' UNLOCK ;
GO
3) If this is a one-time issue, enable “sa” login WITHOUT changing password of “sa” login.
ALTER LOGINÂ sa WITH CHECK_POLICY =Â OFF;
ALTER LOGINÂ sa WITH CHECK_POLICY =Â ON;
GO
4) If you are not using “sa” login, switch your authentication from mixed mode authentication to windows authentication to remove your “sa” login account.
5) BEST Practice: Create another user with systemadmin role having the same rights as “sa” login and let “sa” login get disabled. Use the newly created account as this will not be exposed on the Internet and for hackers it will be a tough nut to crack! They will find it difficult to guess the right password and moreover, they will not be able to do Brute Force attack over it.
I am eager to know if there are other options to solve this problem.
If you simply want to change the “sa” password, you can follow my previous article SQL SERVER – Change Password of SA Login Using Management Studio.
Please note that it is not mandatory to reboot SQL Server or restart SQL Server services after changing the password for “sa” login. If you are interested, go through all the comments and bring forth your opinion about this discussion.
Reference : Pinal Dave (https://blog.sqlauthority.com)
48 Comments. Leave new
Thanks Pinal! your tutorials have provided me great help in overcoming the hindrances in configuring sql server in lots of aspects.You are a life saver for me
Hello, I’d like to ask you if the “sa” account is the only one account with you have access to SQL server and you cannot log in even with windows authentication – what should you do
hai i want to lock an user account when a wrong pasword is entered too many times using triggers.. could u pls help me
hai i want to lock an user account when a wrong pasword is entered too many times using triggers.. could u pls help me
This has really worked.. Thanks
Great post
WOW just what I was searching for. Came here by searching for to lose weight
Pure GCE Diet
Sir,
I have a similar problem. I have to reset sa login password daily. I dont know why it is happening. My SQL db was working with blank sa password. Now I have changed password in my application so that I dont have to reset password again n again. But problem still exists. Can you please help me as what is problem and how to resolve it
Thanks..
Hi there, I am unable to log in under SQL authentication as I’ve tried 3 times and it got locked. Now I am also unable to log in using Windows authentication. Is there a way to reset the password or unlock the account ( using commands or registry or whatever)?
Thanks
Great post. I am dealing with many of these issues as well..
Thankx it works fine
Thanks a lot , it worked
You actually make it seem so easy with your presentation but I find this topic to be really something that I think I would never understand. It seems too complex and extremely broad for me. I am looking forward for your next post, I’ll try to get the hang of it!
Everything is very open with a really clear explanation of the challenges. It was definitely informative. Your site is very helpful. Thank you for sharing!
Thaaaaaaaak Soooooo Much . Soooo Help Full
hi!,I love your writing so a lot! share we keep up a correspondence extra approximately your post on AOL? I require an expert on this area to resolve my problem. May be that’s you! Looking forward to see you.
Hi,
Wat if you dont have any other sysadmin user. How do you then unlock the “sa” password.
Thanks
Its working fine
Gracias Compadre me ha servido de mucho tu sitio!!!!!!!!!!!!!!
hi, i have forgot the SA password for SQL 2012 and account is locked out. I have only window authentication mode by which I can login but not not having rights to add any user or do any modification. How to recover the SA password or how I can reset the password for SA account.
Thank you so much sir! I got the solution in second.. really thanks a ton.