SQL - Query to get server's IP address

Is there a query in SQL Server 2005 I can use to get the server's IP or name?

Solution 1:

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.

Solution 2:

You can get the[hostname]\[instancename] by:

SELECT @@SERVERNAME;

To get only the hostname when you have hostname\instance name format:

SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1)

Alternatively as @GilM pointed out:

SELECT SERVERPROPERTY('MachineName')

You can get the actual IP address using this:

create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
          set @ip = NULL
          Create table #temp (ipLine varchar(200))
          Insert #temp exec master..xp_cmdshell 'ipconfig'
          select @ipLine = ipLine
          from #temp
          where upper (ipLine) like '%IP ADDRESS%'
          if (isnull (@ipLine,'***') != '***')
          begin 
                set @pos = CharIndex (':',@ipLine,1);
                set @ip = rtrim(ltrim(substring (@ipLine , 
               @pos + 1 ,
                len (@ipLine) - @pos)))
           end 
drop table #temp
set nocount off
end 
go

declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip

Source of the SQL script.

Solution 3:

The server might have multiple IP addresses that it is listening on. If your connection has the VIEW SERVER STATE server permission granted to it, you can run this query to get the address you have connected to SQL Server:

SELECT dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;

This solution does not require you to shell out to the OS via xp_cmdshell, which is a technique that should be disabled (or at least strictly secured) on a production server. It may require you to grant VIEW SERVER STATE to the appropriate login, but that is a far smaller security risk than running xp_cmdshell.

The technique mentioned by GilM for the server name is the preferred one:

SELECT SERVERPROPERTY(N'MachineName');