Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Permissions to Schedule an SSIS Package from SQL Server Agent and the File System


By:   |   Read Comments (2)   |   Related Tips: More > Integration Services Error Handling

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

When trying to schedule a SQL Server Integration Services (SSIS) Package to run from SQL Server Agent, you get the following error:

“Connecting to the Integration Services service on the computer “…” 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.”

Connecting to the Integration Services service on the computer failed with the following error: Access is denied.
Solution

As the error message states, the user that is running the job step does not have administration privileges to connect to the Integration Services service even in this case we are using the SQL Server Agent service account.

Follow the next steps to provide the necessary privileges to the account that is running the job step:

  1. Open the Component Services (from the Windows Start menu type “dcomcnfg”)
  2. Drill down to Console Root \ Component Services \ Computers \ My Computer \ DCOM CONFIG as shown in the next figure
Windows Component Services
  1. Locate the entry for the Microsoft SQL Server Integration Services version that you are working with and right-click on it and click on the Properties option. The respective Properties window will pop-up and choose the Security tab
Security tab of Component Services
  1. Edit the Launch and Activation Permissions option and add the user that is running the Job step allowing the Local Launch and Local Activation privileges:
Launch and Activation Permission in Component Services
  1. Edit the Access Permissions option and add the user that is running the Job step allowing the Local Access privilege:
Access Permissions in Component Services
  1. Restart the respective SQL Server Integration Services service

Note For DTSX Packages in the File System

If you are running the dtsx package from the file system and you receive the following error after executing the previous steps, then you will need to give read permissions to the folder for the same user that is running the job step.

Error for DTSX Packages in the File System
Security tab of Package Properties
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, September 15, 2017 - 7:19:47 AM - Vitor Montalvão Back To Top

Carl, thank you for your comment.

In your solution which permissions that credential needs?


Thursday, September 14, 2017 - 2:26:02 PM - Carl Back To Top

 

 It would seem to me that it would be better to handle this in SQLServer than in DCom at the OS level. Add a credential for a the service service account you want to use, Add an Agent proxy for "SSIS Package Execution" using that credential. They anyone who edits the job can run job steps that use SSIS using the proxy account.  No need to ever leave SQLServer.

 


Learn more about SQL Server tools