We're trying to rein in the number of sysadmins on our servers, and one of the issues we're running into pertains to SQL Agent security — especially with regard to SSIS jobs and our ETL team. I understand that non-sysadmins cannot execute SSIS job steps by default and that proxies can be created in order to allow this, but I'm still struggling with the implementation.
This post talks about using proxies to allow non-sysadmins to manage and maintain SQL jobs, but creating credentials for each of my ETL developers and maintaining those via screen-share every time their password changes (according to our AD policy) seems clumsy at best. Even with just a handful of developers this quickly becomes untenable. I can't afford to have jobs fail because someone's password expired, and I'd rather not rely on people remembering that the credential needs to be updated every time their password changes.
I'm curious how other shops deal with this. We can't be the only company with a team of ETL developers that need to be able to create and manage SQL Agent jobs to run the SSIS packages they develop. Do you use a service account for the credential/proxy and grant everyone on the ETL team access to the proxy? And if so, does this same service account "own" the SQL Agent job or do the individual developers?
It would be nice if anyone on the team could make changes to jobs as needed, but it seems the only way to accomplish this is to use a "shared" service account or make everyone sysadmins — which is exactly the scenario we're trying to avoid. What are the best practices? Can anyone speak to their own implementation? I'm trying to understand how I can use credentials and proxies to help, but I haven't been able to find much concrete guidance with specific implementation instructions or best practices.
Apologies if my question was all over the place. Here's the scenario: An SSIS developer deploys a package and creates a SQL Agent job to run that package. By default, he's the owner of the job. Because he's not a sysadmin, however, the job throws this error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed. If I make the sa account the owner of the job, it executes but the SSIS developer can no longer make changes to it. Even if I grant ssis_admin in SSISDB and db_ssisadmin in msdb the job will fail.
I want to have my cake and eat it too. Obviously the job needs to run, and I would prefer not to involve a sysadmin every time a job needs edited. The closest I've come to being able to do this is to create a credential for the developer, associate a proxy with that credential, then give the proxy access to the SSIS Package Execution subsystem. But then I'm stuck maintaining passwords for each of the credentials every time they change. And if I should ever miss updating a credential password when it expires or is changed, the job will begin failing again.
- The ETL dev owns the job;
- The ETL dev belongs to SQLAgentOperatorRole in msdb (which allows him to make changes to his own jobs but not those owned by others);
- The ETL dev belongs to db_ssisadmin, db_ssisltduser, and db_ssisoperator in msdb;
- The ETL dev belongs to ssis_admin in SSISDB Even then, and even if I'm the one executing the job (as a sysadmin), it fails with Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
The job will only succeed if it's owned by a sysadmin or if the SSIS step executes using a proxy. Does everyone just make their ETL devs sysadmins and call it a day? I'm leaning toward creating a service account to associate the credential with whose password doesn't expire then creating an SSIS proxy for the credential and granting all of the SSIS devs access to the proxy. At least then they're able to edit the jobs they own, the jobs will succeed so long as the SSIS step uses the proxy, and I'm not having to update credential passwords all the time.