Login passwords for Microsoft SQL Server should never be changed by an end user without appropriate approval. However, by default, login password changes for SQL Server are not tracked, so if a login password is changed in your SQL server environment, you will have no way of knowing that it happened. You will also be unable to identify who changed the password. This absence of a password change log makes it a difficult task to manage the database. In addition, not having visibility over login changes means that your SQL Server security and critical data are both vulnerable and at risk from attack.
Password change events can be captured using SQL Server audit. However, this method requires knowledge of Transact-SQL scripting in SQL Server Management Studio. An alternative, more straightforward method is to use the Lepide Data Security Platform. The Lepide Solution provides an easy way to audit login password changes along with other auditing functionality including changes to database objects and permissions. It provides detailed information about the actions which were performed, when and by whom, so you can detect any potential threats more quickly and strengthen your SQL Server security.
In this guide we will first look at the native method of tracking password changes using SQL Server Management Studio and then the more straightforward solution using the Lepide Auditor for SQL Server.
Track Password Changes using SQL Server Management Studio
Run SQL Management Studio and execute the following T-SQL code to create and enable a SQL Server audit and a SQL Server audit specification. Adjust the path to the logs as needed:
- Create a server audit
CREATE SERVER AUDIT AuditSQL
TO FILE ( FILEPATH ='\\SQL\Audit\' )
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE );
GO
- Create a server audit specification for login password changes
CREATE SERVER AUDIT SPECIFICATION User_pw_change
FOR SERVER AUDIT AuditSQL
ADD (LOGIN_CHANGE_PASSWORD_GROUP);
GO
- Enable the audit
ALTER SERVER AUDIT AuditSQL
WITH (STATE = ON);
GO
To view login password changes, execute the following code in SQL Management Studio:
SELECT * FROM sys.fn_get_audit_file ('\\SQL\Audit\*',default,default)
WHERE action_id = 'PWR'
GO
Open the file produced by the script in MS Excel.
How Lepide Helps
Lepide Auditor for SQL Server provides reporting for SQL Server changes using a range of pre-defined reports. One of these reports is the Login Changes Report for SQL Server and an example of this is given below:
To run this report:
- Select Lepide Auditor, Reports
- From here, Expand SQL Server and select the Login Changes Report
- Specify a date range if required
- Select Generate Report
The report is generated and can be filtered, sorted and exported to CSV and PDF formats.
