SQL Server Agent Job doesn't execute SSIS package Permission Error

Question

I am trying to run a SSIS Package (Uploading a file) to my SQL Server Database. After creating the SSIS Package in SSIS it saves to a folder on SSMS. I have set it up like the below:

I basically have all admin rights on this instance. When I try to run the package I am hit with a fail saying:

04/14/2021 10:05:27,Testing SSIS,Error,1,XXXX\INSTANCE04,Testing SSIS,Upload,,Non-SysAdmins have been denied permission to run ANALYSISCOMMAND job steps without a proxy account. The step failed

and this error sometimes too:

04/14/2021 10:05:27,Testing SSIS,Error,1,XXXX\INSTANCE04,Testing SSIS,Upload,,Non-SysAdmins have been denied permission to run DTS Excecution jobs steps without a proxy account. The step failed

I plan to run multiple packages in this job later on If someone could help I would be very grateful!

Agent tasks don't run under your credentials they run under the Agent Service Account; which shouldn't be a sysadmin. The error, however, is telling you what to do here... "Non-SysAdmins have been denied permission to run ANALYSISCOMMAND job steps without a proxy account" — Larnu
@Larnu Ok thanks for the clarification, so I cannot use Windows Credentials to login? Is there a workaround for this? — pandasman
" so I cannot use Windows Credentials to login" I didn't say that at all; in fact SSIS packages must be run under Windows Authentication in SSISDB. — Larnu
@Larnu Ok sorry I am unsure what the best approach to the problem is — pandasman

Mark Wojciechowicz Mark Wojciechowicz · Accepted Answer · 2021-04-14T16:37:12

It looks like you are processing an analysis services model or cube. To resolve this, try the following:

1- Add a credential (stores a windows account credentials in SQL Server)

2- Add a proxy and give it permission to process and SSAS Command and run an ssis package

3- Grant permissions to the account used in the credential to process the SSAS model or cube

4- Configure the job to run under the new proxy account

USE [master];
GO

CREATE CREDENTIAL [<domain\user>]
WITH
    IDENTITY = N'<domain\user>'
  , SECRET = N'<password>';
GO

USE [msdb];
GO

EXEC dbo.sp_add_proxy
    @proxy_name = N'SSAS_Processor'
  , @credential_name = N'<domain\user>'
  , @enabled = 1;
GO

EXEC dbo.sp_grant_proxy_to_subsystem
    @proxy_name = N'SSAS_Processor'
  , @subsystem_id = 10; --ssas command
GO

EXEC dbo.sp_grant_proxy_to_subsystem
    @proxy_name = N'proxy'
  , @subsystem_id = 11; --ssis package
GO

Configure job:

SQL Server Agent Job doesn't execute SSIS package Permission Error

1 Answers