Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Tracking Login Password Changes in SQL Server

MSSQLTips author Aaron Bertrand By:   |   Read Comments (5)   |   Related Tips: More > 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 GO

Finally, 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


Last Update: 6/13/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, July 24, 2014 - 5:51:28 AM - Ingo Read The Tip

Thank you Aaron for the great example!

How exactly would you track the user who changed the password, what machine?


Thursday, December 26, 2013 - 8:16:33 AM - Musava Read The Tip

How do i track sql username and password.or how can i get the username and password of sql 2005 database..hi please give me step by stepprocedure


Friday, July 19, 2013 - 12:07:05 PM - Aaron Bertrand Read The Tip

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.


Thursday, July 18, 2013 - 5:42:41 PM - Svetlana Golovko Read The Tip

Hi Aaron,

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').

).

 

 


Wednesday, June 20, 2012 - 10:48:48 PM - Active Directory Compliance Read The Tip

Hello Friends,

Thank you for your great post. I really appreciate the efforts you have put in your blog .It is interesting and helpful. Good luck with it!!!




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.