Methods for Tracking Login Password Changes in SQL Server


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.

Login Password Changes with Server-Side Trace

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:

TraceId

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.

Create SQL Server Audit

Change a few passwords while this query is running and pull out the audit data with the query given below:

Declare Folder

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:

Event Notification

Then, to handle the events set up a notification and a queue:

Notification Setup

Then use the following for logging events to be table created:

Logging Events

At the end, change the queue for calling the specified SP in response to the event:

Change Password

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.

Advertisements
This entry was posted in Technical descriptions and tagged . Bookmark the permalink.

One Response to Methods for Tracking Login Password Changes in SQL Server

  1. Very Good Post Anderw bet it will be better if you share the Scripts to be easy for us to try it but now it is hard to take it as Image

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s