Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

Question

I have two SSIS packages that run scheduled overnight (via SQL Server Agent) as part of a larger SSIS deployment, without any issues. Everything is using Windows authentication, and the scheduled job is owned by a sysadmin (well, me) and run as the SQL Server Agent Service Account.

So, the data essentially goes source system ~> transit db ~> staging ~> NDS overnight.

The two SSIS packages I care about, handle the transit db ~> staging and staging ~> NDS parts, respectively, for a specific set of data.

A domain user (non-sysadmin) does something in the source system and that pushes the interesting data into the transit db, so I need a way to fetch this updated data during work hours to update the NDS: it was decided that the simplest way for this person to trigger that ETL, was by clicking a button in a macro-enabled Excel workbook, that connects to SQL Server via ODBC (using Windows Authentication) and executes a stored procedure.

The stored procedure looks like this:

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

The "sister" stored procedure in [msdb] looks like this:

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

This [SqlAgentProxy] user is a Windows user I created in [msdb] off the domain user's login, to which I granted execute permission to this UpdateMaterialInventory procedure. This avoids having to grant the domain user execute permission to msdb.dbo.sp_start_job, which would be excessive.

The SQL Agent job NDS-ManualMaterialInventory is owned by the domain user and has 2 steps, each of type [SQL Server Integration Services Package], set up to Run as SSISProxy.

SSISProxy is an SQL Server Agent proxy that's mapped to the [SQL Server Integration Services Package] subsystem, using credential name SSISProxyCredentials. The domain user's login was added to the Proxy account principals.

The SSISProxyCredentials were created with the Identity of the same domain user that's running the entire SSIS ETL overnight, and its password was quadruple-checked.

Now, if I run this:

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

I get this output:

Job 'NDS-ManualMaterialInventory' started successfully.

However the job history is telling a much less encouraging story:

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

And step 1 details:

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider 
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

The job fails and nothing gets logged anywhere.

If I change the job owner to be myself, and change the steps' run as to be the SQL Server Agent Service Account, the job runs, succeeds and logs 1,067 rows to [Metadata].[dbo].[sysssislog].

Looks like there's something not right about how the proxy/credentials are set up. Which part am I doing wrong?

Answer 1

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

The only thing that actually matters is:

Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.   
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

Your Proxy user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions

Once you have the package actually loading you might run into other security context issues but you should get better logging from integration services itself.