Solved

EXECUTE AS Permissions To Query system tables requireing View Server State

Posted on 2011-09-12
10
99 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now