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

Today’s interview question was not really asked interview, but it is indeed a real world scenario which everyone should know the answer of. I strongly suggest you bookmark this blog post at once in your lifetime you will need this blog post to know who changed the password of your SQL Login?

Question: Who changed the password of SQL Login?

Answer: Interacting with customers in my consulting role keeps me up-to-date with current versions, but sometimes I get opportunity to brush up my basics also. This blog is the result of one such interaction. Never are questions and requirements from customers easy. They come to us only when something is really wrong or they are not able to figure out after spending a couple of hours on the various search engines / forums. Even better when their internal IT team just turns their back. Some of these requirements come when the year is about to end and there is auditing happening inside the organization. I was fortunate when one of the internal audits inside a company got the better and they reached out to me before the external auditor can question them.

My client noticed that on a random basis, SQL “SA” account password was getting reset and they wanted to know who is doing it? I knew that SQL Server error log does not record login password change events. So my next first thought was “Default trace” should have that data. On my lab machine I ran below query to find out the events captured

DECLARE @Trace_ID INT;

SELECT @Trace_ID = id
FROM sys.traces
WHERE is_default = 1;

SELECT t.EventID
	,e.NAME AS Event_Description
FROM sys.fn_trace_geteventinfo(@Trace_ID) t
INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id
WHERE e.name like '%Audit%'
GROUP BY t.EventID
	,e.NAME;

EventID Event_Description
—– ——————-
18 Audit Server Starts And Stops
20 Audit Login Failed
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
117 Audit Change Audit Event
152 Audit Change Database Owner
153 Audit Schema Object Take Ownership Event
175 Audit Server Alter Trace Event

Unfortunately, the SQL Server default trace records only Audit Login Change Property Event Class and not Audit Login Change Password Event Class. So the client may need to run custom trace to track password changes.

So, we are left with no choice but monitor it via a new trace. Another thought which came to my mind was that can we make use of fn_dblog to read the transaction log. I told my client that this method is completed undocumented. Another problem with this approach is that if the checkpoint is fired the data would be flushed in T-log because master database is in simple recovery model. But this was a great start for them to at least debug what was happening in the system.

Here is what I provided them as script to troubleshoot:

Create the Login for demo

USE [master]
GO

CREATE LOGIN [SQLLogin1] WITH PASSWORD=N'@Very$trongP@ssw0rd123', DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Use this to Change Password

USE [master]
GO
ALTER LOGIN [SQLLogin1] WITH PASSWORD=N'VerystrongP@ssword123'
GO

Query to find who changed the password

SELECT	[Transaction SID]
	,suser_sname([Transaction SID]) AS 'Login Name'
FROM::fn_dblog(DEFAULT, DEFAULT)
WHERE [Transaction Name] = 'ALTER LOGIN'

Query to find which account password got changed

SELECT [Lock Information]
FROM::fn_dblog(DEFAULT, DEFAULT)
WHERE [Lock Information] LIKE '%SERVER_PRINCIPAL%'

Now we can use below to find which account is 270 (as highlighted above)

SELECT name  
FROM sys.server_principals
WHERE principal_id = 270

Have you ever used such undocumented command? Use this with care as this is not a supported command. So please make sure you are careful in it’s usage. I am sure there are tons of interesting ways in which you might use this blog, I am very much interested in knowing some of them. Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)