How to find destination IP address of a linked server in SQL Server 2008?

How can I find the address of defined linked server in SQL Server开发者_如何学运维 2008? The link server is present and its connection test shows there is no problem. but I can not find where the link server connects to. The properties box did not lead to the answer.

Thank you.

From inside SQL Server if the target is SQL Server 2005+

SELECT * FROM OPENQUERY (
         myLinkedServer, 
         'SELECT
             @@SERVERNAME AS TargetServerName,
             SUSER_SNAME() AS ConnectedWith,
             DB_NAME() AS DefaultDB,
             client_net_address AS IPAddress
          FROM
              sys.dm_exec_connections
          WHERE
              session_id = @@SPID
        ')

You can adapt this anyway you want of course.

If your linked server connected to another MSSQL DB then you can simply ping target server name from command line: ping serverName. To determine target server name see properties of your linked server. See for details this: http://msdn.microsoft.com/en-us/library/ff772782.aspx#SSMSProcedure. In this article target server name is "Accounting"

I tried gbn's answer but due to lacking permission I got these errors:

The user does not have permission to perform this action.

VIEW SERVER STATE permission was denied on object 'server', database 'master'.

But taking his idea on running a query on the target server using OPENQUERY I derived this version:

SELECT * FROM OPENQUERY (
    myLinkedServer, 
    'select CONNECTIONPROPERTY ( ''client_net_address'' ) CLIENT_ADDRESS
          , CONNECTIONPROPERTY ( ''local_net_address'' ) SERVER_ADDRESS'
)

If you run this query directly:

select CONNECTIONPROPERTY ( 'client_net_address' ) CLIENT_ADDRESS
     , CONNECTIONPROPERTY ( 'local_net_address' ) SERVER_ADDRESS

CLIENT_ADDRESS would be the IP of your workstation, and SERVER_ADDRESS would be the IP of the server you are connecting to.

But if you run that via OPENQUERY then CLIENT_ADDRESS will be the server you are connecting to and SERVER_ADDRESS will be the target of the linked server.

More informatron on Connectionproperty can be found in: CONNECTIONPROPERTY (Transact-SQL) Microsoft documentation

Generally, this function can work without special permissions, unlike dynamic views, so it is very useful for restricted environments and for production code that must run with minimal permissions.