Is there a way to determine which Windows/Domain account is connected to SQL Server using SQL Authentication?

Question

Hi everybody,

Is there a way to determine which Windows/Domain account is connected to SQL Server using SQL Authentication?
In Connection String, we do not include Windows/Domain account name. It is very important for audit purposes when several people connect to SQL using the same SQL Login from unidentified computers or the same computer. I guess the answer is No. I appreciate your help, thanks.

Best regards,
Ali

Answer 1

No, that is not possible. For starters, people may be connecting from no domain at all.

What you can get is the hostname (which can be set in the connection string, and not be relied on at all), the IP-address (which too can be spoofed, but this requires more work and skills) and the process id on the client (which presumably also can be spoofed),

It is very important for audit purposes when several people connect to SQL using the same SQL Login

If it is important to know who did it, stop using shared accounts and disable SQL authentication.

I should add that I have been in environments where I had first to log in to some web page with a personal account using an RSA token, plus a password that expired after 35 days or so. I think there was one more login step to connect for RDP to a machine. But once I was on that machine I connected to SQL Server with an SQL login that was shared by many. Since they had control how people reach the machine with SSMS, I guess they could connect the SQL login. But what if two persons were connected at the same time? What if someone inside the organisation "borrowed" that account?

Answer 2

Hi @amomen ,

If you are concerned about security, permissions including SQL Server access, Windows Server access depend on the rights assigned to your account either in the domain or on the system, if a system is out of the scope of it, you will unable to access these servers with Windows Authentication SQL Server logins

For unidentified, you should pay more attention to the permissions assigned out

 sp_who2   

may can provide some information including who is logged in, application name, current command, and so on

Find Hostname and Current Logged In User Name

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.