SQL SERVER – Who Changed the Password of SQL Login? – Interview Question of the Week #065

Question: Who changed a SQL login’s password? An audit record captured at the time can identify the principal and target login. Without suitable retained evidence, SQL Server cannot reliably reconstruct the actor after the event.

This came from a real client during an internal audit. Their sa password appeared to change unexpectedly, and they wanted to identify who was doing it before the external audit. I first checked whether the default trace had captured the needed event.

-- Read-only: inspect captured default-trace event classes if enabled.
DECLARE @TraceID int;
SELECT @TraceID=id FROM sys.traces WHERE is_default=1;
IF @TraceID IS NOT NULL
 SELECT DISTINCT e.trace_event_id,e.name
 FROM sys.fn_trace_geteventinfo(@TraceID) AS t
 JOIN sys.trace_events AS e ON e.trace_event_id=t.eventid
 WHERE e.name LIKE '%Audit%';
ELSE SELECT 'Default trace is not enabled' AS Information;

-- Historical unsupported investigation, templates only, NOT an audit trail:
-- USE master;
-- SELECT [Transaction SID],SUSER_SNAME([Transaction SID]) AS [Login Name]
-- FROM sys.fn_dblog(NULL,NULL) WHERE [Transaction Name]='ALTER LOGIN';
-- SELECT [Lock Information] FROM sys.fn_dblog(NULL,NULL)
-- WHERE [Lock Information] LIKE '%SERVER_PRINCIPAL%';
-- SELECT name FROM sys.server_principals WHERE principal_id=270;

-- Read an EXISTING approved file audit, using its actual path and permissions:
-- SELECT event_time,action_id,succeeded,server_principal_name,
--        target_server_principal_name
-- FROM sys.fn_get_audit_file(N'D:\SqlAudit\*.sqlaudit',DEFAULT,DEFAULT)
-- ORDER BY event_time DESC;

Check what was actually captured

The original trace inventory included Audit Login Change Property, not Audit Login Change Password. Those are different event classes. A current inventory can also be empty if the default trace is disabled, and its files roll over. Do not equate the absence of a retained event with proof that no password change occurred.

The historical log investigation was a clue

I explored the undocumented fn_dblog route in master. The original images showed an ALTER LOGIN transaction SID resolving to PinalDave, a SERVER_PRINCIPAL lock with principal_id 270, and that ID resolving to SQLLogin1.

These clues are not sufficient to correlate every ALTER LOGIN record with every principal-lock record, and an ALTER LOGIN need not be a password change. In SIMPLE recovery, checkpoint can make inactive log space reusable; it does not instantly erase every older byte. The available clues depend on retention and reuse. Do not promise this unsupported method will answer the question.

Capture the next event deliberately

For ongoing monitoring, configure an approved SQL Server Audit with LOGIN_CHANGE_PASSWORD_GROUP and protect its target and retention. It records password changes through ALTER LOGIN or sp_password. Test both a successful and a failed change against a disposable login, and verify actor and target fields under the real access model before relying on it.

The file-reading template above reads an existing audit only. It does not enable auditing or create a login. I have omitted the old demonstration’s hard-coded passwords and disabled password policy; neither is needed to preserve the lesson.

Microsoft’s audit action groups identify the password-change group. The audit-file reference documents the actor and target fields.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.