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?

  • Select max key from joined keys
  • How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  • create Dynamic where in sql SP
  • powershell extract a value from a table
  • Invalid length parameter passed to the SUBSTRING function behavior when called from a job
  • How to select the records that are NOT MAX() in query
  • 9 Solutions collect form web for “SQL – Query to get server's IP address”

    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.

    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.

    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');
    

    It’s in the @@SERVERNAME variable;

    SELECT @@SERVERNAME;
    

    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 fan 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 a newly installed SQL instance. The solution? Force a connection over TCP/IP. To do 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.

    SSMS - Connect to Database Engine

    select @@servername
    

    A simpler way to get the machine name without the \InstanceName is:

    SELECT SERVERPROPERTY('MachineName')
    

    you can use command line query and execute in mssql:

    exec xp_cmdshell 'ipconfig'
    

    I know this is an old post, but perhaps this solution can be usefull when you want to retrieve the IP address and TCP port from a Shared Memory connection (e.g. from a script run in SSMS locally on the server). The key is to open a secondary connection to 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 get around OPENROWSET’s limitation of not being able to 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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.