Tracing is a onetime procedure run through an act to find out the root whereas tracking is a continual process. To remain updated with the regularly made changes, tracking of an activity must be done. Similarly, on an SQL Server, one can track down the login password changes in SQL Server. However, SQL Server is not programmed to maintain a log of the changes in password taking place, by default.
Nevertheless, there are technically three methods that can be implemented for quickly tracking back the changes with passwords lately taken place. These methods are:
- Server-Side Trace
- SQL Server Audit
- And, Event Notification
This post will be sharing a brief example of using each of the listed methods for tracking of login password changes in SQL Server. However, note that these techniques can track back change of password with the help of ALTER LOGIN, Management Studio Login Properties dialog, or sp_password; the system procedure deprecated since version 2005.
Method 1: Login password changes in SQL Server with Server-Side Trace
This trace method has an event that is named; ‘Audit Login Change Password Event’. The event is comparatively more reliable than the process of capturing batches then performing on filter using stored procedure ‘%sp_password%’ along with ‘%ALTER%LOGIN%PASSWORD%’.
EventID for this is 107, thus, it is possible for you to setup a simple trace using the given code:
NOTE: A proper path needs to be set for tracing the desired file.
Note the TraceID that is mentioned in the output. After running this query, you can make use of the TraceID you just noted down for reviewing the captured events with the query given below:
Trace definition above specifies files of a limited size only therefore, an event happening today won’t be available tomorrow. Taking a snapshot of the data to a permanently table, to run the queries from.
Method 2: SQL Server Audit for Tracking Login Password Changes
Audit specifications for SQL Server are the next method of tracking all the login password changes. Most server administrators are already familiar with it, thus, using this technique may seem more logical to them than the other two. Nevertheless, take note that SQL Server 2008 or its R2 version is required for using the Server Audit.
One of the audit specifications is LOGIN_CHANGE_PASSWORD_GROUP.
These events can be tracked with the help of setting up an audit that is file-based along with the given code.
NOTE: This must be carried out in master thus, make sure you have appropriately updated the file path for the same.
Change a few passwords while this query is running and pull out the audit data with the query given below:
The file-based audit has a limit therefore; you are required to make changes in the settings to make the data stick around for a little longer than usual or probably you could store the query result occasionally in a permanent table.
NOTE: Server Audit records time of the event in UTC standard. Thus, you may realize that the timestamps may require further efforts for conversion.
Method 3: Event Notifications
In the place of the above-mentioned techniques, one can also apply Event Notification for tracking login password changes in SQL Server. These are messages that light and asynchronous and are sent out through a Service Broker used for performing a series of actions as part of responding to a specific event. AUDIT_LOGIN_CHANGE_PASSWORD_EVENT is one of the events. In many cases, users send email or start a job with these. However, in our case, only a log to the table will be made. Create the given below table in MSDB:
Then, to handle the events set up a notification and a queue:
Then use the following for logging events to be table created:
At the end, change the queue for calling the specified SP in response to the event:
These are some of the methods that can be implemented for tracking login password changes in SQL Server. One of the options is executable using SQL Server version 2008 while the others are for version 2005.