Achieve CLR impersonation in a SQL Agent job step

All Replies

• Tuesday, March 08, 2011 2:13 AM

  Moderator

   

   
  

  0

  Hi,

  If you want to use SQL Agent job to impersonate the job owner's security context, please make sure the following two options are true:

  1. The job owner is not sysadmin, which means your users who will create the job are not sysadmin;
  2. The job step must be the type of T-SQL.

  I would recommend the following blog post from Tibor, which describes SQL Server Agent job user context in detail, see: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx.

  Hope this helps.

  ---

  Best Regards,
  Chunsong Feng
  
  Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
  • Reply
  • Quote

   
• Tuesday, March 08, 2011 2:32 AM

   

   
  

  0

  Thanks for your reply.  Yes, both 1 and 2 are correct: the job owner is not sysadmin, and the job step is of type T-SQL (it runs a T-SQL command like "EXEC myCLRproc").

  Tibor's post is helpful: it explains that in this scenario, "Agent logs in to SQL Server using its own credential (windows authentication from service account), then uses EXECUTE AS LOGIN = '<job_owner_login>' to "impersonate" the login who owns the job. I.e., the TSQL commands will be executed in the security context of the job owner's login."

  Unfortunately, CLR impersonation does not pick up the login specified in an EXECUTE AS statement.  In testing, I have found that in the above scenario the SQLContext.WindowsIdentity needed for CLR impersonation is NULL.  This posting supports this (though I haven't yet dug up the msdn documentation he references): http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2007-01/msg00077.html

   

  So I'm left wondering if there is any other way to get this done?  I had sort of hoped the CLR could somehow inspect the owner of the job that called it, but I see from Tibor's post this is extremely unlikely.  Any other ideas?

  • Reply
  • Quote

   
• Wednesday, March 09, 2011 2:15 AM

  Moderator

   

   
  

  0

  Hi,

  Thanks for your follow up.

  Actually, if you job step meets the above two prerequisites, you have no need to use CLR Impersonation because the job step will be running in the security context of the job owner.

  Hope this helps.

  ---

  Best Regards,
  Chunsong Feng
  
  Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
  • Reply
  • Quote

   
• Friday, March 11, 2011 5:15 PM

   

   
  

  0
  To test this, my team created an EXTERNAL ACCESS CLR proc that outputs the results for the following user related values (testing when impersonation is switched on and then when it's not), which we ran for a few different test cases, the detailed results of which are below.

  
  

  "SELECT SYSTEM_USER";

  "SELECT ORIGINAL_LOGIN()";

  "SELECT USER_NAME()";

  "SELECT SUSER_SNAME()";

  SqlContext.WindowsIdentity.Name

  WindowsIdentity.GetCurrent().Name

  
  

  The contexts coming from SQL queries behave as described in Tibor's post, but the accounts under which the CLR can do file access (using WindowsIdentity, or through impersonation using SQLContext.WindowsIdentity) are always one of the service accounts (SQLServerAgent or SQLServerDatabaseEngine).  In particular, for the case you describe of a job whose owner is not sysadmin and whose job step is a T-SQL call to my CLR proc, the CLR WindowsIdentity is the SQL Server database engine service account and the SqlContext.WindowsIdentity is the SQL Agent service account.

  
  

  So the EXECUTE AS does not seem to pass through to the CLR.  Have you run tests that show otherwise?  Should I post our CLR code to see if anything seems to be wrong with it (though it's very simple)?

  
  

  DETAILED TESTING RESULTS

  ============================================================

  
  

  1) Baseline: CLR proc called via T-SQL from SSMS Query Window, logged in as COMPUTER\Administrator

  
  

  **SAME RESULTS WITH OR WITHOUT IMPERSONATION BEING USED IN THE CLR CODE** 

  SYSTEM_USER: COMPUTER\Administrator 

  ORIGINAL_LOGIN():COMPUTER\Administrator 

  USER_NAME():dbo 

  SUSER_SNAME(): COMPUTER\Administrator 

  SQLContext.WindowsIdentity: COMPUTER\Administrator 

  Actual WindowsIdentity: COMPUTER\SQLServerDatabaseEngine

  
  

  2) CLR proc called via T-SQL from Agent T-SQL Job Step (in a job owned by a Windows user in the sysadmin role)

  
  

  **SAME RESULTS WITH OR WITHOUT IMPERSONATION BEING USED IN THE CLR CODE** 

  SYSTEM_USER: COMPUTER\SQLServerAgent 

  ORIGINAL_LOGIN():COMPUTER\SQLServerAgent 

  USER_NAME():dbo 

  SUSER_SNAME(): COMPUTER\SQLServerAgent 

  SQLContext.WindowsIdentity: COMPUTER\SQLServerAgent 

  Actual WindowsIdentity: COMPUTER\SQLServerDatabaseEngine 

  
  

  3) CLR proc called via T-SQL from Agent T-SQL Job Step (in a job owned by a Windows user who is NOT in sysadmin role) - same results as 2

  
  

  **SAME RESULTS WITH OR WITHOUT IMPERSONATION BEING USED IN THE CLR CODE** 

  SYSTEM_USER: COMPUTER\SQLServerAgent 

  ORIGINAL_LOGIN():COMPUTER\SQLServerAgent 

  USER_NAME():dbo 

  SUSER_SNAME(): COMPUTER\SQLServerAgent 

  SQLContext.WindowsIdentity: COMPUTER\SQLServerAgent 

  Actual WindowsIdentity: COMPUTER\SQLServerDatabaseEngine 
  • Reply
  • Quote

   
• Thursday, April 21, 2011 8:56 PM

   

   
  

  0
  I think I've found the documentation to support what I've observed above.  At http://msdn.microsoft.com/en-us/library/ms345101.aspx, it says "If... The caller is not the original caller, Access is denied and a security exception is raised."  Since Agent Jobs impersonate their job owner, and then our CLR code is trying to impersonate its caller, I think it might fall under this condition.  (Maybe I'm wrong, but that's my best interpretation.)
  • Reply
  • Quote

   
• Thursday, July 19, 2012 3:57 PM

   

   
  

  0

  Ethan..  I am unable to get impersonation to work in my CLR.  How were you able to get it to work when calling from your SSMS query window?  Please see my example below (in VB):

  Dim clientId As WindowsIdentity
  clientId = SqlContext.WindowsIdentity

  Using impersonatedUser As WindowsImpersonationContext = clientId.Impersonate()

     Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
     filePerm.Assert()
     Dim fi As New FileInfo(path)
     If fi.Exists Then...

  My "If" fails here as I don't have permission to the 'path', although the client (me) actually does have the correct permissions.

  Thank you!

  • Reply
  • Quote

   
• Thursday, July 19, 2012 8:42 PM

   

   
  

  0

  i think it is better that you start a new thread rather than trying to revive an old one. There may be difference to the original case, so it's a good idea if you can describe your situation from the beginning.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
  • Reply
  • Quote