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

Improve this question
6
  • 2
    "Executed as user: NT Service\SQLSERVERAGENT." -- this service account needs to be granted permissions to access the package.
    – Jon Seigel
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 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
    Commented Dec 4, 2012 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
    Commented Dec 5, 2012 at 3:18

2 Answers 2

Reset to default
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.

Improve this answer
6
  • 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
    Commented Dec 5, 2012 at 3: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
    Commented Dec 5, 2012 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
    Commented Apr 25, 2015 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
    Commented Jan 28, 2016 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
    Commented Jan 28, 2016 at 15:03
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...

Improve this answer
1
  • 1
    I don't see how this would help the OP as the package doesn't even load
    – Tom V
    Commented Nov 17, 2015 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.