NTLM authentication error in Sharepoint List Adapter for SSIS

Aug 18, 2011 at 4:23 PM

SQL Server 2008, SharePoint 2007:  I am using the latest version of the sharepoint list adapter(release 3th Aug) to pull data from sharepoint lists.  SharePoint List adapter for SSIS works fine when executed from BIDS for the user who created the package, but it fails to run when executed by some other user or when deployed in SQL job.

Even tried providing full control for the sharepoint lists to the users but still getting the same error. Tried moving the connection strings to  .dtsConfig file but still getting the same error. It works only for the single user who created the package.

Getting  below error ..

[SharePoint List Source [1]] Error: System.ServiceModel.Security.MessageSecurityException: The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized.
   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.ValidateAuthentication(HttpWebRequest request, HttpWebResponse response, WebException responseException, HttpChannelFactory factory)
   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.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
   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)  


[SSIS.Pipeline] Error: component "SharePoint List Source" (1) failed validation and returned error code 0x80131501.

Sharepoint "

Any help on this is highly appreciated.

 

Thanks

Coordinator
Aug 19, 2011 at 7:34 AM

Check the account actually running the package when you have it installed, and make sure that account can access the SharePoint list.  When running it in the designer, it will run with your credentials. When running it from SQL, it may be using the SQL Agent account, unless you've configured a proxy.  You can check the FAQ section for more details on this.

Sep 8, 2011 at 7:11 PM

Is there anyway to force the sharepoint List Adapter to run on custom user account other than the SQL agent account o proxy users. Something like the SQL or oracle connection mnager can we force the Sharepoint to List adapter to always use the SP connection manager.

Thanks

Shekhar

Coordinator
Sep 9, 2011 at 1:52 PM

Yes, check out the guidance on this here  http://sqlsrvintegrationsrv.codeplex.com/wikipage?title=SharePoint%20List%20Adapters&referringTitle=Documentation

Sep 11, 2013 at 7:12 PM
i had a similar issue when my machine password expired, followed the instructions on this site and package worked perfectly. credits to http://sqlstuff.weebly.com/ssis.html
suggested further reading on the dll http://support.microsoft.com/kb/555631