March 25, 2010 at 10:57 am
Can someone help me understand what's happening? I have a job with one step that runs an SSIS package. When the job attempts to run at the scheduled time, it returns this error:
Job Error: The job failed. The Job was invoked by Schedule 116 (Run Lead Update). The last step to run was step 1 (Extract Prior Days Usage).
Step Error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
The error seems to indicate that the entity trying to invoke the job isn't a sys admin. But who does it think is trying to invoke the job? It was invoked by the job's schedule...is that not the SQL agent account?
March 25, 2010 at 9:34 pm
If the user is not a SQL Server sysadmin, you must define a SQL Server proxy account if the user needs to run a SSIS package from SQL Agent. This is a requirement introduced in SQL 2005 to improve security - previously, the job would have run in the context of the SQL Agent service account which may not have been appropriate.
Proxy accounts are configured from within Management Studio - expand the SQL Agent node and you should see "Proxies". Right click on it and select new proxy. If you have not set up and "Credentials" yet, you will need to do that first.
Have a look for "Proxy Accounts" in Books Online - there is plenty of info on how to do this.
March 26, 2010 at 7:58 am
Thanks for the response HappyCat59, I'm familiar with proxy accounts but I think you are misunderstanding my problem. The problem is not the security within the execution context of the PACKAGE, but the security within the execution context of the JOB that is trying to execute the package.
To clarify with an example, I have a package that "Bob" (senior DBA) built to run under "Bob's" security context. "Bob" gives the package to "Mike" (a new DBA) and asks him to schedule it. "Mike" creates a job and configures the package itself to run under "Bob's" credentials via a proxy account that "Bob" setup. "Mike" schedules the job to run at 1am daily. To "Mike's surprise, the job fails with the error I mentioned earlier.
What I THINK is happening is that even though the package is configured to run under "Bob's" credentials (proxy), the job that tries to execute the package is running under "Mike's" credentials. Unfortunately, "Mike" doesn't have permission to run DTS Execute on the server. In essence, "Bob" has given "Mike" the car (package) and the keys to the car (proxy), but not the key to the garage (DTS Execute).
So how do you control the security context of the jobs themselves? I always thought they ran under the SQL Agent's context....but I guess not.
March 26, 2010 at 8:02 am
I think you should have separate account to run jobs.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 26, 2010 at 8:24 am
Thanks Bhuvnesh. But that's soft of like answering, "Yes" to a three part question. 😉 I'm trying to figure out HOW to control the security context of the job.
:w00t:After talking myself through that example for HappyCat59, I found the answer...the context of the Job is based on the Owner of the Job. So using my previous example, "Mike" doesn't have permission to execute jobs (hence the error). "Mike" needed to specify an owner that has permission to execute jobs. "Mike" creates the job but this time set's "Sue" (another DBA) as the owner and viola...the Job runs under "Sue's" security context (job owner)...the Package runs under "Bob's" (package's proxy). Interesting.
March 29, 2010 at 12:19 am
Uncle Moki (3/26/2010)
Thanks Bhuvnesh. But that's soft of like answering, "Yes" to a three part question. 😉 I'm trying to figure out HOW to control the security context of the job.
In our company we have separate account like INDB\Sqlman just to run all jobs. so i can also go with this approach.SO whomsoever is creating any job, he can select this account to run that.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 29, 2010 at 1:26 am
.
March 29, 2010 at 1:27 am
General answer-
SQL jobs run under the context of logon account with which SQL Server angent is running.
March 29, 2010 at 10:35 am
vidya_pande (3/29/2010)
General answer-SQL jobs run under the context of logon account with which SQL Server angent is running.
No, SQL jobs run under the context of whoever owns that job, don't they?
March 29, 2010 at 11:49 am
vidya_pande (3/29/2010)
General answer-SQL jobs run under the context of logon account with which SQL Server angent is running.
I don't think so...at least that's not what I'm seeing. Apparently we need to keep the Job's context distinct from the Step's context. The job runs under the context of the, "Owner". The step runs under the context of the, "Run As"...which will be the SQL Agent if blank (and most people blindly leave it blank...myself included).
liteswitch (3/29/2010)
No, SQL jobs run under the context of whoever owns that job, don't they?
I think so. If I were to summarize what I'm seeing (granted, this is just a guess based on the evidence):
- Job's, "Owner": Needs to be able to run the the contents of the job. Typically defaults to the person who created the job.
Windows or SQL Login
- Step's, "Run As": The security context of whatever is being run. Typically defaults to the SQL Agent account (when blank).
Proxy
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
So in my case, the Job's, "Owner" wasn't a Sys Admin, and apparently only Sys Admins can run jobs that use the, "DTS Execute" command (DTS, SSIS) without having to specify a Proxy....so the job couldn't execute.
The Step's, "Run As" was blank so the IF the job WERE executed, it would have executed as the SQL Agent and all would have been fine.
To correct this, I either needed to create and use a Proxy for the SSIS step...or...specify a different "Job Owner" (one that was allowed to run DTS Execute under the context of the SQL Agent...i.e. the owner had to be a Sys Admin) and viola. Again, since the Step's, "Run As" was blank, it executed under the context of the SQL Agent (not the Job Owner) and all was fine.
At least this is what seems to be the case. I haven't tried reading BOL on the topic to confirm.
March 29, 2010 at 11:57 am
Uncle Moki (3/29/2010)
vidya_pande (3/29/2010)
General answer-SQL jobs run under the context of logon account with which SQL Server angent is running.
I don't think so...at least that's not what I'm seeing. Apparently we need to keep the Job's context distinct from the Step's context. The job runs under the context of the, "Owner". The step runs under the context of the, "Run As"...which will be the SQL Agent if blank (and most people blindly leave it blank...myself included).
liteswitch (3/29/2010)
No, SQL jobs run under the context of whoever owns that job, don't they?I think so. If I were to summarize what I'm seeing (granted, this is just a guess based on the evidence):
- Job's, "Owner": Needs to be able to run the the contents of the job. Typically defaults to the person who created the job.
Windows or SQL Login
- Step's, "Run As": The security context of whatever is being run. Typically defaults to the SQL Agent account (when blank).
Proxy
Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
So in my case, the Job's, "Owner" wasn't a Sys Admin, and apparently only Sys Admins can run jobs that use the, "DTS Execute" command (DTS, SSIS) without having to specify a Proxy....so the job couldn't execute.
The Step's, "Run As" was blank so the IF the job WERE executed, it would have executed as the SQL Agent and all would have been fine.
To correct this, I either needed to create and use a Proxy for the SSIS step...or...specify a different "Job Owner" (one that was allowed to run DTS Execute under the context of the SQL Agent...i.e. the owner had to be a Sys Admin) and viola. Again, since the Step's, "Run As" was blank, it executed under the context of the SQL Agent (not the Job Owner) and all was fine.
At least this is what seems to be the case. I haven't tried reading BOL on the topic to confirm.
Yes indeed, this is why when I create jobs I usually set the owner to be the SQL Agent account, as otherwise if you've a job assigned to a windows login and that login is deleted, all those jobs will then start failing.
March 29, 2010 at 12:50 pm
liteswitch (3/29/2010)
Yes indeed, this is why when I create jobs I usually set the owner to be the SQL Agent account, as otherwise if you've a job assigned to a windows login and that login is deleted, all those jobs will then start failing.
Good point. BTW: I think the same thing is true for Proxies...the underlying login/password is hard-coded and likely would need to be updated everywhere the proxy was defined.
March 30, 2010 at 3:05 am
I think Moki has covered most angles, but a couple of things need more clarification.
If the job owner has sysadmin authority, the job always runs under the SQL Agent security context. (A side effect is even in a system that only allows Windows authentication you can set sysadmin jobs to be owned by sa, as these are run under the SQL Agent context.)
If the job step Run As is blank, the step runs under the job owner security context. This means if the owner has sysadmin authority then it will run under the SQL Agent context.
If the job step Run As is not blank, the step will run using the Run As context.
If the job owner is not a member of sysadmin, the job runs under the job owner security context. This will also mean that any job steps that need proxies must have proxy accounts assigned.
IMHO the cleanest security model for non-sysadmin job owners is to configure the job owner account as a proxy account for all the subsystems it needs. This means the entire job (with the exception of any xp_cmdshell use) runs under a single security context, which simplifies assigning and troubleshooting Windows and NTFS rights.
If you set up subsystem proxies that are not the same as job owner accounts then the security model gets quite complex. For example, if job owner FRED wants to run a DTS job that accesses D:\Finance\File1.xls and job owner JOE wants to run a DTS job that accesses E:\Sales\File2.xls and they both use the same DTS proxy, then the proxy account needs to have access to both files. This can lead to a security exposure, as a hacker could exploit FRED or JOE to access each other's files.
Finally, xp_cmdshell use has a separate security context. If xp_cmdshell is run by a user with sysadmin rights, it always runs with the context of the SQL Server service account. If xp_cmdshell is run by a non-sysadmin user then xp_cmdshell always runs using the xp_cmdshell proxy authority.
This means that even jobs run by a sysadmin user will have two security contexts associated with them if they use xp_cmdshell - the SQL Server context for xp_cmdshell and the SQL Agent context for everything else.
For a non-sysadmin user running xp_cmdshell, the fact that a single proxy is used for all access means there is a security issue as that proxy needs to have access to all data that all job owners reference via xp_cmdshell.
BTW, job output files are always written using the SQL Agent context, which means that non-sysadmin users cannot specify a job output file.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 30, 2010 at 10:02 am
If the job owner has sysadmin authority, the job always runs under the SQL Agent security context.
...
If the job step Run As is blank, the step runs under the job owner security context. This means if the owner has sysadmin authority then it will run under the SQL Agent context.
If the job step Run As is not blank, the step will run using the Run As context.
If the job owner is not a member of sysadmin, the job runs under the job owner security context. This will also mean that any job steps that need proxies must have proxy accounts assigned.
Thanks for the info. I think that clarifies. The key seems to be that if the Job Owner is not a member of sysadmin, then the job step's Run As cannot be blank...proxies must be specified.
Question: Is this only true for steps that use SSIS packages? Stated in reverse - can non sysadmins own jobs, and leave the job step's Run As blank, if the job steps only execute TSQL against db objects that they have permission to access?
March 30, 2010 at 10:11 am
Uncle Moki (3/30/2010)
If the job owner has sysadmin authority, the job always runs under the SQL Agent security context.
...
If the job step Run As is blank, the step runs under the job owner security context. This means if the owner has sysadmin authority then it will run under the SQL Agent context.
If the job step Run As is not blank, the step will run using the Run As context.
If the job owner is not a member of sysadmin, the job runs under the job owner security context. This will also mean that any job steps that need proxies must have proxy accounts assigned.
Thanks for the info. I think that clarifies. The key seems to be that if the Job Owner is not a member of sysadmin, then the job step's Run As cannot be blank...proxies must be specified.
Question: Is this only true for steps that use SSIS packages? Stated in reverse - can non sysadmins own jobs, and leave the job step's Run As blank, if the job steps only execute TSQL against db objects that they have permission to access?
Anyone can own a job, as long as they've got the permissions to do the work that's in the job.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply