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


By:   |   Updated: 2017-09-12   |   Comments (4)   |   Related: More > Integration Services Error Handling


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 Updated: 2017-09-12


get scripts

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





Comments For This Article




Thursday, July 12, 2018 - 8:46:10 AM - Fuhkit Back To Top (76622)

Thank you, very helpful


Thursday, April 19, 2018 - 4:48:58 PM - Jeff K Back To Top (75737)

Thank you! This was very helpful. 


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

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 (66288)

 

 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.

 



download





Recommended Reading

Custom Logging in SQL Server Integration Services SSIS

Capturing and Logging Data Load Errors for an SSIS Package

Continue a Foreach loop after an error in a SQL Server Integration Services package

Retrieve the column causing an error in SQL Server Integration Services

Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers








get free sql tips
agree to terms