2

I have an SSIS 2005 package which is being executed using an SQL Agent Job. This package has a Web Service Task with different credentials than those being used to execute the package.

I am having the following problem when my package is executed on the Server:

"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: System.UnauthorizedAccessException: Access to the path ..... is denied.

The package is being executed on a clustered environment. Both accounts have been created on the server so that package is able to write to TEMP files but problem still persists.

user1161113
  • 21
  • 2
  • SQL Agent user on both servers is Alice, packages have credential information for Bob so that it may connect to the web service, correct? When the package executes, the supplied error message leads you to believe the Bob credentials are not being supplied? – billinkc Feb 01 '12 at 14:08
  • If you execute the package manually with the credentials of someone who is authorized to connect to the service, does it work? If you disable the web service task, does the basic package run under the context of the SQL Agent? – billinkc Feb 01 '12 at 14:09
  • the problem seems to be that the package is trying to write to a TEMP file on the server using the credentials of the web service instead of those with which the package is being executed.. – user1161113 Feb 01 '12 at 15:17
  • Interesting, I'd have to experiment with that to see if I can reproduce your findings. Normally, one would adjust the Buffer/BlobTempStoragePath as addressed in this [question](http://stackoverflow.com/questions/8858463/what-is-the-default-file-path-location-for-buffertempstoragepath-in-ssis-2005). I don't think those exist for web service task but I could be mistaken. – billinkc Feb 01 '12 at 15:31

2 Answers2

0

Based on this thread, you should be able to set the OutputLocation property of the webservice. This will need to be configured to point to a location the credentials of the user invoking the webservice will have access to. This might require an admin modifying ACLs on a directory path.

billinkc
  • 59,250
  • 9
  • 102
  • 159
0

In a clustered environment, you should ensure the location being written to is a clustered resource. For example, you can create file share, but the file share must be a cluster resource so that it is available when the cluster fails over. If you are writing to a drive, ensure the drive is a cluster drive dependent on the proper SQL Server resource.

Phil Bolduc
  • 1,586
  • 1
  • 11
  • 19