How to know computer name that access SQL Server via remote

johannajohanna Member Posts: 369
edited 2011-01-04 in SQL General
Dear all,

When I am on computer A, I remote computer B and open Microsoft SQL Server Management Studio (SQL Server 2005).
On computer B, I have used 'sys.dm_exec_sessions' and got the result that the host name is computer B. How to know that SQL is accessed from computer A via remote access with SQL query?

Thanks before :)
Best regards,



  • pdjpdj Member Posts: 643
    I doubt very much SQL is able to find that using system views or tables.

    I guess you will need to make a stored procedure that is using some Citrix or Remote Desktop API to find the actual client. I'll suggest a Citrix or Remote Desktop forum instead.

    But it would be great if you share it here if you find a solution - I'm sure other NAV developers could use it :)
  • johannajohanna Member Posts: 369
    Hi Peter,

    Thank you for your suggestion.. :)
    I will try to ask in Citrix or Remote Desktop forum..
    Sure, I will share it here if I find a solution..
    Once again, thank you Peter.. :)
    Best regards,

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    I don't think if it is possible at all to do it right. You may try to employ some WMI Queries from T-SQL..

    If you think of it T-SQL queries are executed at SQL server machine. So the code (even .NET CLR) has access to SQL Server environment.

    You may want to use HOST_NAME() in T_SQL to get machine from which the connection to SQL Server was made. Yet it will be Citrix/RDP server name, not the local workstation from where connection to Windows/Citrix server was initiated.

    In case of Citrix/Windows2008 there is environment variable CLIENTNAME storing current workstation name from where ICA/RDP connection has been initiated. So tracing back connection to the Citrix/terminal server (using HOST_NAME()), then retrieving list of logged users, finding 'your' user environmnet (using remote WMI Query), then examining CLIENTNAME environment variable could get original workstation name.

    But... what if the same user is logged twice on the same Citrix/terminal server ? From two different machines ? What if connection to Citrix/Windows was made from another RDP/Citrix connection ?

    Perhaps writing your own .NET CLR assembly would be the simplest option, at least getting access to various internal system data or using WMI would be fairly simple

    Anyway - have fun :)

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • johannajohanna Member Posts: 369
    Hi Slawek,

    Thank you for your suggestion.. I really appreciate it..
    It looks like complicated to know computer name that access SQL Server via remote.
    I'll try it later if I have free enough time.
    Thanks again! ^^
    Best regards,

Sign In or Register to comment.