SQL - Query to get server's IP address

asked Sep 26, 2008 at 21:37

SeibarSeibar

70.2k38 gold badges93 silver badges100 bronze badges

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 tát 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
• '' 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 tát 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.

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
          phối @ip = NULL
          Create table #temp (ipLine varchar(200))
          Insert #temp exec master..xp_cmdshell 'ipconfig'
          select @ipLine = ipLine
          from #temp
          where upper (ipLine) lượt thích '%IP ADDRESS%'
          if (isnull (@ipLine,'***') != '***')
          begin 
                phối @pos = CharIndex (':',@ipLine,1);
                phối @ip = rtrim(ltrim(substring (@ipLine , 
               @pos + 1 ,
                len (@ipLine) - @pos)))
           kết thúc 
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.

The server might have multiple IP addresses that it is listening on. If your connection has the VIEW SERVER STATE server permission granted to tát it, you can run rẩy this query to tát get the address you have connected to tát 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 tát shell out to tát 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 tát grant VIEW SERVER STATE to tát the appropriate login, but that is a far smaller security risk than vãn running xp_cmdshell.

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

SELECT SERVERPROPERTY(N'MachineName');

Most solutions for getting the IP address via t-sql fall into these two camps:

1. Run ipconfig.exe via xp_cmdshell and parse the output

2. Query DMV sys.dm_exec_connections

I'm not a người yêu thích of option #1. Enabling xp_cmdshell has security drawbacks, and there's lots of parsing involved anyway. That's cumbersome. Option #2 is elegant. And it's a pure t-sql solution, which I almost always prefer. Here are two sample queries for option #2:

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

SELECT TOP(1) c.local_net_address
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;

Sometimes, neither of the above queries works, though. Query #1 returns NULL if you're connected over Shared Memory (logged in and running SSMS on the SQL host). Query #2 may return nothing if there are no connections using a non-Shared Memory protocol. This scenario is likely when connected to tát a newly installed SQL instance. The solution? Force a connection over TCP/IP. To vì thế this, create a new connection in SSMS and use the "tcp:" prefix with the server name. Then re-run either query and you'll get the IP address.

--Try this script it works to tát my needs. Reformat to tát read it.

SELECT  
SERVERPROPERTY('ComputerNamePhysicalNetBios')  as 'Is_Current_Owner'
    ,SERVERPROPERTY('MachineName')  as 'MachineName'
    ,case when  ServiceName = 
    Right ( Servername,len( ServiceName)) then  Servername 
      else  servername +' \ ' +  Servicename
      kết thúc as ' Servername \ Servicename',  
    CONNECTIONPROPERTY('net_transport') AS net_transport,
    CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
    dec.local_tcp_port,
    CONNECTIONPROPERTY('local_net_address') AS local_net_address,
    dec.local_net_address as 'dec.local_net_address'
    FROM sys.dm_exec_connections AS dec
    WHERE dec.session_id =  SPID;

answered May 11, 2021 at 9:21

گلیگلی

2404 silver badges14 bronze badges

Seibar

70.2k38 gold badges93 silver badges100 bronze badges

answered Sep 26, 2008 at 21:38

you can use command line query and execute in mssql:

exec xp_cmdshell 'ipconfig'

answered Sep 26, 2008 at 23:49

GilMGilM

3,76119 silver badges18 bronze badges

I know this is an old post, but perhaps this solution can be usefull when you want to tát retrieve the IP address and TCP port from a Shared Memory connection (e.g. from a script run rẩy in SSMS locally on the server). The key is to tát open a secondary connection to tát your SQL Server using OPENROWSET, in which you specify 'tcp:' in your connection string. The rest of the code is merely building dynamic SQL to tát get around OPENROWSET’s limitation of not being able to tát take variables as its parameters.

DECLARE @ip_address       varchar(15)
DECLARE @tcp_port         int 
DECLARE @connectionstring nvarchar(max) 
DECLARE @parm_definition  nvarchar(max)
DECLARE @command          nvarchar(max)

SET @connectionstring = N'Server=tcp:' +  SERVERNAME + ';Trusted_Connection=yes;'
SET @parm_definition  = N'@ip_address_OUT varchar(15) OUTPUT
                        , @tcp_port_OUT   int         OUTPUT';

SET @command          = N'SELECT  @ip_address_OUT = a.local_net_address,
                                  @tcp_port_OUT   = a.local_tcp_port
                          FROM OPENROWSET(''SQLNCLI''
                                 , ''' + @connectionstring + '''
                                 , ''SELECT local_net_address
                                          , local_tcp_port
                                     FROM sys.dm_exec_connections
                                     WHERE session_id =  spid
                                   '') as a'

EXEC SP_executeSQL @command
                 , @parm_definition
                 , @ip_address_OUT = @ip_address OUTPUT
                 , @tcp_port_OUT   = @tcp_port OUTPUT;


SELECT @ip_address, @tcp_port

answered Mar 28, 2023 at 19:22

armadillo.mxarmadillo.mx

9911 gold badge11 silver badges18 bronze badges

It is possible to tát use the host_name() function

select HOST_NAME()