- 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, adjusting 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 );
-- Create a server audit specification for login password changes.
CREATE SERVER AUDIT SPECIFICATION User_pw_change
FOR SERVER AUDIT AuditSQL
-- Enable the audit.
ALTER SERVER AUDIT AuditSQL
WITH (STATE = ON);
- 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'
- Open the file produced by the script in MS Excel.
- Run Netwrix Auditor → Navigate to "Search" → Click on "Advanced mode" if not selected → Set up the following filters:
- Filter = "Data Source"
Operator = "Equals"
Value = "SQL Server"
- Filter – "Details"
Operator = "Contains"
Value = "Password"
- Filter = "Data Source"
- Click the "Search" button and review what changes were made to passwords.
Keep an Eye on Login Password Changes in SQL Server to Minimize the Risk of Account Compromise
No user should ever change a login password for Windows SQL Server without proper authorization. Unfortunately, by default, SQL Server does not keep track of login password changes, so if someone alters a login password in your SQL server environment, you will not even know it happened, let along be able to determine who changed the password. This lack of a password change log makes it difficult to properly control the database; in fact, not being able to audit login changes puts your SQL Server security and critical data at risk.
To collect password events with changes, you can use SQL Server audit. However, that method requires Transact-SQL scripting in SQL Server Management Studio, so be ready to revamp your scripting skills. Alternatively, you can use Netwrix Auditor for SQL Server to audit login password changes — and much more. This comprehensive solution enables you to keep track of not only SQL server logins, but also changes to database objects, permissions, instances, password events and more. It provides detailed information about what actions were performed, when and by whom, so you can identify threatening activity faster and strengthen your SQL Server security before your data is compromised.