SQL SERVER – FIX : Error: 18486 Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. – Unlock SA Login

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 (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – FIX : Error: 18486 Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. – Unlock SA Login

  1. 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.

  2. 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.

  3. @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.

  4. @ 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 :-)

  5. 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

  6. 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!

  7. 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

  8. 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.

  9. 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

  10. 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

  11. 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.

  12. Is there any other way to resolve this problem? i am getting this error multiple times… :(
    kindly do the needful

  13. 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…

  14. 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

  15. 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

  16. 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..

  17. 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

  18. 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!

  19. 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!

  20. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s