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?
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:
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:
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:
It’s in the @@SERVERNAME variable;
Most solutions for getting the IP address via t-sql fall into these two camps:
xp_cmdshelland parse the output
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.
A simpler way to get the machine name without the \InstanceName is:
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