Who Logged In Using SA Credentials?

Introduction

Hello Joe/Jane!

Ever got questioned by audits? Who changed this table? Why do people know SA credentials? Why are you not alerted? Why can't we know who logged in using SA account?

Well, there are times when you cannot change SA password for your DB instances (like a critical legacy service app is using SA to connect to your servers).

There is no built-in feature under SQL Server to know who logged in using SA, but we can definitely find the "culprit"!

Background

Every DBA knows to query two things - Sessions & Connections.

We take one information from each of the above:

1. Sessions - The Remote Process PID (host_process_id)
2. Connections - The IP Address of the Client (client_net_address)

If you're a DevOps King/Queen, you already know what to do from here. If you aren't, then we use a built-in Windows command named TASKLIST along with our very own favorite XP_CmdShell.

Using the Code

Here's how TASKLIST works for us in this scenario: (you can /? for all available options, but the below serves us the purpose).

TASKLIST /S <ip address> /FI "PID eq <pid>" /V /FO LIST /U <domain\admin> /P <pass>

If we were to provide an IP where SSMS is running with a pid, this gives us:

Image Name: Ssms.exe
PID: <pid>
Session Name: Console
Session#: 6
Mem Usage: 176,904 K
Status: Running
User Name: <remote user>
CPU Time: 0:02:03
Window Title: Microsoft SQL Server Management Studio

So, we can now use this in our T-SQL this way:

CREATE TRIGGER [trgRogueUser] ON ALL SERVER 
WITH ENCRYPTION
FOR LOGON
AS
BEGIN
	IF ORIGINAL_LOGIN() = N'sa'
	AND APP_NAME() LIKE N'Microsoft SQL Server Management Studio%'
	BEGIN
		DECLARE @Body VARCHAR(MAX)
		DECLARE @Sub VARCHAR(MAX) = 'DB Server Access using SA credentials!' 
		DECLARE @Output TABLE ([Output] VARCHAR(1000))
		DECLARE @CMD VARCHAR(500) = 
        (
             SELECT  TOP 1 'tasklist /S ' + client_net_address + ' /FI "PID eq ' 
			     + CONVERT(VARCHAR(MAX),host_process_id) 
			     + '" /V /FO LIST /U DOMAIN\Admin /P Password' 
             --User & Pass required when SQL service is running under local account 
             --or when service account does not have domain admin privileges. 
             --(that's the reason to encrypt this trigger)
		     FROM sys.dm_exec_connections C JOIN sys.dm_exec_sessions S
		     ON C.session_id = S.session_id WHERE S.session_id = @@SPID
        )
		
		INSERT INTO @Output
		EXEC xp_cmdshell @CMD

		DELETE @Output 
        WHERE [Output] IS NULL

		SELECT @Body = COALESCE(@Body + CHAR(13) + CHAR(10), '') + [Output] 
        FROM @Output
		
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'Alerts',
			@recipients = 'dba@domain.com',
			@body = @Body,
			@subject = @Sub
	END
END

Well, think about the clever or wild or zany part!

What if a user uses a vbs to access the database?
Check if sys.dm_exec_sessions.is_user_process != 0 instead of checking for a particular app.

History

Changes to be made when things break! =)