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

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 w

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.

StackExchange Database Administrators Q#120907, answer score: 17