Today, we will discuss a common issue that many SQL Server administrators face – how to unlock a locked “sa” login. SQL Server is often hosted on a separate server than the application server, and in many cases, the SQL Server ports or IP are exposed to the web, making them vulnerable to hacking attempts. Hackers often target the “sa” login, which is the System Admin login, as it provides complete control over the SQL Server.
One method that hackers use to gain access to the “sa” login is through a brute force attack. This involves attempting to guess the password by trying every possible combination. If your machine or domain has a policy setting that disables an account after a certain number of unsuccessful login attempts, it will also lock out the “sa” login. When this happens, SQL Server will display the following error message: “Login failed for user ‘sa’ because the account is currently locked out.”
So, how can we unlock a locked “sa” login? Here are a few solutions:
- Disable the policy on your system or domain level. However, this may not be the most appropriate option as it will adversely affect your security protection level.
- If this is a one-time issue, enable the “sa” login and change its password using the following SQL command:
ALTER LOGIN sa WITH PASSWORD = 'yourpass' UNLOCK; GO
- If this is a one-time issue, enable the “sa” login without changing its password using the following SQL commands:
ALTER LOGIN sa WITH CHECK_POLICY = OFF; ALTER LOGIN sa WITH CHECK_POLICY = ON; GO
- If you are not using the “sa” login, switch your authentication from mixed mode authentication to Windows authentication to remove the “sa” login account.
- Best Practice: Create another user with the system admin role, giving it the same rights as the “sa” login. Disable the “sa” login and use the newly created account instead. This account will not be exposed on the internet, making it difficult for hackers to guess the password or perform a brute force attack.
If you simply want to change the password for the “sa” login, you can follow my previous article on how to change the password of the “sa” login using Management Studio. It is important to note that it is not mandatory to reboot SQL Server or restart SQL Server services after changing the password for the “sa” login.
I am eager to know if there are other options to solve this problem. If you are interested, please go through the comments and share your opinion about this discussion.