By default, SQL Server does not keep track of login password changes. When the question initially came up with a user, I thought that perhaps it might be in the default trace or in the system_health extended event session. No such luck. So I was in search of an alternate way to keep track of these events, if not retroactively, at least going forward.
In a short time you can be up and running with collecting password change information using three different methods: server-side trace, event notifications, and SQL Server audit. Below I will provide an example using each technology. Note that all three examples are able to track password changes using ALTER LOGIN, the system procedure sp_password (deprecated since SQL Server 2005), or the Management Studio Login properties dialog.
Trace includes an event called "Audit Login Change Password Event" - which is much more reliable than capturing all batches and filtering on '%sp_password%' and '%ALTER%LOGIN%PASSWORD%'. The EventID is 107, so you can set up a very simple trace with the following code (make sure to set a proper path to the desired trace file):
Make note of the TraceID in the output. Once this has been running, you can use that TraceID to review the events that have been captured using the following query:
DECLARE @path NVARCHAR(255);
SELECT @path = [path]
WHERE id = <traceID from above>;
LoginName = TargetLoginName,
EventCount = COUNT(*),
FirstEvent = MIN(StartTime),
LastEvent = MAX(StartTime)
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 107 -- in case you've added other events
GROUP BY TargetLoginName;
Since the above trace definition specifies a max of 10 x 5MB files, eventually an event that happens today will no longer be available through the above query. So as an added exercise you may consider periodically taking a snapshot of this data into a permanent table, and running your queries from there.
An alternative to trace is to set up a targeted Event Notification. These are lightweight, asynchronous messages sent via Service Broker that can be used to perform various actions in response to a specific event. One such event is AUDIT_LOGIN_CHANGE_PASSWORD_EVENT. In a lot of cases people use these to send an e-mail or start a job, but in this case we're just going to log to a table. We can create the following table in msdb:
CREATE TABLE dbo.PasswordChangeLog
EventTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
We will then need to set up a queue and a notification to handle our events:
CREATE QUEUE PasswordChangeQueue;
CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue
CREATE EVENT NOTIFICATION PasswordChangeNotification
ON SERVER WITH FAN_IN
TO SERVICE 'PasswordChangeService', 'current database';
And then the following procedure can be used to log events to our table:
CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
SET NOCOUNT ON;
DECLARE @message_body XML;
WHILE (1 = 1)
RECEIVE TOP(1) @message_body = message_body
), TIMEOUT 1000;
IF (@@ROWCOUNT = 1)
SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)', 'sysname');
Finally, we can change the queue to call this stored procedure in response to the event:
ALTER QUEUE PasswordChangeQueue
STATUS = ON,
PROCEDURE_NAME = dbo.LogPasswordChange,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
Now change the password for a few logins, and you should see results from the following query:
EventCount = COUNT(*),
FirstEvent = MIN(EventTime),
LastEvent = MAX(EventTime)
GROUP BY LoginName;
The final option I'll present here is creating a Server Audit Specification. You may already be using Server Audit, and if so, handling password change auditing using this technology might make more sense than using either of the above two methods. (However note that Server Audit requires Enterprise Edition of SQL Server 2008 or SQL Server 2008 R2 - in SQL Server 2012, this feature has been made available in all editions.)
One of the options for a Server Audit Specification is LOGIN_CHANGE_PASSWORD_GROUP. We can set up a file-based audit to capture these events with the following code (note that this needs to be performed in master and you should update the file path appropriately - you probably don't want to rely on C:\ for this):
CREATE SERVER AUDIT ChangePasswordAudit
TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 5MB, MAX_ROLLOVER_FILES = 10)
WITH (ON_FAILURE = CONTINUE); -- important unless you want your server to halt on failure
ALTER SERVER AUDIT ChangePasswordAudit
WITH (STATE = ON);
CREATE SERVER AUDIT SPECIFICATION ChangePasswordAuditSpecification
FOR SERVER AUDIT ChangePasswordAudit
WITH (STATE = ON);
Once this is running, you can change a few passwords and then retrieve data from the audit using the following query:
DECLARE @folder VARCHAR(255);
SELECT @folder = log_file_path + '*'
WHERE name = 'ChangePasswordAudit';
LoginName = target_server_principal_name,
EventCount = COUNT(*),
FirstEvent = MIN(event_time),
LastEvent = MAX(event_time)
FROM sys.fn_get_audit_file(@folder, DEFAULT, DEFAULT)
WHERE action_id IN ('PWR', 'PWC') -- PWR = ALTER LOGIN / SSMS, PWC = sp_password
GROUP BY target_server_principal_name;
As with the trace above, this file-based audit is limited to 10 x 5MB files. So you may want to change those options to have the audit data hang around longer, or you may consider occasionally storing the result of this query in a permanent table.
One important thing to note about Server Audit is that it records the event time in UTC, so you might notice that the timestamps are off depending on your time zone. Therefore you may need to look into adding a helper function that will convert any UTC date to your time zone. Since this can get complicated with Daylight Saving Time, I've often found it easier to just set up all of our servers to do everything in UTC. :-)
As you can see, there are a variety of ways to set up tracking for password changes, and each method is relatively straightforward to implement. While it is still impossible to obtain this information from the past, once you have implemented one of the above solutions, you will be able to look back on this information over time.
Choose the method of tracking password changes that you deem most appropriate for your environment.
Implement the chosen solution as illustrated above.
Great post about tracking password changes in real time! But I am not quite agree that "SQL Server does not keep track of login password changes". You can find the date when the password was set last time with LOGINPROPERTY function: LOGINPROPERTY(l.name,N'PasswordLastSetTime').
Friday, July 19, 2013 - 12:07:05 PM - Aaron Bertrand
Thanks Svetlana, I wasn't aware of that login property. The nice thing about the methods above, though, is that you can extend them to capture other information, e.g. the user who changed the password, what machine they were on, etc.