Most SQL Servers are set up with mixed mode authentication, meaning that you can use a domain login from Active Directory (AD) or create a local login / password at the SQL Server level. Microsoft recommends that you only use Active Directory accounts to access SQL Server but in the real world, that is sometimes not possible. Many applications that use SQL Server as their DBMS require you to use SQL logins as that is how the application is configured. Some, but hopefully not many, actually require the use of the SA login.

So what does that mean for you? It means that you need to keep track of all the SQL logins and make sure that they have strong passwords and that they get changed on a regular schedule (normally every 90 days). This can be a real chore if you have 25 SQL Servers and each has 25 SQL logins. That is 625 logins you need to audit and report on. That means you really need an automated system in place that will give you (or your security department) a report of SQL logins with password age and password strength. Here is how I track logins on my SQL Servers.

First we need the structures in place to hold our audit information. I always create a DBA database on every server I manage and use that to keep my maintenance scripts and all my audit data. So I will be using the DBAdmin database for this exercise. The table structure I use is simple and straightforward. I don’t like to add to many bells and whistles unless I absolutely need to.

CREATE TABLE SQLPasswordAudit(
       ID INT IDENTITY(1,1) NOT NULL,
       ServerName VARCHAR(50) NOT NULL,
       SQL_Login VARCHAR(50) NOT NULL,
       IsSysAdmin BIT NOT NULL DEFAULT(0),
       IsWeakPassword BIT NOT NULL DEFAULT(0),
       WeakPassword VARCHAR(250) NULL,
       PwdLastUpdate DATETIME2 NOT NULL,
       PwdDaysOld INT NULL,
       DateAudited DATETIME2 NOT NULL DEFAULT(GETDATE())
);

I think the columns should be self-explanatory but let’s walk through them.

• ID – Just an identity column for a unique key. I will use it as my clustered index.
• ServerName – The name of the server being audited. This is useful if you want to consolidate the data from multiple servers into a central database.
• SQL_Login – Name of the SQL Login.
• IsSysAdmin – Does login have SysAdmin rights? 1 = Yes    0 = No
• IsWeakPassword – Does the password for this user fall into the weak category (we will go into more detail about this later).   1 = Yes    0 = No
• WeakPassword – Description of why the password is weak.
• PwdLastUpdate – The date the password for the user was last updated.
• PwdDaysOld – How many days since password was last changed.
• DateAudited – Date the audit was conducted.

Once you have the table in place let’s look at the stored procedure, piece by piece. The create script for this stored procedure can be downloaded from here: http://www.sqlservercentral.com/scripts/SQL/153919/

The first thing I do is check to see if the table exists and if it does not, then we create it. This is a bit redundant but it is nice in that if you are deploying this to 20 servers (or 200), you don’t have to log on and manually create the table every time. This will create the table in whichever database you run it in.

       -- if table does not exist create it.
       IF OBJECT_ID(N'dbo.SQLPasswordAudit', N'U') IS NULL
       BEGIN
              PRINT 'Creating Table'
              CREATE TABLE SQLPasswordAudit(
                     ID INT IDENTITY(1,1) NOT NULL,
                     ServerName VARCHAR(50) NOT NULL,
                     SQL_Login VARCHAR(50) NOT NULL,
                     IsSysAdmin BIT NOT NULL DEFAULT(0),
                     IsWeakPassword BIT NOT NULL DEFAULT(0),
                     WeakPassword VARCHAR(250) NULL,
                     PwdLastUpdate DATETIME2 NOT NULL,
                     PwdDaysOld INT NULL,
                     DateAudited DATETIME2 NOT NULL DEFAULT(GETDATE())
              )
       END;

CREATE CLUSTERED INDEX [cluster_idx_ID] ON [dbo].[SQLPasswordAudit]
([ID] ASC)
WITH (FILLFACTOR = 90);

Next we get all the SQL logins for the server. We do this by joining SYS.SQL_LOGINS with MASTER..SYSLOGINS. SYS.SQL_LOGINS returns one row for every SQL Server authentication login. We join to MASTER..SYSLOGINS in order to get the IsSysAdmin flag. We also include the server name here for identification purposes.

You can see we are not auditing the certificate-based SQL Server Logins, logins with names enclosed by double hash marks (##). These are for internal system use only and are created from certificates when SQL Server is installed.

Please notice we are using the LOGINPROPERTY(<SQL Login>, ‘PasswordLastSetTime’) here to find the date the user last changed their password. This is a very useful function and can return quite a bit of information about the SQL Login. Please see BOL for its other parameters.

We are storing this information in a temp table, the reason which will become clear in just a bit.

       -- Get all SQL Logins
       SELECT
              @@ServerName ServerName,
              a.name AS SQL_Login,
              b.sysadmin AS IsSysAdmin,
              CAST(LOGINPROPERTY(a.[name], 'PasswordLastSetTime') AS DATETIME) AS 'PwdLastUpdate'
       INTO #TempAudit
       FROM sys.sql_logins a
       LEFT JOIN MASTER..syslogins b ON a.sid = b.sid
       WHERE a.name NOT LIKE '##%';

Now we are going to merge the temp table with the SQLPasswordAudit table. Depending on what data is there and what might have changed, we will INSERT or UPDATE or DELETE. We are matching on the SQL Login name. When the login matches and the PwdLastUpdate is different or the IsSysAdmin value is different, we update those fields.

If there is no match in the Target (SQLPasswordAudit) then we insert the values from the temp table. If there is no match in the Source, we have to assume that login has been delete so we delete it from our Target as well.

After the merge, we can then drop the temp table.

       -- merge with perm table
       MERGE INTO SQLPasswordAudit a
       USING #TempAudit b ON a.SQL_Login = b.SQL_Login
       WHEN MATCHED AND (a.PwdLastUpdate != b.PwdLastUpdate OR a.IsSysAdmin != b.IsSysAdmin) THEN
              UPDATE
              SET a.PwdLastUpdate = b.PwdLastUpdate,
               a.IsSysAdmin = b.IsSysAdmin
       WHEN NOT MATCHED BY TARGET THEN
              INSERT (ServerName, SQL_Login,IsSysAdmin, PwdLastUpdate)
              VALUES (b.ServerName, b.SQL_Login, b.IsSysAdmin, b.PwdLastUpdate)
       WHEN NOT MATCHED BY SOURCE THEN
              DELETE;
       -- drop temp table
       DROP TABLE #TempAudit;

Now we calculate the number of days old the passwords are. This could easily be done in the report and not in the table itself, but I like it in the table so that if someone who is not familiar with SQL is using the data, it is ready to go.

       -- calculate the number of days old the passwords are
       UPDATE SQLPasswordAudit
       SET PwdDaysOld = DATEDIFF(day,PwdLastUpdate,GETDATE());

Next we are going to look at the passwords for the SQL Logins. SQL Server hashes passwords when they are created so we will not be able to actually see what the passwords are. There is a password compare function though, so we can compare the hash stored in SQL Server against a known value. 

We want to clean out the IsWeakPassword bit and WeakPassword column before we do this so we don’t accidently miss changes.

       -- reset fields for password
       UPDATE SQLPasswordAudit
       SET IsWeakPassword = 0,
              WeakPassword = '';

First check is to see if the password is blank. Normally this is not allowed if you use the “Enforce Password Policy” when the password is created. But if for some reason that was unchecked or maybe the user was copied over during a SQL migration, it is possible to have a blank password. I have found a few in my career.

       -- check if password is blank
       UPDATE SQLPasswordAudit
       SET WeakPassword = '[BLANK PASSWORD]',
              IsWeakPassword = 1
       FROM SQLPasswordAudit a
       LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
       WHERE PWDCOMPARE('', b.password_hash) = 1;

Another possible issue is that the password is the same as the login name. Again, this is normally not possible but I have found a lot of people will create a user for a linked server called LinkedUser and then override the password policy check to make the password LinkedUser as well. This is actually much more common than you might think. So we check for it. 

       -- check if password is same as login
       UPDATE SQLPasswordAudit
       SET WeakPassword = 'Same As Login',
              IsWeakPassword = 1
       FROM SQLPasswordAudit a
       LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
       WHERE PWDCOMPARE(a.SQL_Login, b.password_hash) = 1
       AND WeakPassword = '';

PLEASE NOTE:  This next step is optional. Because of the CROSS JOIN this step can be intensive and could cause issues. I would not run it during peak times on production servers.

Finally we check the passwords against the most common passwords found in the wild. There are dictionaries of simple, common passwords that can be used by someone who might mean you harm, so we are checking the top 1000 to make sure we find them before they might. Now, 1000 is not a huge number and you can find passwords lists on the internet with 100,000 or more passwords, but at some point this becomes a losing battle as the time it takes to check every password against every password list is just not worth it. You will have to use your own judgment but I think checking the top 1000 is a good balance between being worth it and not crippling my server with too much scanning.  

Be careful here, if we find a SQL login with a common password, we log it in the WeakPassword column. This will expose the password for that login, so you might want to be careful who you grant access.

   -- check the common password table if it exists
IF OBJECT_ID(N'dbo.CommonPwds', N'U') IS NOT NULL
BEGIN
UPDATE SQLPasswordAudit
     SET IsWeakPassword = 1,
     WeakPassword = 'WEAK - ' + c.pwd
FROM SQLPasswordAudit a
LEFT JOIN sys.sql_logins b ON a.SQL_Login = b.name
CROSS JOIN CommonPwds c
WHERE PWDCOMPARE(c.pwd, password_hash) = 1 
     AND WeakPassword = '';
END;

And that concludes the auditing of the server.

Create a SQL Agent job to run this on a regular basis (I normally do it weekly) and you are done. If there are any logins with passwords older than 70 days (my password policy is every 90 days), have the job alert you.