SQL - 查询以获取服务器的 IP 地址

SQL - Query to get server's IP address

提问by Seibar

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

我可以使用 SQL Server 2005 中的查询来获取服务器的 IP 或名称吗？

回答by Jeff Muzzy

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;

此处的代码将为您提供 IP 地址；

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

这适用于对 SQL 2008 和更新版本的远程客户端请求。

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'.

• “共享内存”作为“net_transport”的值，以及
• 'local_net_address' 为 NULL，以及
• “ <local machine>”将显示在“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.

'client_net_address' 是发出请求的计算机的地址，而 'local_net_address' 将是 SQL 服务器（因此共享内存连接为 NULL），以及如果某人不能使用服务器的 NetBios，您将提供给某人的地址名称或 FQDN 出于某种原因。

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

我强烈建议不要使用这个答案。在生产 SQL Server 上启用外壳是一个非常糟糕的主意。

回答by Brian R. Bondy

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:

或者，正如@GilM 指出的那样：

SELECT SERVERPROPERTY('MachineName')

You can get the actual IP address using this:

您可以使用以下方法获取实际 IP 地址：

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.

SQL 脚本的来源。

回答by s-k-y-e---c-a-p-t-a-i-n

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:

服务器可能有多个正在侦听的 IP 地址。如果您的连接被授予 VIEW SERVER STATE 服务器权限，您可以运行此查询以获取您已连接到 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.

此解决方案不需要您通过 xp_cmdshell 向 OS 进行 shell，这是一种应在生产服务器上禁用（或至少严格保护）的技术。它可能需要您将 VIEW SERVER STATE 授予适当的登录名，但这比运行 xp_cmdshell 的安全风险要小得多。

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

GilM 提到的服务器名称技术是首选技术：

SELECT SERVERPROPERTY(N'MachineName');

回答by Dave Mason

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

大多数通过 t-sql 获取 IP 地址的解决方案都属于这两个阵营：

1. Run ipconfig.exevia xp_cmdshelland parse the output

2. Query DMV sys.dm_exec_connections

1. 运行ipconfig.exe通过xp_cmdshell并解析输出

2. 查询车管所 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:

我不喜欢选项#1。启用 xp_cmdshell 存在安全缺陷，并且无论如何都涉及大量解析。那很麻烦。选项#2 很优雅。这是一个纯粹的 t-sql 解决方案，我几乎总是喜欢它。以下是选项 #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.

有时，虽然上述查询都不起作用。如果您通过共享内存进行连接（登录并在 SQL 主机上运行 SSMS），则查询 #1 将返回 NULL。如果没有使用非共享内存协议的连接，查询 #2 可能不会返回任何内容。当连接到新安装的 SQL 实例时，可能会出现这种情况。解决方案？强制通过 TCP/IP 建立连接。为此，请在 SSMS 中创建一个新连接，并在服务器名称中使用“tcp:”前缀。然后重新运行任一查询，您将获得 IP 地址。

回答by Micha? Piaskowski

It's in the @@SERVERNAME variable;

它在@@SERVERNAME变量中；

SELECT @@SERVERNAME;

回答by Hank Freeman

--Try this script it works to my needs. Reformat to 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
      end 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;

回答by Ranjana Ghimire

you can use command line query and execute in mssql:

您可以使用命令行查询并在 mssql 中执行：

exec xp_cmdshell 'ipconfig'

回答by Pseudo Masochist

select @@servername

回答by GilM

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

在没有 \InstanceName 的情况下获取机器名称的更简单方法是：

SELECT SERVERPROPERTY('MachineName')

回答by Bert Van Landeghem

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.

我知道这是一篇旧帖子，但是当您想从共享内存连接（例如，从服务器本地 SSMS 中运行的脚本）检索 IP 地址和 TCP 端口时，此解决方案可能很有用。关键是使用 OPENROWSET 打开与 SQL Server 的辅助连接，在连接字符串中指定“tcp:”。其余的代码只是构建动态 SQL 来绕过 OPENROWSET 无法将变量作为其参数的限制。

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