locked
What windows account used a SQL Server login to access SQL Server RRS feed

  • Question

  • If we have a "pool" SQL login, a one that uses SQL Server authentication, and this login is used by different domain account to access SQL Server, is there a way to audit which domain account used that "pool" login to do something on a object in SQL Server?

    Note: that I have to keep this way of accessing SQL Server, so please do not suggest to create a login for every domain account accesses SQL Server

    Thursday, May 14, 2015 6:31 AM

Answers

  • If I understood correctly, your requirement is something like:

    • There are people who are logged-in to the machine using domain account, say Domain\User
    • They are connecting to SQL Server using SQL Server login, say "dbuser"
    • Now you want to trace who is the original login (The one who logged into the machine) and using this "dbuser"

    If my understanding is correct then I have to say that that may not be possible, as far as what I have known, SQL Server will represent this user "dbuser" under login_name, nt_domain = NULL and nt_user_name = NULL in SELECT * FROM sys.dm_exec_sessions or other system views but you will still be able to retrieve host_name in this DMV.

    At the application level (Say .NET) you can get this information using CurrentContextID or something similar

    Let me know if that is helpful



    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Butmah Thursday, May 14, 2015 10:40 AM
    Thursday, May 14, 2015 8:10 AM
  • No, you can't. For all SQL Server cares, there may not even be a domain account on the other end, as the connection could from a machine running VMS or whatever.

    What you can track in SQL Server is the IP address, the host name and the process id on the connecting machine. Then you need some another audit to say who was connected to that machine at a certain point in time.

    Note that all these three be spoofed, but the IP address and the process ID are fairly reliable, as spoofing them requires that you craft your own network packets. The host name can be set in the connection string.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Butmah Thursday, May 14, 2015 10:40 AM
    Thursday, May 14, 2015 8:52 AM

All replies

  • https://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

    check and confirm

    Thursday, May 14, 2015 6:44 AM
  • Dinesh! can you please point to the paragraph in the long _very long_ link that you sent me, which answers my question?!

    The article in the link that you sent is explaining generally how to create audit in SQL Server, and I can't find an answer to what I've asked!

    Please read carefully the questions before you start answering ... at least when I asked!


    • Edited by Butmah Thursday, May 14, 2015 7:23 AM
    Thursday, May 14, 2015 7:21 AM
  • Hmm is that what you have on the server? But I am getting error...

    USE [B]
    GO
    CREATE USER [pool] FOR LOGIN [uri] WITH DEFAULT_SCHEMA=[dbo]
    GO
    GO
    CREATE USER [pool] FOR LOGIN [domain\uri] WITH DEFAULT_SCHEMA=[dbo]
    GO
    --Msg 15063, Level 16, State 1, Line 1
    --The login already has an account under a different user name.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 14, 2015 7:59 AM
  • If I understood correctly, your requirement is something like:

    • There are people who are logged-in to the machine using domain account, say Domain\User
    • They are connecting to SQL Server using SQL Server login, say "dbuser"
    • Now you want to trace who is the original login (The one who logged into the machine) and using this "dbuser"

    If my understanding is correct then I have to say that that may not be possible, as far as what I have known, SQL Server will represent this user "dbuser" under login_name, nt_domain = NULL and nt_user_name = NULL in SELECT * FROM sys.dm_exec_sessions or other system views but you will still be able to retrieve host_name in this DMV.

    At the application level (Say .NET) you can get this information using CurrentContextID or something similar

    Let me know if that is helpful



    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by Butmah Thursday, May 14, 2015 10:40 AM
    Thursday, May 14, 2015 8:10 AM
  • I dont think its possible to have such an audit at the SQL server side as you are sharing the sql user id among a set of windows users.

    May be you could create a group (like SQLUSERGROUP) in the Active directory, add the windows ids to this group and finally add the new group in SQL Server. I think this would be a better approach than using shared sql user id


    Satheesh
    My Blog | How to ask questions in technical forum

    Thursday, May 14, 2015 8:31 AM
  • No, you can't. For all SQL Server cares, there may not even be a domain account on the other end, as the connection could from a machine running VMS or whatever.

    What you can track in SQL Server is the IP address, the host name and the process id on the connecting machine. Then you need some another audit to say who was connected to that machine at a certain point in time.

    Note that all these three be spoofed, but the IP address and the process ID are fairly reliable, as spoofing them requires that you craft your own network packets. The host name can be set in the connection string.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Butmah Thursday, May 14, 2015 10:40 AM
    Thursday, May 14, 2015 8:52 AM