Sql-server – SQL logins Audit for SQL Server 2005/2008

auditloginssql-server-2005sql-server-2008-r2

For the Security Audit of SQL servers 2005/8 R2,

We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via LastSetTime in Loginproperty. But for many servers there are more than 80 logins. How should we extract this information for existing logins?

Also, for those we found who have not changed password in last X days, I believe we need to have existing password. Now the problem is, that existing password cannot be found as was set up way back and password change policy was not marked. What should we do in such cases where we need to change the password of more than 80 logins in a go, as we have plenty of servers to perform this activity?

Thanks!

Best Answer

I had thought you would be able to use policy based management but couldn't find a way to get the PasswordLastSetTime property. It didn't exist in the Login facet.

However what you an do is run the following query to get the PasswordLastSetTime for all SQL Logins on the instance, then use a tool such as Idera's Multi Query or a CMS (Central Management Server) to run it across all of your instances.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime')
FROM sys.server_principals
WHERE TYPE = 'S'

Also as stated in the comments by @Reaces if you are a sysadmin or have control server then you can change the password without the old_password.

EDIT: In response to your comment you can add an additional column to generate the ALTER LOGIN command dynamically. This does of course assume that you are sysadmin and don't need the OLDPASSWORD keyword added.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime'),
    'ALTER LOGIN ' + quotename(name) + ' WITH PASSWORD = ''TemporaryPassword'' ' +
    'MUST_CHANGE, CHECK_EXPIRATION = ON '
FROM sys.server_principals
WHERE TYPE = 'S'

I've also added the MUST_CHANGE clause to force the user to change their password from your generic password the next time they log in.

Related Solutions

Sql-server – Audit user queries on SQL Server 2005

Unfortunately there isn't any Change Data Capture, Change Tracking (both coming only from SQL 2008 +) or even enough Trace Events or Trace Event Groups for Use with Event Notifications.

You're stuck with server side traces for saving all the user queries.

SQL Server – Verifying Password Policy on Existing Users

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make sure they meet your policy. Of course you need to be sure that the password policy is as complex as you expect, and that it is enabled (Control Panel > Administrative Tools > Local Security Policy > Account Policies > Password Policy > Password must meet complexity requirements).

SELECT N'ALTER LOGIN ' + QUOTENAME(name) 
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;' 
  FROM sys.sql_logins 
  --WHERE is_policy_checked = 0;

Steve Jones wrote about this a while back. Note that - due to what I uncovered below - you can't rely on is_policy_checked = 1 to mean that the password actually meets your current policy, since the login could have been created with a hashed password (in which case the plain text password can't be checked) or while the local complexity policy was disabled (which still leads to is_policy_checked = 1).

Another approach I thought would work would be to try to create a copy of every login with their current password_hash and with CHECK_POLICY = ON, and make a note of every one that fails. However, this can not work - even with CHECK_POLICY = ON, it doesn't perform any validation of an already-hashed password. I'll include the code for posterity - but, by design, the policy simply can't be checked.

SELECT N'BEGIN TRY
  CREATE LOGIN ' + QUOTENAME(N'copy_of_' + name) 
    + N' WITH PASSWORD = ' 
    + CONVERT(NVARCHAR(255), password_hash, 1)
    + ' HASHED, CHECK_POLICY = ON;
  DROP LOGIN ' + QUOTENAME(N'copy_of_' + name) + ';
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 15118
    PRINT N''' + REPLACE(name, '''', '''''') 
      + N' was not complex enough.'';
END CATCH'
FROM sys.sql_logins;

Personally, I think this is a bug. If the syntax allows me to create a login using a hashed password, and I can stipulate that that password must meet my complexity policy, it should generate an error or warning that the policy was not, in fact, checked.

UPDATE: I filed a bug against this behavior.

CHECK_POLICY is a no-op with CREATE LOGIN ... PASSWORD ... HASHED

Best Answer

Related Solutions

Sql-server – Audit user queries on SQL Server 2005

SQL Server – Verifying Password Policy on Existing Users

Related Question