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

Question

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 System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout) --- End of inner exception stack trace --- Server stack trace: at System.ServiceModel.Channels.HttpChannelUtilities.ValidateRequestReplyResponse(HttpWebRequest request, HttpWebResponse response, HttpChannelFactory factory, WebException responseException, ChannelBinding channelBinding) at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout) at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout) at System.ServiceModel.Dispatcher.RequestChannelBinder.Request(Message message, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoap.GetViewCollection(GetViewCollectionRequest request) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoapClient.ViewsService_ViewsSoap_GetViewCollection(GetViewCollectionRequest request) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ViewsService.ViewsSoapClient.GetViewCollection(String listName) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ViewsAdapter.GetSharePointListViews(String listName) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ViewsAdapter.GetViewList(String listName) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.LookupViewName(String listName, String viewName) at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.GetFields(Uri sharepointUri, NetworkCredential credentials, String listName, String viewName) at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.GetAccessibleSharePointColumns(String sharepointUrl, String listName, String viewName) at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.ValidateSharePointColumns() at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.Validate() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostValidate(IDTSManagedComponentWrapper100 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.

Answer 1

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!