Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

EXECUTE AS Permissions To Query system tables requireing View Server State

Posted on 2011-09-12
10
Medium Priority
?
126 Views
Last Modified: 2016-06-17
I'm trying to put the following query into a trigger (after declaring the variables):

  SELECT @IPAddress = c.client_net_address
      ,@Port = c.local_tcp_port
      ,@Host = s.host_name
      ,@Program = s.program_name
      ,@Login      = s.login_name
  FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON
      s.session_id = c.session_id
  WHERE s.session_id = @@spid

When I log into the server as 'sa' and run it from a New Query window in SQL 2008, it runs fine; however, if I try to hit it from a client (.NET web) application (which comes in as a user with limited permissions), I get an error stating that I don't have adequate permissions.

If I try to wrap the query in the following:
    EXECUTE AS USER = 'dbo'
    REVERT

I get Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

If I try altering the trigger to WITH EXECUTE AS SELF, I get the same results.

In both cases, this happens even when I run it in a New Query window, when I AM LOGGED IN AS 'sa'.  In this case the client application simply times out.

I've also tried creating a separate function WITH EXECUTE AS OWNER to return just the IP Address from sys.dm_exec_connections, and got the same Msg 15517 (above).

I would RATHER NOT try to create a login to the server with "VIEW SERVER STATE" permissions, as that has to be granted on a login, and can't be granted on a user created "WITHOUT LOGIN".

Does anyone know how to get the EXECUTE AS functionality working/what other settings I might need to look at to permit this?

Thank you for any help you can offer.
0
Comment
Question by:baronga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36526694
Please post your full TRIGGER.  I suspect there is something that you are not telling us.
0
 

Author Comment

by:baronga
ID: 36528873
Here's the code for the full trigger.  You will see a section that queries registry tables.  

     IF LTrim(RTrim(@IPAddress)) = '<local machine>'
     BEGIN
          ...
     END

This part works fine in the new Query Window (logged in as 'sa'), and is successfully bypassed in the client application.  Additionally, I commented that portion out, while trying the various "EXECUTE AS" impersonation methods, so that is not what's throwing it off.

When I tried EXECUTE AS USER = 'dbo' / REVERT, I removed the WITH EXECUTE AS SELF from the beginning, and placed the EXECUTE AS USER = 'dbo' directly above the select statement (line 11, after variable declarations), and put REVERT directly after the same select statement (line 19).



ALTER TRIGGER [dbo].[trgAddrUpd]
	on [dbo].[tblAddress]
   WITH EXECUTE AS SELF
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @Port int = NULL, @Login VarChar(128) = NULL, @IPAddress VarChar(48) = NULL, @Host VarChar(128) = NULL, @Program varchar(128) = NULL
	DECLARE @SAMUser VarChar(20) = NULL, @LastUser VarChar(20) = NULL
	SELECT @IPAddress = c.client_net_address
		,@Port = c.local_tcp_port
		,@Host = s.host_name
		,@Program = s.program_name
		,@Login	= s.login_name
	from sys.dm_exec_sessions s 
	JOIN sys.dm_exec_connections c ON
		s.session_id = c.session_id
	WHERE s.session_id = @@spid

	IF LTrim(RTrim(@IPAddress)) = '<local machine>'
	BEGIN
		DECLARE @value VARCHAR(20)
		DECLARE @key VARCHAR(100)

		--DECLARE @value VARCHAR(20)
		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnSAMUser',
		   @SAMUser = @SAMUser OUTPUT

		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnUser',
		   @LastUser = @LastUser OUTPUT
	END

	-- Insert statements for trigger here
	INSERT INTO tblAddressHist
	(	AddressID
		,Address1
		,Address2
		,City
		,Zip
		,IPAddress
		,TransDate
		,TransType
		,Host
		,SQLLogin
		,Program
		,SAMUser
		,LastUser
		,PortUsed
	)

	SELECT
		AddressID
		,Address1
		,Address2
		,City
		,Zip
		,@IPAddress
		,GetDate()
		,'U'
		,@Host
		,@Login
		,@Program
		,@SAMUser
		,@LastUser
		,@Port
	FROM INSERTED

END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36530228
So it looks like the reason you want to impersonate another user is because you want to use the system VIEW sys.dm_exec_session and sys.dm_exec_connections as well as the undocument function xp_regread.

Have you tried:
ALTER TRIGGER [dbo].[trgAddrUpd]
      on [dbo].[tblAddress]
   WITH EXECUTE AS OWNER
   AFTER UPDATE
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:baronga
ID: 36531622
Yes, I had tried EXECUTE AS OWNER, and like EXECUTE AS SELF, it generates the same error:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36533440
>>Yes, I had tried EXECUTE AS OWNER, and like EXECUTE AS SELF, it generates the same error:<<
I am afraid I have no idea.  We use that all the time.

0
 

Accepted Solution

by:
baronga earned 0 total points
ID: 36545958
I found a workaround--

It looks like "EXECUTE AS " works on other procedures, but it just doesn't like those sys.dm_exec tables.  After more research, I found built-in functions to get the same information, and used them, instead (and they don't require EXECUTE AS impersonation, at all).  Code is attached:

ALTER TRIGGER [dbo].[trgAddrUpd]
	on [dbo].[tblAddress]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON

	DECLARE @SAMUser VarChar(20) = NULL, @LastUser VarChar(20) = NULL
	DECLARE @IPAddress VarChar(48) = convert(VarChar(48),CONNECTIONPROPERTY('client_net_address'))

	IF LTrim(RTrim(@IPAddress)) IS NULL		-- = '<local machine>'
	BEGIN
		DECLARE @value VARCHAR(20)
		DECLARE @key VARCHAR(100)

		--DECLARE @value VARCHAR(20)
		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnSAMUser',
		   @SAMUser = @SAMUser OUTPUT

		EXEC master..xp_regread
		   @rootkey = 'HKEY_LOCAL_MACHINE',
		   @key = 'Software\Microsoft\Windows\CurrentVersion\Authentication\LogonUI',
		   @value_name = 'LastLoggedOnUser',
		   @LastUser = @LastUser OUTPUT
	END

	-- Insert statements for trigger here
	INSERT INTO tblAddressHist
	(	AddressID
		,Address1
		,Address2
		,City
		,Zip
		,IPAddress
		,TransDate
		,TransType
		,Host
		,SQLLogin
		,Program
		,SAMUser
		,LastUser
		,PortUsed
	)

	SELECT
		AddressID
		,Address1
		,Address2
		,City
		,Zip
		,@IPAddress
		,GetDate()
		,'U'
		,HOST_NAME()
		,ORIGINAL_LOGIN()
		,APP_NAME()
		,@SAMUser
		,@LastUser
		,convert(int, CONNECTIONPROPERTY('local_tcp_port'))
	FROM INSERTED

END

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41657559
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41657561
Let's try that again:
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question