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:

Solarwinds

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)

Solarwinds

, ,
Previous Post
SQL SERVER – Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition
Next Post
SQLAuthority News – Gandhinagar SQL Server User Group Meeting April 24, 2009

Related Posts

45 Comments. Leave new

  • Radhakrishna Reddy
    December 6, 2016 8:06 pm

    Hi
    We are using SQL Server 2008 R2. CDC is enabled. Somehow CDC updates stopped to CDC tables, but it shows that CDC is enabled. Due to CDC enabled and not updating the tables, Repl-Log Reader continuously reading the logs, result of it Log files didnt get shrink after TLog backup. Log size become peak and it went out of space on disk.
    We didn’t find any errors why CDC got stopped/ hung and not updating data to CDC tables.
    We are suspecting it could be a bug, but there is no such evidence that I can prove it’s a bug.
    No CDC tables structures changes happened recently, no changes to SQL Server or O/S.

    Any idea why CDC can go hung mode.

    Reply
    • If any process behaves unexpectedly then I always look at try to see if there is any dump/exception in SQL Server. It could be a non-yielding scheduler, access violation or anything unusual in SQL.

      Reply
  • Jorge Rodriguez
    November 22, 2019 1:06 am

    Pinal
    Buen Día

    Sabras por que en SQL Server 2016 no sale esta advertencia.

    msg: 18486
    Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

    A pesar de confirmarse el bloqueo siempre me sale el siguiente mensaje:

    Login failed for user ‘usr_tester’. (Microsoft SQL Server, Error: 18456)

    Reply
  • Ignacio Ibarguen
    December 6, 2019 1:19 am

    Can I unlock the sa account without being able to connect to the database?

    Reply

Leave a Reply

Menu