February 13, 2018 at 10:25 am
I have an SSIS package that uses a script task WebRequest object to download a file from a Sharepoint page. This works fine in Visual Studio, but won't work under SQL Agent.
The SQL Agent service account was added to the SharePoint page security, and should have the same rights as my regular login. The error thrown by the WebRequest.GetResponse() method is "Error opening web request: The remote server returned an error: (401) Unauthorized.".
I created a credential for my own login, and an SSIS proxy for that credential. Setting the SSIS job step to run as that proxy produces the same error.
The SQL Server and SharePoint server are in the same domain. I am not aware of any firewall issues between the servers. If I connect to the SQL Server with Remote Desktop, I have no problem downloading the file using the SharePoint URL. I get the same error if I run the package manually with dtexec (while remotely connected to the SQL Server).
So what is different between running a package in the dev environment versus dtexec, under the same login, that would cause WebRequest.GetResponse() to fail?
February 13, 2018 at 10:49 am
I just ran some more tests and tried running the package on my laptop via DTEXEC. It failed. The same package and configuration runs successfully in Visual Studio on my laptop. So this is not related to the SQL Server, SQL Agent, or the service account, just to DTEXEC.
February 13, 2018 at 11:05 am
Scott Coleman - Tuesday, February 13, 2018 10:49 AMI just ran some more tests and tried running the package on my laptop via DTEXEC. It failed. The same package and configuration runs successfully in Visual Studio on my laptop. So this is not related to the SQL Server, SQL Agent, or the service account, just to DTEXEC.
VS spawns DTExec to run packages while in debug mode, so there's more to it than that. I wonder whether Kerberos could be a factor.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 14, 2018 at 3:01 am
Scott Coleman - Tuesday, February 13, 2018 10:49 AMI just ran some more tests and tried running the package on my laptop via DTEXEC. It failed. The same package and configuration runs successfully in Visual Studio on my laptop. So this is not related to the SQL Server, SQL Agent, or the service account, just to DTEXEC.
Yep, I've got MSDN link for you, the issue could be with IIS on your server.
February 14, 2018 at 4:01 pm
Thanks for your suggestions. I tried adding the UseDefaultCredentials and PreAuthenticate settings suggested by your link, but I didn't check the IIS web pool configuration. The code changes alone did not work.
I will get back to this eventually but right now server patching has created too much havoc.
February 15, 2018 at 2:39 pm
The original version of this package was in SQL 2008/VSDT 2010, and it had no problems with the SharePoint download. It ran in Visual Studio, it ran under DTEXEC, it ran on my laptop, it ran on a SQL 2008 server under SQL Agent, etc
I have given up for now on figuring out why DTEXEC 2016 doesn't like WebRequest.GetResponse() with SharePoint, and will just live with the package running on an old server.
February 16, 2018 at 6:09 am
Scott Coleman - Thursday, February 15, 2018 2:39 PMThe original version of this package was in SQL 2008/VSDT 2010, and it had no problems with the SharePoint download. It ran in Visual Studio, it ran under DTEXEC, it ran on my laptop, it ran on a SQL 2008 server under SQL Agent, etcI have given up for now on figuring out why DTEXEC 2016 doesn't like WebRequest.GetResponse() with SharePoint, and will just live with the package running on an old server.
I would still continue to get a resolution if I were you. Don't give up....
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply