Finding NT user (Windows login) that uses SQL Serv

Question

As part of a security task I need to find all SQL Server logins that connect to the SQL server instance.

I create extended event for capture logins and filter only SQL Server logins. The problem is that I can not tell from the SQL Server what was the NT user that was used. Example: From the xEvent I can see that user [sa] logged in to the SQL server instance and I can also see this client host name. BUT , now I would like to know what was the domain account that was logged in to the client host name and use this SQL authentication.

I understand that SQL Server can not give this information but I would like yo know if I can get this info using PowerShell maybe. I have the host name and the SID.

I could not find a solution here : How to get Windows Log-in User Name for a SQL Log in User

Answer 1

Thank you all. I managed to get the AD domain user by running a Get-WmiObject -Class Win32_Process :

1.Find the host_process_id and host_name using sys.dm_exec_sessions

2.Run a PowerShell:

 Get-WmiObject -Class Win32_Process -ComputerName $host_name -Filter "ProcessId = '$($host_process_id )'" | ForEach-Object { $_.GetOwner() }  

Answer 2

You've referenced having the SID, so if this is correct you can translate that to a user account. This works for both local users, and domain users:

$sid = 'S-1-5-21-3423846758-2645770820-3983523239-1001'
$objSID = New-Object System.Security.Principal.SecurityIdentifier($sid)
$objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
($objUser.value -split '\\')[1]

Note: $objUser will contain either the computer name (for a local account) or the domain name (for an Active Directory account), so the split is to parse out the username only.

However, I'm currently unsure how you link this to which account they're using within SQL server (using SQL authentication) - I'm not sure where you're getting the SID information from (Windows event logs? Within SQL itself?).

Answer 3

You do not have a SID, that is a SQL Server SID (yes, there is a such a concept).

First, the answer: you do not. If you want to prevent SQL Logins, you deactivate SQL Logins, and that should be the end of the story.

Since there is no NTLM/Kerberos exchange for a SQL Server login, there is no way to find the credentials of the process/thread that initiated a SQL Login connection. However, you do have the host from where the connection was initiated and the process ID (they are the host_name and host_process_id columns in sys.dm_exec_sessions). Finding the credentials of the remote process is a trivial matter left as an exercise.