SSIS SQL Server Agent Schedule

1
votes

I have no issue on Execution of SSIS Packages on SSIS Services, but during SQL Server Agent execution I got below issue. I'm using Administrator rights.

Below are my specs

  • MSSQL Server 2014 Standard 64bit
  • Windows Server 2012R2 Standard

Issue/Error on SQL Server Agent History Log

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.6024.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:57:50 PM Could not load package "\File System\ETLPackages" because of error 0xC00160AE. Description: Connecting to the Integration Services service on the computer "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. Source:
Started: 5:57:50 PM Finished: 5:57:50 PM Elapsed: 0 seconds. The package could not be loaded.

1
sql-serverssis
In the Agent Job who is the owner when you go to Job Properties? That may not be the sa/admin account. Or you SSIS server and job server can not access/talk to each other properly or the admin accounts need same permissions from one server to the other. – Brad

1 Answers

0
votes

Cause of the error -- account under which the job is run - NT Service\SQLSERVERAGENT - does not have an access to the SSIS Service. Either create a SSIS proxy with sufficient rights (be a member of local Admins) - quick and dirty solution, or give rights for SSIS Service as follows.
Basic steps, from MS Docs. To grant users rights to the Integration Services service:

  1. Open Component Services; from a Run dialog, you can enter "dcomcnfg".
  2. On the left-hand tree, navigate to Component Services - Computers - My Computer - DCOM Config.
  3. Find "Microsoft SQL Server Integration Services 12.0".
  4. Open Properties - Security tab and for each type of permission hit Edit and add an appropriate group or user. Be sure to check the specific permissions required, such as Remote Launch when editing Launch and Activation Permission. For SSIS package start - give Local Launch in Launch and Activation Permissions and Local Access in Access Permissions.
    Or better - create a user group and give permissions to it and then include needed account into this group.
  5. Launch Services, and restart the SQL Server Integration Services service.