0

We are implementing a monitoring solution that keeps track of logins to SQL Server.

We need to capture the following event: a user logs in to a Windows client and from there logins to a remote SQL Server using SQL Authentication.

When this occurs, we need to keep track of the Window user that logged into the Windows client.

Is there any chance to configure SSMS to pass the credentials of currently logged-in Windows user to the SQL Server it is connecting to?

9
  • Use Windows Authentication maybe? Commented Jun 26, 2023 at 10:22
  • @Charlieface most of users use Windows Authentication. Unfortunately a small quota of users must keep using SQL authentication, so we must support it.
    – Andrea
    Commented Jun 26, 2023 at 10:29
  • 2
    Then no you can't. There is no other mechanism for passing it through (other than maybe doing a manual EXEC sp_set_session_context at the top of every script) Commented Jun 26, 2023 at 10:40
  • 2
    Even Windows Authentication isn't perfect here either. Domain\UserA can login to a Windows machine, then run SSMS as a different domain user, Domain\UserB, and then connect to SQL Server. The SQL Server instance would only see UserB and have no way to tell the user logged into the Windows machine is UserA. Basically, there's no reliable way to do this.
    – J.D.
    Commented Jun 26, 2023 at 11:50
  • 1
    @J.D. learn.microsoft.com/en-us/windows/win32/com/impersonation Commented Jun 26, 2023 at 20:02

1 Answer 1

0

You might be able to use client_net_address, in sys.dm_exec_connections, to query the AD to see who is logged in at that IP.

2
  • This sounds quite interesting idea. Which AD attributes / whatever should one look up in order to map a host with login?
    – vonPryz
    Commented Jun 26, 2023 at 12:29
  • Bit useless if everyone is behind NAT on the same address. And it only tells you the IP address, not the username, there may be multiple users using that machine. Commented Jun 26, 2023 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.