Sql-server – SSIS Package with SharePoint fails when scheduled, but runs fine manually from SSIS Store (SQL 2008)

sharepointsql serversql-server-2008ssis

I have an SSIS job that gets data from a SharePoint 2010 list that runs perfectly from Visual Studio and also if I trigger it manually from the SSIS store, but not when scheduled as a job in the SQL Server DB Engine instance.

I am doing everything using just one super user account called "spadmin".

  • It is the account I am using to log into a Windows 7 workstation from which I designed the SSIS package and I tend to run SSMS.
  • Is the account used to log into SQL server and have full admin rights to the local server, and to SQL (everything).
  • It is also this user's credentials that I am using in the SSIS package to connect to everything I need.
  • It is also the account specified to run the scheduled job.

If I connect to the SQL server's SSIS store (not the DB engine) via SQL Server Management Studio (SSMS) and I right click on the job in question that is stored under "Stored Packages\MSDB" and choose to execute it, the job will run without any issues. This happens whether I am using the local SSMS installed on the SQL Server in question, or if I am using a SSMS installation on a remote workstation.

However if I schedule the job through the same SQL's server database engine, the job will fail — both on a schedule and if I try to run the job manually.

Now here is the puzzling bit: The job will not fail if I have on the background a remote desktop connection into the SQL server with that super user account (i.e. spadmin) while I run the job. By on the background I mean that I am not doing anything on this remote desktop connection except login in with the super user account.

When the job fails, I get the following "Bad Gateway" error (see end of post) that suggests the problem is accessing SharePoint. However since I can run this job via the SSIS store with the same account for which the job has been scheduled, there is no doubt that this job is capable of running from the SQL Server.

Server build: 10.50.1617

I am going mental here. Any ideas of what the problem might be?

Here is the full error message for completeness sake:

Message Executed as user: MYDOMAIN\spadmin. …n 10.50.1600.1 for
64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 13:19:34 Error: 2017-01-27 13:19:34.76 Code: 0xC0047062
Source: Copy sharepoint list data SharePoint List Source [1]
Description: System.ServiceModel.ProtocolException: The remote server
returned an unexpected response: (502) Bad Gateway. —>
System.Net.WebException: The remote server returned an error: (502)
Bad Gateway. at System.Net.HttpWebRequest.GetResponse() at
timeout) — End of inner exception stack trace — Server
stack trace: at
request, HttpWebResponse response, HttpChannelFactory factory,
WebException responseException, ChannelBinding channelBinding) at
timeout) at
System.ServiceModel.Channels.RequestChannel.Request(Message message,
TimeSpan timeout) at
message, TimeSpan timeout) at
System.ServiceModel.Channels.ServiceChannel.Call(String action,
Boolean oneway, ProxyOperationRuntime operation, Object[] ins,
Object[] outs, TimeSpan timeout) at
methodCall, ProxyOperationRuntime operation) at
message) Exception rethrown at [0]: at
reqMsg, IMessage retMsg) at
msgData, Int32 type) at
request) at
request) at
listName) at
listName) at
listName) at
listName, String viewName) at
sharepointUri, NetworkCredential credentials, String listName, String
viewName) at
sharepointUrl, String listName, String viewName) at
wrapper) End Error Error: 2017-01-27 13:19:34.76 Code:
0xC0047017 Source: Copy sharepoint list data SSIS.Pipeline
Description: component "SharePoint List Source" (1) failed validation
and returned error code 0x80131501. End Error Error: 2017-01-27
13:19:34.76 Code: 0xC004700C Source: Copy sharepoint list data
SSIS.Pipeline Description: One or more component failed
validation. End Error Error: 2017-01-27 13:19:34.76 Code:
0xC0024107 Source: Copy sharepoint list data Description:
There were errors during task validation. End Error DTExec: The
package execution returned DTSER_FAILURE (1). S… The package
execution fa… The step failed.

Best Answer

Here is the answer.

Even though the job was configured to run via a PROXY Account, the SQL Server Agent is still responsible for the job. I had a look and the SQL Server agent was configured to run under the Local System Account on that server. So what I did is to put the agent to run under the superuser admin account and it worked as expected.

Now in this case the fact that the job no longer needs a proxy since the Server Agent itself is running under the ultimate account. However I appreciate that this is not the right way moving forward (even though this isn't my server and I hope I never get to touch it again!) I will be advising the customer to reconfigure SQL so every service runs under a dedicated domain account (i.e. created solely for this purpose), which is the way it should be!

Now what I would love to understand is why the job would run as long as the proxy account used for scheduling the job was logged into the SQL server!