Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent

Question

I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a folder on the SQL Server server.

The problem is when I created a job in SQL Server Agent and tried to run the job. That is when I get the error:

Executing the query "EXEC ? = dbo.usp_Clear_All_JDAWG_Tables" failed with the following error:

"The EXECUTE permission was denied on the object 'usp_Clear_All_JDAWG_Tables' database 'JDAWGD' schema 'dbo'.".

Possible failure reasons:

Problems with the query "ResultSet" property not set correctly
parameters not set correctly
or connection not established correctly.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

The usp_Clear_All_JDAWG_Tables is the name of the first of many packages this job is trying to run. The stored procs themselves do not use parameters but in the SQL Task I have commands like this to run the stored proc:

EXEC ? = dbo.usp_Load_JDAWG_Tables_From_Persons_All_Agencies

Is there a better/different way to run a stored procedure in the SQL Task?

As a side note, I was able to create and successfully run an Agent job that directly executed the stored procedures that the packages run.

Answer 1

It sounds like your connection manager is setup to use windows Authentication. If that is the case, when you execute the package via the SQL Server Agent it will run with credentials of the agent.

You need to do one of three things:

1. Give the account that the SQL Server Agent runs as appropriate permissions to execute SPs in your database.

2. Setup a proxy account for the agent with the appropriate permissions (as suggested by Jon Seigel).

3. Use a different authentication method - i.e SQL Server Auth.

I would go with option 1 if possible, but it depends what your company's policies are.

Answer 2

We do exactly the same in my place and we use a proxy to deal with the authorisation. It's not the only way, but it is (I think) the MS recommended way and I have certainly found it to work exactly as I needed it to.

We have an AD account called ssisautomation (it can be called whatever really) which has no more permissions than a bog standard user of our domain. We then use a script I wrote (link at end of answer to PasteBin) to set up permissions as part of an install process. Our scenario is that we have an SSIS package that extracts data from one database into a second database, all on the same server, and it uses stored procedures to do so. This script is executed using sqlcmd and takes three arguments:

1. SourceDB
2. TargetDB
3. SSISAccount

Note that the SSISAccount parameter should be in the form CORPDOMAIN\UserName as the script is now, but you can always amend this.

To make it easier for the proxy to execute stored procedures, the script creates a new role in the source database called db_executor. Any member of this role is granted execute permissions on stored procedures in that DB. It will also ensure that the proxy is granted membership of the db_dtsoperator (2005) or db_ssisoperator (2008+) roles on the msdb database dependent upon the version of the target server.

Feel free to use it or amend it to suit your needs. Script can be found here.