By: Aaron Bertrand | Updated: 2012-06-13 | Comments (5) | Related: 1 | 2 | 3 | > Security
Problem
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.
Solution
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.
Server-Side Trace
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):
DECLARE @TraceID INT, @MaxFileSize BIGINT; SET @MaxFileSize = 5;
EXEC sp_trace_create @TraceID OUTPUT, 2, N'C:\Traces\PasswordChangeTrace', -- make sure to change this! @MaxFileSize, 10;
EXEC sp_trace_setevent @TraceID,107, 1, 1; EXEC sp_trace_setevent @TraceID,107, 11, 1; EXEC sp_trace_setevent @TraceID,107, 8, 1; EXEC sp_trace_setevent @TraceID,107, 12, 1; EXEC sp_trace_setevent @TraceID,107, 14, 1; EXEC sp_trace_setevent @TraceID,107, 40, 1; EXEC sp_trace_setevent @TraceID,107, 42, 1;
EXEC sp_trace_setstatus @TraceID, 1;
SELECT @TraceID;
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] FROM sys.traces WHERE id = <traceID from above>;
SELECT 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.
Event Notifications
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:
USE [msdb]; GO
CREATE TABLE dbo.PasswordChangeLog ( LoginName SYSNAME, 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; GO
CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]); GO
CREATE EVENT NOTIFICATION PasswordChangeNotification ON SERVER WITH FAN_IN FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT TO SERVICE 'PasswordChangeService', 'current database'; GO
And then the following procedure can be used to log events to our table:
CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @message_body XML;
WHILE (1 = 1)
BEGIN
WAITFOR
(
RECEIVE TOP(1) @message_body = message_body
FROM dbo.PasswordChangeQueue
), TIMEOUT 1000;
IF (@@ROWCOUNT = 1)
BEGIN
INSERT dbo.PasswordChangeLog(LoginName)
SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
END
END
END
GOFinally, we can change the queue to call this stored procedure in response to the event:
ALTER QUEUE PasswordChangeQueue WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = dbo.LogPasswordChange, MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ); GO
Now change the password for a few logins, and you should see results from the following query:
SELECT LoginName, EventCount = COUNT(*), FirstEvent = MIN(EventTime), LastEvent = MAX(EventTime) FROM dbo.PasswordChangeLog GROUP BY LoginName;
Server Audit
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):
USE [master]; GO
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 ADD (LOGIN_CHANGE_PASSWORD_GROUP) WITH (STATE = ON); GO
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 + '*' FROM sys.server_file_audits WHERE name = 'ChangePasswordAudit';
SELECT 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. :-)
Conclusion
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.
Next Steps
- Choose the method of tracking password changes that you deem most appropriate for your environment.
- Implement the chosen solution as illustrated above.
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-06-13
