How to Unlock Locked SQL Server Logins
Introduction / Issue
During day-to-day SQL Server administration, users may report that they are unable to connect to the SQL Server instance even though they are entering the correct credentials. On investigation, the SQL Server login is found to be locked due to multiple unsuccessful login attempts.
A locked login prevents users or applications from accessing the database, which may interrupt business operations and scheduled processes. As a DBA, it is important to identify the reason for the lockout, verify the account status, and safely unlock the login after confirming that the login attempts are legitimate.
Why We Need to Do / Cause of the Issue
SQL Server supports password policies by integrating with the Windows password policy for SQL authenticated logins. When the CHECK_POLICY option is enabled, SQL Server enforces password complexity, password expiration, and account lockout rules.
A login may become locked for several reasons:
- Multiple incorrect password attempts.
- Applications using outdated or incorrect passwords.
- Scheduled jobs or services attempting to connect with expired credentials.
- Password changes that were not updated in application connection strings.
- Automated scripts repeatedly attempting authentication with invalid credentials.
When a login is locked:
- Users cannot connect to SQL Server.
- SQL Server Agent jobs or applications using the login may fail.
- Business processes that depend on database connectivity may be interrupted.
- The SQL Server error log may contain repeated login failure messages until the issue is resolved.
Before unlocking a login, it is recommended to identify the source of the failed login attempts to prevent the account from becoming locked again.
How Do We Solve
Step 1: Verify Whether the Login Is Locked
Run the following query to check the login status.
SELECT
name,
is_disabled,
LOGINPROPERTY(name, ‘IsLocked’) AS IsLocked,
LOGINPROPERTY(name, ‘BadPasswordCount’) AS BadPasswordCount,
LOGINPROPERTY(name, ‘BadPasswordTime’) AS BadPasswordTime
FROM sys.sql_logins
ORDER BY name;
If IsLocked returns 1, the login is currently locked.
Step 2: Unlock the Login
Unlock the SQL login using the following command.
ALTER LOGIN [LoginName]
WITH PASSWORD = ‘StrongPassword@123’ UNLOCK;
Replace:
- LoginName with the SQL login name.
- StrongPassword@123 with the appropriate password.
If the password is already known and does not need to be changed, specify the existing password while using the UNLOCK option.
Step 3: Verify That the Login Has Been Unlocked
Run the verification query again.
SELECT
name,
LOGINPROPERTY(name, ‘IsLocked’) AS IsLocked
FROM sys.sql_logins
WHERE name = ‘LoginName’;
The IsLocked value should now return 0, indicating that the login has been successfully unlocked.
Step 4: Identify the Cause of the Lockout
Review the SQL Server error log to identify repeated login failures.
EXEC xp_readerrorlog
0,
1,
‘Login failed’;
This helps determine whether an application, SQL Server Agent job, scheduled task, or user is repeatedly attempting to authenticate with incorrect credentials.
Real-Time Scenario
A production application suddenly reported database connectivity failures. The application team confirmed that no recent changes had been made to the database server.
The DBA verified the SQL login status and found that the application login was locked due to repeated failed authentication attempts. Further investigation of the SQL Server error log revealed that another scheduled process was still using an old password after the application’s password had been updated.
The DBA coordinated with the application team to update the stored credentials, unlocked the SQL login, and verified that the application could connect successfully. After correcting the outdated password, no further account lockouts occurred.
Conclusion
Unlocking a SQL Server login restores user or application access, but it is equally important to identify and resolve the root cause of the lockout. Simply unlocking the account without addressing repeated failed login attempts can result in the account becoming locked again.
By verifying the login status, unlocking the account using the appropriate SQL command, and investigating the source of authentication failures, DBAs can quickly restore database connectivity while preventing recurring login issues. Regular monitoring of SQL Server error logs and periodic review of application credentials help minimize future account lockouts and improve overall database security.
