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

How to get the client IP address from SQL Server 2008 itself?

提问by Dmitry

I have a trigger for insert/update/delete. That is working fine. Also, I need the client's IP address from where the changes are made. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL while my trigger will get fired.

我有一个插入/更新/删除的触发器。那工作正常。另外，我需要进行更改的客户端的 IP 地址。我需要在 T-SQL 中，这意味着，不是在任何网络表单中，而是在 SQL/T-SQL 中，而我的触发器将被触发。

Also I go-ogled, and got that there is stored procedure in master database named xp_cmdshellwhich when executed with ipconfigwe can get the IP Address. I think this will work only when you have administrative access to database. Mine hosting is a shared hosting , so I don't have such privilege. Is there any other way out?

我也搜索了一下，发现 master 数据库中有一个名为的存储过程xp_cmdshell，当ipconfig我们使用它执行时，我们可以获得 IP 地址。我认为这仅在您对数据库具有管理访问权限时才有效。我的主机是共享主机，所以我没有这样的特权。还有其他出路吗？

Please help

请帮忙

Thanks in advance

提前致谢

Please note: I don't have administrative privileges on my SQL Server 2008 database. I need a solution as an authenticated user.

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

Another update:

另一个更新：

I have got the solution, the query that will work for my scenario is

我有解决方案，适用于我的场景的查询是

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

It executes as needed but there is only one issue, that net_address is not in IP format. below is mine result:

它根据需要执行，但只有一个问题，即 net_address 不是 IP 格式。以下是我的结果：

hostname    net_library     net_address
IPC03       TCP/IP          AE8F925461DE  

I am eager to know:

我很想知道：

1. What is net_address here? Is is MAC Address or Some IP address etc?

2. Is there any way to convert net_address to ip?

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

2. 有没有办法将net_address转换为ip？

Humble request:

谦虚的要求：

Before answering/commenting/downvoting, I would request you to go through the question, first, thoroughly. I found some guys commented/downvoted without properly going through the question. No issues, everybody makes mistakes. But not every time make mistake. :)

在回答/评论/否决之前，我会要求您首先彻底解决这个问题。我发现有些人在没有正确回答问题的情况下评论/否决了这个问题。没有问题，每个人都会犯错。但不是每次都会出错。:)

回答by huMpty duMpty

I found something which might work for you

我找到了可能对你有用的东西

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

From How to get Client IP Address in SQL Server

从如何在 SQL Server 中获取客户端 IP 地址

Also have a look at this article about Get client IP address

也看看这篇关于获取客户端IP地址的文章

回答by Dmitry

You can try out this solution. It even works on shared hosting:

您可以试试这个解决方案。它甚至适用于共享主机：

select CONNECTIONPROPERTY('client_net_address') AS client_net_address 

回答by Sukhdevsinh Zala

it needs just single line of code

它只需要一行代码

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

回答by Pierre

Ultimately join the two system tables:

最终加入两个系统表：

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

Output:

输出：

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

回答by Chagbert

I couldn't get the exact numeric IP address, instead I got a NULL value because of the limitation of the above statements. The limit is that you only get IP addresses if you're connected via TCP/IP. If you're local and using Shared Memory then those attributes don't exist. If you turn off Shared Memory (or any protocols except for TCP/IP) via Server Configuration Manager you will always get IP address for any connection.

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

You are best stuck with

你最好坚持

SELECT SERVERPROPERTY(N'MachineName');

... which can act in place of numeric IP address.

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

回答by Kovid Purohit

try this

尝试这个

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

select @IP_Address;