Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

EXECUTE AS Permissions To Query system tables requireing View Server State

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
baronga
Asked:
baronga
  • 5
  • 3
1 Solution
 
Anthony PerkinsCommented:
Please post your full TRIGGER.  I suspect there is something that you are not telling us.
0
 
barongaAuthor Commented:
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
 
Anthony PerkinsCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
barongaAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
barongaAuthor Commented:
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
 
Anthony PerkinsCommented:
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0
 
Anthony PerkinsCommented:
Let's try that again:
I suggest you save this thread with the author's comment https:#a36545958 as the solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now