THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

SQL Server Agent jobs and user contexts

In what user context does a job run? I recently found myself in a forum discussion and gave my stock reply, later realizing that I haven't actually tested this for a long time (I used to demo this in class during 6.5 courses - when we actually had time for slight diversions). Lets start with my assumptions:

  1. Job owned by sysadmin, TSQL jobsteps:
    Agent log in to SQL Server using its own credential (windows authentication from service account) and execute the TSQL commands - no impersonation performed.
  2. Job owned by sysadmin, other jobsteps:
    Agent starts a process using its service account - no impersonation performed.
  3. Job owned by non-sysadmin, TSQL jobstep:
    Agent log 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.
  4. Job owned by non-sysadmin, other jobsteps:
    Agent starts a process using the service account as specified by the Agent proxy selected for this jobstep. (See Books Online "sp_add_proxy" and "CREATE CREDENTIAL" and this for more information about Agent Proxies and credentials in SQL Server.)

So, how to prove above? Lets use a combination of jobstep output file and a Profiler trace. The ouput files will be used to catch the login/user names, and the Profiler trace to verify what TSQL commands will be submitted by Agent (for TSQL jobstep). For the TSQL jobsteps, we execute:

SET NOCOUNT ON
SELECT 
 
CAST(ORIGINAL_LOGIN() AS VARCHAR(20)) AS Original_login
,
CAST(SUSER_SNAME() AS VARCHAR(20)) AS Effective_login
,
CAST(USER_NAME() AS VARCHAR(20)) AS Db_user
 

And to test the other jobsteps, where we will be using a CmdExec jobstep as example (we are interested in what Windows account the process will be started as), and use whoami.exe which returns the windows user name. Each jobstep is configured to output the result to a file.

  • I change my real machine name to "Machine" in this text.
  • I'm not in a domain.
  • I'm logged in interactively (to Windows) as Windows account "Tibor".
  • I'm logged in to SQL Server using Windows authentication.
  • I'm sysadmin.
  • My service account for both Agent and SQL Server services is "SQLService".

Here's the result from having the job owned by me (being sysadmin):

Original_login       Effective_login       Db_user            
-------------------- -------------------- --------------------
MACHINE\SqlService   MACHINE\SqlService   dbo

machine\sqlservice

As you see, no attempted impersonation performed. Steps 1 - 2 in above list confirmed.

We now going to test this for somebody who isn't sysadmin:

  • Create SQL Server login named "Kalle"
  • Create user in msdb for above
  • Assign Kalle permission to create jobs (SQLAgentUser role in msdb)
  • Create account "MySqlProxy" in Windows.
  • Creade credential "c_MySqlProxy" in SQL Server for above.
  • Create proxy "p_MySqlProxy" for above, and allow login "Kalle" to use this for CmdExec jobsteps.

Now we modify the job and set the owner to Kalle, and also specify for the CmdExec jobstep to use the proxy p_MySqlProxy. Also, since non-sysadmins can't use output files (I didn't know that), we instead configure each step's output to go to table. Execute job. No, finally we can check each job step output for result:

Original_login       Effective_login       Db_user            
-------------------- -------------------- --------------------
MACHINE\SqlService   Kalle                guest

machine\mysqlproxy

The first section above confirms that Agent uses it's own login, but then changes login context to Kalle (verified by Profiler trace capturing the "EXECUTE AS LOGIN = N'Kalle' WITH NO REVERT" command). The job step was configured to run in the master database; since I didn't add Kalle as a user to master, you see the user name guest.

The second section verifies that The CmdExec step started a process using the mysqlproxy windows account, and specified using our SQL Server Agent Proxy and SQL Server credential object.

(I will not reply to support questions here. For support, I recommend you visit a forum, for instance this. Other discussions, comments and corrections are of course very welcome!)

Finally, I scripted out the job (as owned by me - being sysadmin), if you want to re-create it and play with it. Use at own risk, do apropriate modifications etc.

USE [msdb]
GO

/****** Object:  Job [CredTest]    Script Date: 09/19/2009 12:12:47 ******/
BEGIN TRANSACTION
DECLARE 
@ReturnCode INT
SELECT 
@ReturnCode 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/19/2009 12:12:47 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC 
@ReturnCode msdb.dbo.sp_add_category @class=N'JOB'@type=N'LOCAL'@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback

END

DECLARE 
@jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CredTest'
       
@enabled=1
       
@notify_level_eventlog=0
       
@notify_level_email=0
       
@notify_level_netsend=0
       
@notify_level_page=0
       
@delete_level=0
       
@description=N'No description available.'
       
@category_name=N'[Uncategorized (Local)]'
       
@owner_login_name=N'MACHINE\Tibor'@job_id @jobId OUTPUT
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback
/****** Object:  Step [A_TSQL]    Script Date: 09/19/2009 12:12:48 ******/
EXEC @ReturnCode msdb.dbo.sp_add_jobstep @job_id=@jobId@step_name=N'A_TSQL'
       
@step_id=1
       
@cmdexec_success_code=0
       
@on_success_action=3
       
@on_success_step_id=0
       
@on_fail_action=2
       
@on_fail_step_id=0
       
@retry_attempts=0
       
@retry_interval=0
       
@os_run_priority=0@subsystem=N'TSQL'
       
@command=N'SET NOCOUNT ON 
SELECT  
 CAST(ORIGINAL_LOGIN() AS VARCHAR(20)) AS Original_login
,CAST(SUSER_SNAME() AS VARCHAR(20)) AS Effective_user
,CAST(USER_NAME() AS VARCHAR(20)) AS Db_user'

       
@database_name=N'master'
       
@flags=8
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback
/****** Object:  Step [B_OS]    Script Date: 09/19/2009 12:12:48 ******/
EXEC @ReturnCode msdb.dbo.sp_add_jobstep @job_id=@jobId@step_name=N'B_OS'
       
@step_id=2
       
@cmdexec_success_code=0
       
@on_success_action=1
       
@on_success_step_id=0
       
@on_fail_action=2
       
@on_fail_step_id=0
       
@retry_attempts=0
       
@retry_interval=0
       
@os_run_priority=0@subsystem=N'CmdExec'
       
@command=N'whoami.exe'
       
@flags=16
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback
EXEC @ReturnCode msdb.dbo.sp_update_job @job_id @jobId@start_step_id 1
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback
EXEC @ReturnCode msdb.dbo.sp_add_jobserver @job_id @jobId@server_name N'(local)'
IF (@@ERROR <> OR @ReturnCode <> 0GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO 
EndSave
QuitWithRollback:
    
IF (@@TRANCOUNT 0ROLLBACK TRANSACTION
EndSave:

GO
Published Saturday, September 19, 2009 11:57 AM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andy said:

A helpful summary. For the testing of the CmdExec / XP_Cmdshell, I find that running the following is helpful to determine the context that you are running in.

Net Config Workstation

We normally have these issues and then throw some linked servers into the equation which gets us really confused.

February 10, 2010 12:51 PM
 

TiborKaraszi said:

Good tip, Andy. Thanks.

February 11, 2010 2:38 AM
 

Anna said:

Is this article for SQl server 2005, is it still true for 2008 and R2?

June 7, 2012 12:30 AM
 

TiborKaraszi said:

Anna,

This is still true for more recent versions (including 2012).

June 8, 2012 3:14 AM
 

Michael said:

Is there any way to force sql agent tsql jobs to impersonate the owner if it IS a sysadmin role?  I want a sysadmin role to have a mapped login to a linked server and be able to use that in sql agent tsql job steps just as I would if I was running the tsql from a query window.  For security reasons, I want to continue having my linked server login security configured with "no connection will be made" if the user doesn't exist in the mapping list.

In other forums, I have seen phrases like "by default, sql agent jobs will run as the service account if the owner is a sysadmin fixed role"...which leads me to believe there is some advanced option to change that behavior and force impersonation...

January 8, 2014 10:30 AM
 

TiborKaraszi said:

AFAIK, no. I can think about two workarounds:

A: Use CmdExec step instead and call SQLCMD.EXE to execute your commands. You can now use an Agent Proxy for your Windows login, but as that login changes password, you have to modify the underlying Credential. Or if you want to use a SQL Server login, then you would expose login name and pwd in the jobstep (as command switches for SQLCMD.EXE).

B: Use TSQL jobstep, but add an EXECUTE AS LOGIN = in the beginning of your TSQL code. I don't know how well an impersonated login plays with linked servers, though... Since you are sysadmin (your prereq's), you are allowed to EXECUTE AS LOGIN for anyone.

January 8, 2014 11:01 AM
 

Michael said:

I'll try option B and see if that works...  will update shortly.

Thanks for the quick response!

January 8, 2014 11:07 AM
 

Michael said:

Holy cow, you are awesome!  "Execute as" worked perfect!  My job step was just running an exec storedprocedure, but now I've prepended the execute as just before it execs and it fires off perfectly!

THANK YOU!

January 8, 2014 11:13 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement