SQL Server SA Account: What are the best solutions to fix it?

SQL Server SA Account: What are the best solutions to fix it?

October 9, 2020 11 By mindmingles

Want to know the best solutions to fix SQL Server unlock SA Account? A lot of users get into this troublesome situation where they have to unlock SQL Server SA Account. But due to lack of knowledge, they have no hint on how to deal with this situation? If you are in the same situation then, relax and just thoroughly read this article.

Sometimes, after many tries, users get a login failure in the SQL Server SA Account. As a result, they will get the following error message during logging:

“Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486)“.

Various reasons that lock the SQL Server SA Account. Thus, the user doesn’t get access to the SQL SA Account. The reasons behind SA account getting locked out after several failed login attempts. It is because the SQL Server login is configured to use password policy enforcement. Therefore, user’s accounts get locked out after a certain number of failed login attempts.

To unlock this, users should know some approaches and tricks. In this blog, I will discuss these approaches in brief. So stay tuned.

Common Manual Procedures to Unlock SQL SA Account

There are a few approaches that can fix the problem if SQL Server unlocks SA Account. Here, we will discuss different approaches to fix this issue.

Method 1: Unlock SQL Server Account through Command Prompt

User can go to the given steps to perform this approach:

  1. Firstly, press Win+R and type cmd in the dialogue box. Now, press enter. You can now see Command Prompt in next.
  2. Now, type this command “Osql –S ComputerName –E command”  and tap on the enter.
  3. Next, type command EXEC sp_password NULL, ‘NewPassword’, ‘sa’, and press on the enter.
  4. Finally, type Go. Now, the user’s password will be changed. He can sign into their SQL Server through the new password.

If this method doesn’t work then try another approach.

Method 2: Unlock SA Account in Management Studio

To perform this approach, the user should follow the steps that are mentioned below:

Users can log into SQL Server Management Studio with Windows Authentication by enabling the mixed mode authentication in SQL Server. In this way, one can easily unlock any SQL Server user account.

  1. First, through Windows Authentication, login into SQL Server.
  2. Then, open the Security folder and open the Logins folder in the Object Explorer. Right-tap on the SA account and visit the Properties.
  3. Unmark the box beside Login which is signed out in the Login Properties window and tap on OK.

If this method doesn’t work then try another approach.

Method 3: Unlock Microsoft SQL Server User Accounts via Deleting Password Enforcement

To perform this approach, the user should follow the steps that are mentioned below:

  1. To use this method, users must be a member of the local admin group on the SQL server.
  2. Then, stop the service of SQL Server.
  3. Now, initiate the SQL service in Single User Mode with the given below command (-m defines Single User Mode) via a command line:

net start mssqlserver\instance -m

  1. After that, sign in to SQL Management Studio as a Local Administrator account.
  1. For a ‘sa’ account, users have a single connection. Here, users have to unmark the status of ‘Account Locked’.
  2. At last, users can log out of the SQL Management Studio.

These are the manual methods by which users can repair their SQL Server unlock SA Account. Users can try these methods as per their comfort. If one uses these manual approaches then he is required to perform it carefully.  As there is a higher probability of data loss that will ultimately lead to error. If the given method doesn’t go well, then it confirms the presence of a corrupt SQL database. Thus, users must fix these files first. One can go for an alternative tool as they work well within a limited time.

Users can try SQL Database Recovery that performs the recovery process very well.  The software can easily recover crucial data from corrupted MS SQL database files. It is highly compatible with Windows 10, 8, 7, Vista, 2003, XP, and 2000.

Now, users are aware of all the possible ways to fix this solution. So let’s move further and learn why this happened or what were the reasons behind this issue.

Why do we need to Unlock the SQL SA Account?

There can be some causes due to which System Administrator logs out in the SQL server. These are written as follows:

  • If the user forgot the SA login password.
  • If the SA account gets deactivated.
  • When the users accidentally remove all the logins members of the sysadmin fixed server role.
  • If all the Windows Group members of the sysadmin fixed server role get eliminated accidentally.
  • If there is a use of login members of the sysadmin fixed server role that does not exist anymore or no more in the company.
  • Users must enable the option of Enforce password policy. And, the user’s company has used the account lockout after several login attempts.

So, these were some causes that made the SQL Server SA Account locks although it happens accidentally. But, users must know them so that they can deal with this issue with ease. Therefore, it is necessary to keep checking on the above reasons. As it is the only way to avoid the issue of SA accounts getting locked.

Last Remark

Many users who have to perform the process of SQL Server unlock SA Account due to several reasons. But they didn’t know the exact way or procedure to perform. To help those users, we have discussed different manual approaches that one can easily operate after reading this article. If the user has some corrupt SQL files then they can use the SQL Database Recovery. This tool is very easy to use and any user can use it for SQL recovery purposes.