Get the IP address of a user in calling a stored procedure (SQL)

Question

Is it possible, and if so how, to get a user's remote IP address executing the query, analogously as we can get user's name with: SUSER_SNAME()?

Update before bounty

I am looking for a solution which allows to grab the IP address of an ordinary mortal user, not a database owner. The ideas proposed by TheGameiswar or njc do not allow to capture a user's IP address who has been granted just a execute permission. However, they are excellent ideas to start with the problem. Here I list the essence of the ideas:

Please see the sequence I follow:

create procedure MyStoredProcedure as
select client_net_address 
from sys.dm_exec_connections
where session_id = @@SPID

Now add a user and grant permission:

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

When I run the procedure with a query:

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT

I get error message:

The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

I will get this message even if I grant an additional permission:

grant VIEW SERVER STATE to [user_mortal_jack];

If I change the beginning of the stored procedure to:

create procedure MyStoredProcedure
with execute as OWNER as

I end up with different sort of error:

Could not obtain information about Windows NT group/user 'blahblah\admin_user', error code 0x534.

Update after bounty

Bounty is granted to Hadi for this single line of code hidden in their answer:

CONNECTIONPROPERTY('client_net_address')

which let's capture the IP address of any mortal user without neither granting any additional rights to the user nor setting the database TRUSTWORTHY ON option nor even creating a procedure WITH EXECUTE AS OWNER clause.

Change Stored procedure definition to CREATE PROCEDURE MyStoredProcedure WITH EXECUTE AS OWNER ... — Steve Ford, Nov 24 '17 at 13:12
if you are trying to wrap any of the below DMV's in a stored proc likethe way explained you above,check out this :https://www.mssqltips.com/sqlservertip/1227/granting-permission-with-the-execute-as-command-in-sql-server/ — TheGameiswar, Nov 27 '17 at 09:49

score 15 · Accepted Answer · edited Feb 20 '18 at 22:20

General Info

There are two ways to get the current connection information

Getting information from Dynamic Management Views

SELECT
conn.session_ID as SPID,
conn.client_net_address as IPAddress,
sess.host_name as MachineName,
sess.program_name as ApplicationName,
login_name as LoginName
FROM sys.dm_exec_connections conn
inner join sys.dm_exec_sessions sess
on conn.session_ID=sess.session_ID

Using CONNECTIONPROPERTY function (SQL Server 2008 and newer version):

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

Suggested Solutions

If you are looking to grant user for a specific IP address

CREATE PROCEDURE MyStoredProcedure AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))

    IF @IP_Address = 'XXX.XXX.XXX.XXX'
    SELECT TOP 1 FROM tb

END

Assuming that you have a table that contains the granted IP address (i.e. TBL_IP)

CREATE PROCEDURE MyStoredProcedure AS BEGIN DECLARE @IP_Address varchar(255);

SELECT @IP_Address = CAST(CONNECTIONPROPERTY('client_net_address') as varchar(200))

IF EXISTS (SELECT 1 FROM TBL_IP WHERE [IP] = @IP_Address )
SELECT TOP 1 FROM tb

END

If you are looking to grant a user (database user) to execute a stored procedure, you should use this command

GRANT EXECUTE ON MyStoredProcedure TO User;

There are many detailed article and answers talking about the issue you are facing, and many suggested solutions, such as Setting the Database in TRUSTWORTHY mode (before using it read the first Link below) and Trusting the Authenticator, and other methods. You can find them in the links below
- SQL Server EXECUTE AS trouble
- SQL Server execute procedure as user
- Extending Database Impersonation by Using EXECUTE AS (Extending the Scope of Impersonation section)
- EXECUTE AS (Transact-SQL)
- Execute a stored procedure as another user premission
Note: You can check @SteveFord answer for using TRUSTWORTHY property
If you are looking to block connections except specific IP addresses then you should follow this answer
- Restrict SQL Server connection to specific IP address
Also there are many scripts that can be used to get client or server IP addresses that can be found in the question below:
- How to get a client IP address from an SQL Server database

References

@PrzemyslawRemin i updated my answer, i added new links and new methods, please take a look — Hadi, Nov 26 '17 at 17:32
@PrzemyslawRemin try using `SELECT CONNECTIONPROPERTY('client_net_address') AS client_net_address ` — Hadi, Nov 26 '17 at 17:32
`CONNECTIONPROPERTY('client_net_address') AS client_net_address` seems to work, making you candidate for bounty. Some parts of your answer will lead to issues I mention in my question. Please consider reviewing it. — Przemyslaw Remin, Nov 27 '17 at 10:51
@PrzemyslawRemin please take a look https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql#_user i think this is what you are looking for — Hadi, Nov 27 '17 at 11:53
@PrzemyslawRemin also take a look here https://stackoverflow.com/questions/2819367/execute-stored-procedure-as-another-user-premission — Hadi, Nov 27 '17 at 11:56
`user_mortal_jack` won't be able to select from `sys.dm_exec_connections` unless some extra privileges are granted which you do not mention. May I ask you if you agree that selecting from `sys.dm_exec_connections` is misleading and unnecessary part of your answer. — Przemyslaw Remin, Nov 27 '17 at 13:46
@PrzemyslawRemin i prefer to use `CONNECTIONPROPERTY` instead of `sys.dm_exec_connections` but in the answer i wrote it in comments as another method. So i agree with you — Hadi, Nov 27 '17 at 14:05
Also in the link they recommended that the user has a read access or it is a sysadmin — Hadi, Nov 27 '17 at 14:06
@PrzemyslawRemin try create a user for the Login `CREATE USER [user_mortal_jack] FOR LOGIN [user_mortal_jack] WITH DEFAULT_SCHEMA=[dbo]` — Yahfoufi, Nov 27 '17 at 16:30
@PrzemyslawRemin i updated my answer to be more formatted and clear. and i added some useful Links — Hadi, Nov 27 '17 at 19:04

score 4 · Answer 2 · answered Nov 20 '17 at 15:25

4

You can use connections DMV to accomplish that..

select ec.client_net_address,* from sys.dm_exec_connections ec
join
sys.dm_exec_requests rq
on rq.connection_id=ec.connection_id
cross apply
sys.dm_exec_sql_text(rq.sql_handle) txt
where txt.text like '%your stored proc%'

MSDN for client_net_address

Host address of the client connecting to this server. Is nullable.

answered Nov 20 '17 at 15:25

TheGameiswar

27,855
8
56
94

This is good as long as db owner executes query, but pointless as well. When ordinary mortal user executes such query he ain't got no permission right to read neither from sys.dm_exec_requests nor from sys.dm_exec_sql_text. Is there a workaround? – Przemyslaw Remin Nov 21 '17 at 09:17
This kind of DMV requires more access level and this must be designed for a reason – TheGameiswar Nov 21 '17 at 09:18
That is paradox. User's ip address would be handy for security reason (to verify if user can run sp), and for the same reason it is not allowed:-) – Przemyslaw Remin Nov 21 '17 at 09:28
I don't see any other way other than running profiler,not sure of access level – TheGameiswar Nov 21 '17 at 11:39

Steve Ford · Answer 3 · 2017-11-27T11:56:11.493

Using the EXECUTE AS OWNER statement in a CREATE PROCEDURE Statement:

From MSDN

When a user executes a module that has been specified to run in a context other than CALLER, the user's permission to execute the module is checked, but additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. The user executing the module is, in effect, impersonating the specified user.

The context specified in the EXECUTE AS clause of the module is valid only for the duration of the module execution. Context reverts to the caller when the module execution is completed.

The following must be created by a user who has permissions to query the DMVs

CREATE PROCEDURE MyStoredProcedure
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON
   SELECT TOP 1
   FROM tb
   INNER JOIN sys.dm_exec_connections cn
         ON tb.client_net_address = cn.client_net_address
   WHERE cn.Session_Id = @@SPID
END

Then you will need to give the users permissions to execute the stored procedure:

Update to create the right permissions

You will need to set your database to Trustworthy (see Set Database to Trustworthy:

ALTER DATABASE MyDataBase SET TRUSTWORTHY ON

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';

CREATE USER [user_mortal_jack] FOR LOGIN [user_mortal_jack] WITH DEFAULT_SCHEMA=[dbo]
GO

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];

I have tested this and this now works as expected

`with execute as OWNER` does not help. Please see my updated answer for error message I get. — Przemyslaw Remin, Nov 27 '17 at 10:38
@PrzemyslawRemin see my update, I have tested this and this now works — Steve Ford, Nov 27 '17 at 11:57

score 1 · Answer 4 · edited Feb 20 '18 at 22:45

In order to get any caller's IP address and user name without granting all of them special permissions, you could cheat the server and database a little bit. A couple of things are needed to achieve this:

ALTER DATABASE MyDataBase SET TRUSTWORTHY ON
Create a login (not [user_mortal_jack]) in my example [user_immortan_joe]
Create a user for [user_immortan_joe] in MyDataBase
In the context of master grant VIEW SERVER STATE to [user_immortan_joe];
In MyDataBase create a stored procedure (not MyStoredProcedure, in my example get_ip) that is receiving an int representing a specific session_id parameter will output (output, not return) the IP address or that session_id. Create it with execute as 'user_immortan_joe'.
Create MyStoredProcedure in a manner that with the help the get_ip and of SUSER_SNAME() returns the IP address and user name of its caller.

This way you get the IP address and user name of any caller of MyStoredProcedure respecting the principle of least privilege and avoiding the problems you've encountered while pursuing a solution.

Sample script:

use MyDataBase
go
alter database MyDataBase set  trustworthy on;
go

CREATE LOGIN [user_mortal_jack] WITH PASSWORD=N'LongYouLive!!!';
go
create user [user_mortal_jack];
go

CREATE LOGIN [user_immortan_joe] WITH PASSWORD=N'ToTheGatesOfValhalla!!!';
go
create user [user_immortan_joe];
go

use master
go
grant VIEW SERVER STATE to [user_immortan_joe];

use MyDataBase
go


create  PROCEDURE get_ip
@spid int, @ip varchar(50) output
with execute as 'user_immortan_joe'
as
begin
    select @ip = client_net_address
    from sys.dm_exec_connections
    where session_id =  @spid
end;
go

create procedure MyStoredProcedure
as
begin
    declare @spid int = @@spid, @ip varchar(50);
    exec dbo.get_ip @spid,@ip output;
    select @ip as ipAddress ,SUSER_SNAME() as userName
end
go

GRANT EXECUTE ON MyStoredProcedure TO [user_mortal_jack];
go

EXECUTE AS USER = 'user_mortal_jack'
exec MyStoredProcedure
REVERT

Get the IP address of a user in calling a stored procedure (SQL)

4 Answers4

General Info

Suggested Solutions