Cannot run a SQL Server SSIS package from SQL Server Agent

0
votes

Some background:

  • I am running SQL Server 2012
  • Let's call the service account running SQL Server Agent: myserv-sa-sqlagent
  • Right now I have it set up so that I CAN: 1) log onto the server as myserv-sa-sqlagent, and 2) connect to the SSIS server via SSMS, and 3) SUCCESSFULLY RUN the package, let's call it myssispack.dtsx from Stored Packages -> MSDB -> [Folder] => myssispack.dtsx

In short then, if I wanted to log into the server as the service account (myserv-sa-sqlagent) and manually right click on each package in the SSIS server and "Run Package" -- I could successfully do that.

I cannot though call the package from SQL Server Agent job via a SSIS Package type step. Temporarily, I have made myserv-sa-sqlagent an administrator on the server.

Error message when trying to run the package from SQL Server Agent:

Connecting to the Integration Services service on the computer "[my server]" failed with the following error: "Access is denied." By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

1
sql-serverssis
In SQL 2012 I suggest you use package deployment and the SSIS catalog, not the old MSDB storage method. Or do you mean SSISDB? – Nick.McDermaid
Nick: many thanks indeed for your response. To answer your question, I was using the old MSDB storage method -- try as I might, I could not get that to work. I did add the SQL Server Agent service account as a db_owner on MSDB, and I got a different error message... In any event though, the SSIS catalog method is up-and-running with packages firing via SQL Server agent. So I am now all set. – Bob Harford

1 Answers

1
votes

By default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.

To grant access to the Integration Services service:

Source MSDN

  1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
  2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
  3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  4. On the Security tab, click Edit in the Launch and Activation Permissions area.
  5. Add users and assign appropriate permissions, and then click Ok.
  6. Repeat steps 4 - 5 for Access Permissions.
  7. Restart SQL Server Management Studio.
  8. Restart the Integration Services Service.