Detecting Locked or Expired Logins
SQL Server logins can be configured to use password policies enforced by Windows. These policies include determining when passwords expire, how many invalid passwords can be entered before lock-out, etc. The T-SQL code in this post shows a query for detecting locked-out accounts, accounts that are close to being locked out, and accounts where the expiration date is within the next 5 days.
Pieter Codde – A Group Portrait of the Twent Family in an Interior
Windows Policies
Configure the Windows Policies that enable login expiration and lockouts to occur using the Group Policy Editor or the Local Security Policy Editor. The following policies should be enabled, as required by your organization’s security policy:
- Computer Configuration / Software Settings / Security Settings / Account Policies / Account Lockout Policy:
a. Account lockout duration – set this to the number of minutes an account should stay locked after the maximum number of login attempts has been exceeded.
b. Account lockout threshold – this is the number of invalid logon attempts before the account will be automatically locked. Typically set to at least 5.
c. Reset account lockout counter after – this is an optional setting that allows Windows to automatically unlock the account after the specified duration has passed. - Computer Configuration / Software Settings / Security Settings / Account Policies / Password Policy:
a. Minimum password length – In my opinion, should be set to at least 12 characters, preferably more. A commonly used number widely espoused is 8 characters, but this can lead to fairly trivial cracking via hash collisions or rainbow table attacks. My friend, and Microsoft SQL Server MVP, Randolph West wrote a great column about what constitutes a strong password here.
b. Password must meet complexity requirements – Passwords need to meet or exceed the complexity requirements defined by Microsoft. Can be either “enabled” or “disabled”. This is the description found in Windows 10, when viewing the policy:
This security setting determines whether passwords must meet complexity requirements.
If this policy is enabled, passwords must meet the following minimum requirements:
Not contain the user’s account name or parts of the user’s full name that exceed two consecutive characters
Be at least six characters in length
Contain characters from three of the following four categories:
* English uppercase characters (A through Z)
* English lowercase characters (a through z)
* Base 10 digits (0 through 9)
* Non-alphabetic characters (for example, !, $, #, %)Complexity requirements are enforced when passwords are changed or created.
Configure SQL Server Logins
SQL Server allows you to configure specific SQL Server logins to use those policies. Once set up, SQL Server manages automatically locking accounts or expiring passwords in line with policy. Syntax for creating a SQL Server Login that abides by those policies looks like:
|
1 2 3 4 |
CREATE LOGIN my_login WITH PASSWORD = N'ThisIsA$2TestPassword' , CHECK_EXPIRATION = ON , CHECK_POLICY = ON; |
As tends to be the case with virtually all things security-related, there are certain gotchas around these login policies. My friend, and highly respected SQL Server Guru and MVP, Aaron Bertrand described this problem with setting the password via the HASHED option. That issue is a fairly gaping security hole to be aware of, since it offers an easy way to bypass the complexity requirements when setting a password.
Detecting locked or expired logins with T-SQL code
Once you set up those policy options and create SQL Server logins which are managed via those policies, you may need to check for issues like locked-out accounts, or accounts that are expiring soon. This code returns a result-set showing locked accounts, accounts where someone tried to login using the wrong password, and accounts with passwords that will be forced to expire within 5 days.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT ServerName = @@SERVERNAME , LoginName = sp.name , LockoutTime = LOGINPROPERTY(sp.name, 'LockoutTime') , IsLocked = LOGINPROPERTY(sp.name, 'IsLocked') , BadPasswordCount = LOGINPROPERTY(sp.name, 'BadPasswordCount') , DaysUntilExpiration = LOGINPROPERTY(sp.name, 'DaysUntilExpiration') FROM master.sys.server_principals sp WHERE ( LOGINPROPERTY(sp.name, 'IsLocked') = 1 OR LOGINPROPERTY(sp.name, 'BadPasswordCount') > 0 OR LOGINPROPERTY(sp.name, 'DaysUntilExpiration') < 5 ) AND sp.type_desc = N'SQL_LOGIN' ORDER BY sp.name; |
The Results
Output looks similar to this example, where you can see we have a login, named expiration_test, where the number of days to password expiration is only 3, and lockout_test that has had it’s account locked:
╔════════════╦═════════════════╦═════════════════════════╦══════════╦══════════════════╦═════════════════════╗ ║ ServerName ║ LoginName ║ LockoutTime ║ IsLocked ║ BadPasswordCount ║ DaysUntilExpiration ║ ╠════════════╬═════════════════╬═════════════════════════╬══════════╬══════════════════╬═════════════════════╣ ║ MY\SERVER ║ expiration_test ║ 1900-01-01 00:00:00.000 ║ 0 ║ 0 ║ 3 ║ ║ MY\SERVER ║ lockout_test ║ 2020-02-03 09:14:43.573 ║ 1 ║ 5 ║ 0 ║ ╚════════════╩═════════════════╩═════════════════════════╩══════════╩══════════════════╩═════════════════════╝
Microsoft Documentation for the LOGINPROPERTY(...) T-SQL function can be found here.
Let me know if you found this script useful, and please take a moment to check out our posts on security.