Pages

Men

rh

7/08/2013

How to Unlock a SQL Login Without Resetting the Password

Problem

I have a SQL Server login that's been locked out. I went to unlock it, but it's telling me I have to change the password to do so. I can't change the password and I don't know what it is. How can I unlock the account without changing the password?  Check out this tip to learn more.

Solution

Starting in SQL Server 2005, SQL Server can use the password policies that the operating system uses. This includes account lockout. If a SQL Server login is configured to use password policy enforcement and your organization uses account lockout after a certain number of failed logins, you can end up locking out a SQL Server login via the same scenario.

SQL Server Management Studio Login Properties for Enforce password policy

This can result in the account being locked out. Unlocking the account is easy to do if you want to reset the password. If you don't however, SQL Server won't let you directly. For instance, simply unchecking the box beside Login is locked out and clicking OK won't work.

SSMS Login Properties - Login is locked out

Instead, you'll get the following error:

SSMS error - Reset password for the login while unlocking. (SqlManagerUI)

The trick is to temporarily take the login out of password policy enforcement by unchecking it, as shown in Figure 4.

SQL Server Management Studio Enforce Password Policy

This will disable the unlock checkbox.

SSMS SQL Server Authentication login is locked out is unchecked

Click OK to confirm the change and the SQL Server login will unlock. This will permit connections via that login again. If there is a need for the password enforcement to be turned back on, you can do so and it won't re-lock the login. However, in either case be sure you know what caused the lockout in the first place. The reason for having account lockout is to prevent a security breach due to someone brute forcing the password for an account. Disabling password policy enforcement basically means an attacker can try to guess the password.

Source Collected from MSSQLTIPS.COM

No comments :

Post a Comment