Auditing SQL Server Password Age

By: K. Brian Kelley   |   Comments (6)   |   Related: > Auditing and Compliance

Problem

In Active Directory our administrators are able to audit password age to see when accounts last had their password changed. Most of our service accounts are set up to not expire passwords because we change them less frequently than we require users to do so. As a result, the administrators are required to run checks to see how old passwords are for these accounts. We've been asked to do the same for our SQL Server logins. How do I do this?  Check out the solution for this tip.

Solution

If you're on SQL Server 2005 or higher, this is possible. If you're on SQL Server 2000, it's not. SQL Server 2005 was the first version of Microsoft SQL Server which could take the password policy information from the operating system and apply it to the SQL Server-based logins. As a result, it tracks password age and the like so it can expire logins as needed. Since SQL Server 2000 had no such functionality, it didn't include information which could be used to access similar information.

In SQL Server 2005 a built-in function called LOGINPROPERTY() was included which reveals this sort of information. We can use this function with the parameter 'PasswordLastSetTime' to see when the password was last changed for a SQL Server login. To audit all logins, we need to make use of the security catalog view sys.sql_logins.

Here's an example where we audit for any SQL Server logins whose passwords are over 60 days old. Note that the DATEADD function is being used against GETDATE(), a scalar function, instead of against every row.

-- Show all logins where the password is over 60 days old
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

In this query we're reporting the name of the login as well as when the password was last changed. By using the ORDER BY, we could easily sort the query by name or by when the password changed if there are a lot of logins. Now if you're running this against a SQL Server 2008 or higher installation, there are two logins that will show up that you can basically ignore. They are:

If you'd like, you could build the query where it ignores these type of logins. Here's an example:

-- Show all logins where the password is over 60 days old
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
  AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');

Another situation that I often see is, "Tell me all the logins whose passwords changed in the last day." This is a good bit of information to have and we can use a similar query.

-- Show all logins where the password was changed within the last day
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());

Next Steps

• Reviewing the SQL Server password changes is highly recommended on a regular basis or when members of the team transition to other roles or organizations.
• Run this code on some of your SQL Servers and check out how the age of passwords for your SQL Server logins.  Then think about how many people have known those passwords since that date.  Have any of those people left the organization or changed roles?  If so, it might be time to change your passwords.
• If you do not have a password change policy in place at your organization, consider creating one to help protect your logins from misuse.  Use the age of these passwords to build the case for regularly changing passwords.  In many organizations, it is not a simple task, so be sure to plan appropriately and get all of the needed members of your organization involved.
• Read this tip on other login properties available to you.
• Check out all of the SQL Server Security tips on MSSQLTips.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips