Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Auditing SQL Server Password Age


By:   |   Read Comments (6)   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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:

SQL Server Logins to Disregard

##MS_PolicyTsqlExecutionLogin##
##MS_PolicyEventProcessingLogin##

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.


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, September 11, 2015 - 3:05:48 AM - Mahmood Ahmed Back To Top

plz help how to password protect sql server 2000 database 

mdf&log file

thanks


Wednesday, September 19, 2012 - 9:39:25 PM - K. Brian Kelley Back To Top

If you're talking System Center, you'd probably want to use Operation Manager (SCOM), not Configuration Manager.


Wednesday, September 19, 2012 - 5:27:43 PM - sunkavelli Back To Top

Thanks for your quick response, it got resolved..

I have another question, Can we able to create a job through configuration manager generate a report from all the sql servers and send report to the dba team.

Thanks.


Wednesday, September 19, 2012 - 2:35:21 PM - K. Brian Kelley Back To Top

sunkavelli, I try to be as helpful as I can. However, this one I know you can solve yourself. So here I'm going to refer you to Books Online to look at the entry for sys.sql_logins. You just need to add another condition to the WHERE clause and to adust the DATEADD function appropriately.


Wednesday, September 19, 2012 - 2:02:05 PM - sunkavelli Back To Top

Hi,

This query showing the disabled accounts too, Could you please give us a query which gives only enabled year old passwords. Please let me know. Thanks.

 


Thursday, May 12, 2011 - 6:51:52 AM - Usman Butt Back To Top

Thanks a lot. A Very nice thing to know and helped me a lot while auditing the login usage. It helped me find the users never logged in (HAVING PasswordLastSetTime = DEFAULT 1900-01-01 ) and some how still doing the development. May be through Password Sharing.

Keep writing such useful tips. Cheers.


Learn more about SQL Server tools