SQL – Query to get server’s IP address

by 
SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

The code here Will give you the IP Address;

This will work for a remote client request to SQL 2008 and newer.

If you have Shared Memory connections allowed, then running above on the server itself will give you

  • “Shared Memory” as the value for ‘net_transport’, and
  • NULL for ‘local_net_address’, and
  • ‘<local machine>‘ will be shown in ‘client_net_address’.

‘client_net_address’ is the address of the computer that the request originated from, whereas ‘local_net_address’ would be the SQL server (thus NULL over Shared Memory connections), and the address you would give to someone if they can’t use the server’s NetBios name or FQDN for some reason.

I advice strongly against using this answer. Enabling the shell out is a very bad idea on a production SQL Server.

More Related Contents:

  1. Case statement for multiple columns
  2. SQL where clause odd behavior [closed]
  3. SQL Server Exec issue [closed]
  4. Count(*) vs Count(1) – SQL Server
  5. How do I escape a single quote in SQL Server?
  6. Only inserting a row if it’s not already there
  7. Unpivot with column name
  8. Selecting data from two different servers in SQL Server
  9. Is a view faster than a simple query?
  10. How to convert DateTime to VarChar
  11. Most efficient way in SQL Server to get date from date+time?
  12. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  13. Stored Procedure with optional “WHERE” parameters
  14. SQL Server: Howto get foreign key reference from information_schema?
  15. SCOPE_IDENTITY() for GUIDs?
  16. Should I design a table with a primary key of varchar or int?
  17. How do you import a large MS SQL .sql file?
  18. How do I make a composite key with SQL Server Management Studio?
  19. Difference between Subquery and Correlated Subquery
  20. How to execute .sql file using powershell?
  21. Get previous and next row from rows selected with (WHERE) conditions
  22. Does Sql JOIN order affect performance?
  23. Best equivalent for IsInteger in SQL Server
  24. Syntax of for-loop in SQL Server
  25. Using tuples in SQL “IN” clause
  26. Why does ISNUMERIC(‘.’) return 1?
  27. Can SQL Server Express LocalDB be connected to remotely?
  28. Group consecutive rows of same value using time spans
  29. Why do SQL Server Scalar-valued functions get slower?
  30. SQL add filter only if a variable is not null

Leave a Comment