如何从SQL Server 2008本身获取客户端IP地址？

Question

我有一个用于插入/更新/删除的触发器，已经正常工作。我还需要从进行更改的客户端IP地址。我需要在T-SQL中获取它，也就是说，在我的触发器被激活时，不是在任何Web表单中。

我进行了谷歌搜索，并得知主数据库中有一个存储过程名为xp_cmdshell，当使用ipconfig执行时，我们可以获取IP地址。但我认为只有在您拥有数据库管理员访问权限时才能使用此方法。我的主机是共享主机，因此我没有这样的特权。是否有其他方法可行？

请注意：我没有SQL Server 2008数据库上的管理员权限。我需要一个身份验证用户的解决方案。

另一个更新：

我已经找到了解决方案，适用于我的场景的查询是：

SELECT hostname, net_library, net_address
FROM sys.sysprocesses 
WHERE spid = @@SPID

代码会根据需要执行，但是有一个问题，net_address不符合IP格式。以下是我的结果:

hostname    net_library     net_address
IPC03       TCP/IP          AE8F925461DE  

我很想知道：

1. 这里的net_address是什么？是MAC地址还是某些IP地址等等？

2. 是否有将net_address转换为IP的方法？

- user240141

---

你提到你的数据库是托管的，但应用程序是如何工作的呢？在大多数ASP.NET应用程序中，用户连接到网站，然后网站再连接到数据库。因此，数据库服务器不知道“真正”的最终用户是谁或在哪里。如果您可以提供有关您的应用程序以及用户如何连接到数据库的更多详细信息，那么可能会有人提出建议。 - Pondlife

你可以从 http://msdn.microsoft.com/en-us/library/ms179881.aspx 找到关于 net_address 的详细信息。 - huMpty duMpty

Answer 1

我发现了一些可能适合你的东西。

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;
END

从如何在SQL Server中获取客户端IP地址了解更多信息。

此外，请查看这篇关于获取客户端IP地址的文章。

- huMpty duMpty

---

找到了相同的，但你赢了20秒 :) - Peter Aron Zentai

嗨，查询似乎没问题，但我收到了一个错误消息 - 用户没有执行此操作的权限。这里也出现了相同的情况...我没有管理员特权。 - user240141

@AmitRanjan：是因为您没有创建函数的权限还是无法从sys.dm_exec_connections中进行选择？ - Ben Thul

@AmitRanjan 从 sys.dm_exec_connections 进行选择需要 VIEW SERVER STATE 权限 http://msdn.microsoft.com/en-us/library/ms181509.aspx - Bryan

正如 @AmitRanjan 指出的那样，此方法需要访问 sys.dm_exec_connections 的权限，这可能用户没有。建议按 Dmitry 提供的 select CONNECTIONPROPERTY('client_net_address') 方法进行操作。 - Kenneth Xu

Answer 2

你可以尝试使用这个解决方案，它甚至适用于共享托管：

select CONNECTIONPROPERTY('client_net_address') AS client_net_address 

- Dmitry

---

该链接指出，您可以使用CONNECTIONPROPERTY('local_net_address') AS local_net_address以及许多其他CONNECTIONPROPERTY变量。 - Trisped

这是更好的解决方案，因为它不需要读取sys.dm_exec_connections的权限。 - Kenneth Xu

这显示了在 SSMS 的情况下您的本地地址。但是，在通过 SPID 发出查询的情况下，运行客户端的 SPID 是谁？这是不合适的。 - MarmiK

Answer 3

最终将这两个系统表连接起来：

SELECT  hostname,
        net_library,
        net_address,
        client_net_address
FROM    sys.sysprocesses AS S
INNER JOIN    sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE   spid = @@SPID

输出：

hostname | net_library | net_address | client_net_address    
PIERRE   | TCP/IP      | 0090F5E5DEFF| 10.38.168.5

- Pierre

Answer 4

仅需一行代码即可实现it技术

 SELECT CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

- Sukhdevsinh Zala

---

如果您正在生产环境中工作，这可能无法正常工作。客户端地址只是您的机器地址，而不是执行客户端的地址。Pierre上面的答案更为合适。 - MarmiK

Answer 5

以下查询返回客户端机器（而非服务器）的IP地址和工作站名称。

SELECT CONNECTIONPROPERTY('client_net_address') AS IpAddress,
       HOST_NAME() AS ClientMachineName

参考资料: https://learn.microsoft.com/zh-cn/sql/t-sql/functions/host-name-transact-sql?view=sql-server-ver15

示例

以下示例创建一个表，使用 HOST_NAME() 函数在默认定义中记录向 orders 表插入行的计算机的工作站名称。

CREATE TABLE Orders_demo  
(
  OrderID     INT        PRIMARY KEY,  
  OrderDate   DATETIME   NOT NULL,
  Workstation NCHAR(30)  NOT NULL     DEFAULT HOST_NAME()
);  

- Vignesh Raja

Answer 6

由于上述语句的限制，我无法获得准确的数字IP地址，而是得到了一个NULL值。该限制是仅在通过TCP/IP连接时才能获取IP地址。如果您是本地用户并且使用共享内存，则这些属性不存在。如果您通过服务器配置管理器关闭共享内存（或除TCP/IP之外的任何协议），则始终会为任何连接获取IP地址。

您最好坚持使用

SELECT SERVERPROPERTY(N'MachineName');

...可以代替数字IP地址。

- Chagbert

Answer 7

试试这个

DECLARE @IP_Address varchar(255);
SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

select @IP_Address;

- Kovid Purohit