How to know computer name that access SQL Server via remote

johanna

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

pdj

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

johanna

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..

Slawek_Guzek

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

johanna

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! ^^