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
Good idea for #5.
Is there a way to restrict the source for a login? For example, to restrict sa to be a local login.
Dear Pindal,
My Friend Forgot the SA password in SQL SERVER 2005
Is there any way to Reset “SA” password if the SQL Server is in SQL Server Authentication Mode Only….
and there is no other sysadmin account.
Or
Is there any way to change the Sql Server Authentication mode from Sql to Mixed Mode.
@Kunal.
SQL Server provides two types of Authentication,
Windows Authentication and SQl Server Authentication.
You can have Windows Authentication and
You can have Windows + SQL Server Authentication.
In no case you will have only SQL Server Authenticatication, you are talking about a hypothetical example which would never exists.
If you have an exception other than what I explained above, please provide more details or ask your friend to write a reply to this post.
Thanks,
~ IM.
@ Imran Mohammed
Thanks a lot! That worked for me!!!!
->Connected to the Management Console, logged in with Windows Authentication instead of SQL Server Authentication! So i had Administrative rights and was able to set the ‘sa’ account back on! Thats it!
Great :-)
Hi Pinal,
As in your entire article 4th & 5th option are valuable for security purpose. If the application running on web then It should follow to all DBA for security purpose.
Regards,
Rajiv Singh
Hello!!!
The fix worked great, tks!!!
Worked like a charm!!!
Thanx a lot!
Thank you so much for clearing up a big mystery!
I’ve had SQL-authenticated user accounts apparently locking and unlocking themselves for no good reason, and without DBA-assistance.
It’s good to finally understand that the server’s local security settings affect my SQL logins!
Hi Pinal,
Can you explain how Active directory users can use SQL Server in a domain
Thanks.
Really useful……grt :)
Really helpful….thanks a ton
it works@@@ Thanks a lot!!!
Hi,
This article is very interesting !
But there’ s something that i need to know :
You talked about how to unlock an account locked because of wrong passwords but what i need to know is how to enforce password policy to lock an SQL login as ‘sa’ after multiple numbers of failed attempts for example 3 attempts ?
How can i do this ??
I want to lock the account user with SQL authentication after 3 times of wrong passwords ? HOW ?
Thank you to help
Hello,
Im using asp.net2.0 and sql sever 2000 in my windows server 2003 system. While im run my asp program the following error message arise.”Login failed for user ‘sa’.”. I have change my authentication mode to mixed mode,but the issue is still persisting.Could you plese look into this and tell how to fix this problem.
Hi, maybe too late, but personally I found another issue with some users that can not login with sa account
For some reason I do not know, I found a sa account with no default database, so they can not login with sa account, I just change the default database and “voila”, they can login again with no trouble
Hope It helps anyone
Dear All
i am using os win server 2003 enterprise edition, Sql server 2000 and Vb 6.0
Dear and install all supported applications crystal report Bism etc now when i am going to open vb exe file or application at that time i got this error
login failed for user ‘sa’.
and try to search resolve this issue but still failed so please kindly help me as soon as possible
me waiting all of your reply
Hi All,
in one of my application i am running multiple reports in singleshot, but the issue is after running about 20 to 30 reports the ‘sa’ account of sql is getting lockedout. please help to fix this issue.
thanks in advance.
thank you very much!
Is there any other way to resolve this problem? i am getting this error multiple times… :(
kindly do the needful
Hey my account also locked suddenly..It was nt wrkng for my password.sa Login..later i had run this query n changed my pwd as per my requirement in config file..
ALTER LOGIN sa WITH PASSWORD = ‘yourpass’ UNLOCK ;
Thank god it wrked out..Thanx for ur site…