Sql-server – Security best practices for SSIS package execution via SQL Agent job

Securitysql serversql-server-agentssis

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.

  1. The ETL dev owns the job;
  2. The ETL dev belongs to SQLAgentOperatorRole in msdb (which allows him to make changes to his own jobs but not those owned by others);
  3. The ETL dev belongs to db_ssisadmin, db_ssisltduser, and db_ssisoperator in msdb;
  4. 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.

Best Answer

You don't need to use a proxy account nor do you need to grant sysadmin to your ETL team for them to be able to run SSIS packages and SQL Agent jobs.

You should first look into the SQL Server Agent Fixed Database Roles, such as the SQLAgentOperatorRole. (I'm guessing you have already, but in case you haven't yet.)

If that alone doesn't solve your issue then you should also look into Integration Services Roles (SSIS Service) - Roles in the msdb database. If you need the ETL team to be able to execute SSIS packages that they don't own, then you'll want to particularly look into the db_ssisoperator role or db_ssisadmin role which are certainly more limited than sysadmin. (It sounds like db_ssisoperator is probably what you want.)

One caveat with the db_ssisadmin role is it has the ability to run an SSIS package that can execute the T-SQL to grant the sysadmin role to a user (if the package runs under an existing sysadmin account), should someone on your team feel maleficent enough to create such a package and run it, as mentioned in this warning:

**** Warning ****Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent.

If you only need your ETL team to be able to run only their own packages, then the above warning is moot, because you can use the db_ssisltduser role instead.

Related Question