SQL - 查询服务器的IP地址

Question

在SQL Server 2005中，是否有一种查询可以用来获取服务器的IP或名称？

- Terrapin

---

当使用AG监听器连接到SQL时，CONNECTIONPROPERTY('local_net_address')返回的是监听器IP而不是服务器IP。 - brian beuning

SELECT CONNECTIONPROPERTY('local_net_address') AS [IPAddress]选择 CONNECTIONPROPERTY('local_net_address') 作为 [IPAddress] - Ali NajafZadeh

Answer 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 

以下代码将给出IP地址;

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

如果允许共享内存连接，则在服务器本身上运行以上内容将会给您：

• 'net_transport'的值为“Shared Memory”，
• 'local_net_address'的值为NULL，以及
• 'client_net_address'中会显示'<local machine>'。

'client_net_address'是请求来源计算机的地址，而'local_net_address'则是SQL服务器的地址（因此在共享内存连接上为NULL），如果某些原因不能使用服务器的NetBios名称或FQDN，则应该将其地址提供给其他人。

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

- Jeff Muzzy

---

回答很好，除了我得到一个负端口号（-15736），而应该是49800。 如果是负数，那么只加65536是安全的吗？ - crokusek

如果有人想远程登录我的计算机SQL服务器，那么我应该给他哪个IP地址？本地网络地址还是客户端网络地址？ - david blaine

@rene - 我曾考虑过这个问题，但原始答案本身是正确的（适用于SQL2008+和远程连接），所以我只是想澄清ConnectionProperty参数的含义。当然，Chris Leonard的答案（dm_exec_connections）也是正确的，原因相同。如果您认为我的补充更适合作为单独的答案，请随意这样做 :) 我已经有一段时间没有回答问题了，所以这里的政策和规则可能已经改变了（现在会去阅读它们...）。 - Martin S. Stoller

根据"http://stackoverflow.com/help/editing"的说明，添加补充内容的原因是为了澄清帖子的含义（而不改变该含义）。 - Martin S. Stoller

@MartinS.Stoller 我修改了你的添加以提高可读性。请检查我是否漏掉了什么。 - rene

我运行了上述查询并获得以下细节：共享内存 TSQL SQL NULL NULL <本地机器>.. 我在 local_tcp_port 中获得 NULL。我该怎么办？ - vikrant rana

Answer 2

你可以通过以下方式获取[hostname]\[instancename]:

SELECT @@SERVERNAME;

当你有主机名\实例名称格式时，只需获取主机名：

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

另外，正如@GilM所指出的：

SELECT SERVERPROPERTY('MachineName')

您可以使用以下内容获取实际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

SQL脚本来源。

- Brian R. Bondy

---

使用xp_cmdshell是一个坏主意，该过程应予删除，因为它是一种安全风险，允许访问它会授予任何能够执行它的人对"盒子"的访问权限。 - Walter Verhoeven

Answer 3

服务器可能有多个IP地址进行监听。如果您的连接被授予了VIEW SERVER STATE服务器权限，则可以运行此查询以获取您连接到SQL Server的地址：

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

这种解决方案不需要通过xp_cmdshell与操作系统进行交互，而这种技术在生产服务器上应该被禁用（或者至少要严格保护）。它可能需要您向适当的登录授予VIEW SERVER STATE权限，但这比运行xp_cmdshell带来的安全风险小得多。

GilM提到的使用服务器名称的技术是首选方法：

SELECT SERVERPROPERTY(N'MachineName');

- chris

---

感谢您的回复...我相信这是确定服务器IP地址的真正方法，如果您需要从客户端连接方面进行验证，特别是如果客户端连接是使用包含SQL别名或命名实例作为数据源的连接字符串建立的。 - Rodolfo G.

Answer 4

获取IP地址的T-SQL解决方案大多分为以下两种：

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

2. 查询DMV sys.dm_exec_connections

我不喜欢选项#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;

有时候，上述两个查询都无法正常工作。如果您通过共享内存连接（在SQL主机上登录并运行SSMS），则查询＃1返回NULL。如果没有使用非共享内存协议的连接，则查询＃2可能不会返回任何内容。当连接到新安装的SQL实例时，这种情况很可能发生。解决方案是强制使用TCP/IP进行连接。要做到这一点，在SSMS中创建一个新连接，并使用“tcp：”前缀与服务器名称。然后重新运行任一查询，您将获得IP地址。

- Dave Mason

---

这将提供ipv4..我们能获取ipv6吗？ - Shikhil Bhalla

这里可能需要澄清一下。共享内存连接只能被在与 SQL Server 相同的主机上运行的客户端使用，并且没有必要强制它们使用 TCP 来确定它们正在运行的位置。选项 #2 仍然返回正确的信息，其中 net_transport = 'Shared memory'，而不是 net_transport = 'TCP'。当您看到这个时，您就知道客户端在 SQL Server 上本身。如果您真的需要 IP 地址，则为 local_net_address 中的值，但请记住，客户端本身并未通过 TCP 连接。 - chris

Answer 5

--尝试这个脚本，它符合我的需求。重排以便阅读。

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;

- Hank Freeman

---

谢谢。我只需要更新dec.local_tcp_port为'dec.local_tcp_port'，以便在函数中使用它。否则会抱怨有两个名为local_tcp_port的列。 - Steven

Answer 6

这个变量是在 @@SERVERNAME 变量里;

SELECT @@SERVERNAME;

- Michał Piaskowski

Answer 7

请使用以下查询：

SELECT CONNECTIONPROPERTY('local_net_address') AS [IP]

- گلی

Answer 8

您可以在 MSSQL 中使用命令行查询和执行：

exec xp_cmdshell 'ipconfig'

- Ranjana Ghimire

---

只有在服务器安全配置中启用了xp_cmdshell，这个才会起作用。 - Javasick

Answer 9

select @@servername

- Pseudo Masochist

Answer 10

我知道这是一篇旧文章，但也许当你想从共享内存连接中检索IP地址和TCP端口（例如从在服务器本地运行的SSMS脚本）时，这个解决方案可能很有用。关键是使用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

- Bert Van Landeghem

---

只是注意，这需要打开“Ad Hoc Distributed Queries”。 - Dan