4

Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.

12/03/2012 17:32:15,New_App_R,Error,1,SQLAAG01,New_App_R,New_App_R,,
Executed as user: NT Service\SQLSERVERAGENT.
Started:  5:32:15 PM
Could not load package "\MSDB\New_App_R" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "SQLAAG01" 
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.

I also tried creating the same package and saved it on the file system (desktop) and when I try to execute the package using the below code in a job or in a query window 

EXEC @returncode = xp_cmdshell 'dtexec /f "C:\Users\jvelez\Desktop\New_App.dtsx"'

I get another access denied error that says:

Description: Failed to open package file "C:\Users\jvelez\Desktop\New_App.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs when loading a package
and the file cannot be opened or loaded correctly into the XML document. This can
be the result of specifying an incorrect file name when calling LoadPackage or the
specified XML file has an incorrect format.

If I double click the package on my desktop it runs and works perfectly, but what I need to do is schedule the package to run. I tried creating jobs in different ways and adding myself to the administrators group but nothing seems to work. Any advice would be greatly appreciated.

I'm running SQL Server 2012 on Windows Server 2008

Juan Velez
  • 3,255
  • 17
  • 52
  • 72
  • 2
    "Executed as user: NT Service\SQLSERVERAGENT." -- this service account needs to be granted permissions to access the package. – Jon Seigel Dec 04 '12 at 17:47
  • 1
    That wouldn't explain why he got an access denied error while running it manually so there may be something else going on as well. Have you checked what the package does and confirmed that all files and permissions are correct for the account running the package? – cfradenburg Dec 04 '12 at 17:55
  • 1
    @cfradenburg: xp_cmdshell runs as the database engine service account (for sysadmins), which presumably doesn't have access either. – Jon Seigel Dec 04 '12 at 18:04
  • 1
    The reason why the xp_cmdshell failed could be due to the fact that you're connecting to SQL as a login in the sysadmin group, which will run xp_cmdshell as the SQL service account. This serivce account may not have rights to the location of the dtsx package. – Keith Tate Dec 04 '12 at 18:05
  • 1
    @KeithTate you were absolutely right. I was connecting to SQL as a login in the sysadmin group. I gave the rights to the service account to the actual package file and it worked great. Thanks! Only problem I have now is that No matter what privileges and rights (administrator and sysadmin) I give to the SQLSERVERAGENT and the MSSQLSERVER accounts I still can't run the package on the SQL Server (same package but on server). I am trying to run the package from a Step in a JOB. I put some more details in my replay to your answer below. – Juan Velez Dec 05 '12 at 03:18
  • If your package resides on a network, map network path to a drive and try opening from there. – Prabhakar Kafle Dec 12 '16 at 14:42

2 Answers2

4

The 'Service\SQLSERVERAGENT' service account needs access to the file system to run that package.

You can also setup a proxy for the SSIS package and run the job using that proxy account.

Keith Tate
  • 409
  • 2
  • 4
  • Thank I was able to finally get the Package in the file system (desktop) to workby adding Full control rights to NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER to the actual package file BUT I have not been able to run the Package from the SQL Server. I added both NT Service accounts as administrators on the server as well as in the sysadmin roles in SSMS. I still get the "Access is denied." By default only administrators have access to the Integration Services service" message. Any suggestions??? Im running Windows Server 2008 R2 enterprise and trying to run package from job step. – Juan Velez Dec 05 '12 at 03:19
  • I'm going to try and set up a Proxy account now to run the job. If that works then I'll be good to go. – Juan Velez Dec 05 '12 at 14:37
  • 1
    I had a similar problem which was fixed when I changed the package source from "SSIS Package Store" to "SQL Server" – kappamaki Apr 25 '15 at 10:37
  • @PokerPlayer23, in your last update you said "If that works then I'll be good to go." Did it work?? What's the job owner? – Doug_Ivison Jan 28 '16 at 14:55
  • @PokerPlayer23, Is it the job owner that needs permissions, or the "log on as" of the "SQL Server Agent" service, or both? Did you grant permissions using Management Studio (granting specific permissions, or sysadmin), or DCOMCNFG.EXE (granting local launch & execute, or what)? – Doug_Ivison Jan 28 '16 at 15:03
  • @Doug_Ivison Being that this post is from 2012, to be 100% honest, I don't remember the outcome but I am pretty sure it worked because if it didn't then I would have most likely posted back asking for more help. Sorry. – Juan Velez Feb 01 '16 at 19:16
0

Most of the time you can pass through security via the properties of the Step by going to Edit Step->General->Configuration->Connection Managers & selecting your connection string properties or password...

Shirzad Sharif
  • 1