November 9, 2018 at 10:17 am
Hi,
The setup:
SQL 2014 Enterprise Standalone install on node of cluster (BizTalk etc)
SQL 2014 Running SSIS and Reporting, SSIDB on one node of cluster (not the same cluster)
Issue:
Have a package that uses a UNC path to the server with BizTalk on, share exists and has the rights for everyone to at least read, the service account and admins to have full control. When the package is executed from the package store, it runs and completes fine, file is created etc. When we put it in a job on the BizTalk server, it fails with:
Cannot open the datafile \\Server\Share\RestofPath\File.csv
error <destination> failed the pre-execute phase and returned error code 0xC020200E
Which is a permissions issue. Now, before you point me to the 1000's of articles on this, I have probably read a fair few of them already, so I have had the GPO changed to include the service account in all the places it needs to be, I have ensured it has the control at each level of the tree in the path. The file just doesn't want to be created by this account and I can't see what is missing? Is it because it does a double hop (even though it is there and back as such), I just do not know and can't figure it out?
Any other ideas?
November 9, 2018 at 11:39 am
Rick-153145 - Friday, November 9, 2018 10:17 AMHi,The setup:
SQL 2014 Enterprise Standalone install on node of cluster (BizTalk etc)
SQL 2014 Running SSIS and Reporting, SSIDB on one node of cluster (not the same cluster)Issue:
Have a package that uses a UNC path to the server with BizTalk on, share exists and has the rights for everyone to at least read, the service account and admins to have full control. When the package is executed from the package store, it runs and completes fine, file is created etc. When we put it in a job on the BizTalk server, it fails with:Cannot open the datafile \\Server\Share\RestofPath\File.csv
error <destination> failed the pre-execute phase and returned error code 0xC020200E
Which is a permissions issue. Now, before you point me to the 1000's of articles on this, I have probably read a fair few of them already, so I have had the GPO changed to include the service account in all the places it needs to be, I have ensured it has the control at each level of the tree in the path. The file just doesn't want to be created by this account and I can't see what is missing? Is it because it does a double hop (even though it is there and back as such), I just do not know and can't figure it out?
Any other ideas?
Do you use the same service account for the SQL Server and SQL Server Agent services?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 9, 2018 at 11:52 am
No, two separate accounts. Would it matter in this situation as it should be using the Agent account to run anything outside of SQL shouldn't it?
November 9, 2018 at 1:21 pm
Yes by default it would be accessing the file share as the account that SQL Agent service is running as. The best practice for this type of scenario would actually be to setup a credential in SQL Server and set the SQL Agent job to run the SSIS package as that proxy and grant file share permissions to the stored credential:
https://www.red-gate.com/simple-talk/sql/database-administration/setting-up-your-sql-server-agent-correctly/
November 10, 2018 at 3:34 am
Thanks Chris, I have tried that with a domain admin account , still the same error, hence the confusion as with those rights it should just work regardless.
Its frustrating that I can run it manually from the SSIS server, yet running it as a job from the db server causes this error, I am wondering whether it's classing it as a double hop, but if it was, it would complain about the network service user surely (Its not, the context is the domain admin user [me])?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply