Speak guys!
In this article I wanted to share with you a simple solution to unlock a user without having to change/reset the password, which can sometimes be very annoying when having to change a user's password so that he logs back into the bank.
To learn more about Policies, Automatic Blocking and Expiration, read the article SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts.
When you create a login using a password policy (CHECK_POLICY) to ensure that passwords are complex enough and that short and weak passwords are not accepted, depending on your Windows/AD settings, the auto-lock feature after N incorrect attempts may be activated.
To test this scenario, we will create a user with password policy enabled:
|
1 2 3 4 5 6 7 |
USE [master] GO CREATE LOGIN [teste_politica_senha] WITH CHECK_POLICY = ON |
Now I ensure that the auto-blocking properties are enabled on the “Local Security Policy” screen (secpol.msc)
We will block this user trying to connect me more than 5 times (parameter I set in the previous screen) with the wrong password
To check if the user is blocked, you can use the query below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT A.[name], A.[type_desc], A.is_disabled, A.create_date, A.modify_date, A.is_policy_checked, A.is_expiration_checked, LOGINPROPERTY(A.[name],'BadPasswordCount') AS [BadPasswordCount], LOGINPROPERTY(A.[name],'BadPasswordTime') AS [BadPasswordTime], LOGINPROPERTY(A.[name],'DaysUntilExpiration') AS [DaysUntilExpiration], LOGINPROPERTY(A.[name],'HistoryLength') AS [HistoryLength], LOGINPROPERTY(A.[name],'IsExpired') AS [IsExpired], LOGINPROPERTY(A.[name],'IsLocked') AS [IsLocked], LOGINPROPERTY(A.[name],'IsMustChange') AS [IsMustChange], LOGINPROPERTY(A.[name],'LockoutTime') AS [LockoutTime], LOGINPROPERTY(A.[name],'PasswordLastSetTime') AS [PasswordLastSetTime], LOGINPROPERTY(A.[name],'PasswordHashAlgorithm') AS [PasswordHashAlgorithm] FROM sys.sql_logins A JOIN sys.server_principals B ON A.[sid] = B.[sid] WHERE A.is_disabled = 0 AND B.is_fixed_role = 0 AND LOGINPROPERTY(A.[name],'IsLocked') = 1 |
Result:
I can also check if SQL Login is blocked by SSMS interface
Now comes the tip of this post. How can I unlock this login without having to change the password?
If you try to simply uncheck this option in the SSMS interface, you will see this error message:
For this to work, uncheck the option of “Enforce password policy”
Ready! The user has already been unlocked. You can now check this option again, if you want to keep it the way it was before.
You can also use this SQL command to unlock the user without having to change the password:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master]; GO ALTER LOGIN [teste_politica_senha] WITH CHECK_POLICY = OFF; GO ALTER LOGIN [teste_politica_senha] WITH CHECK_POLICY = ON; GO |
And that's it!
Short and quick post with this useful everyday tip.
Now you won't need to change an application's user password because a dev set up the wrong environment 🙂
