The HTTP request is unauthorized with client authentication scheme 'Ntlm'

Mar 8, 2013 at 3:46 AM
I am using the SharePoint source list adapter and when I try to connect to any list in SharePoint 2007, I got 401 error. This happens in both designer and SQL Agent, and I am sure that my account and the SQL agent account has permission to the SharePoint list.
It works with all other Web Applications within the farm, but fail for all sites within this particular webapp that I need data from. All webapp in the farm are using Windows Integrated Auth with Kerberos.

Error at Data Flow Task [SharePoint List Source [1]]: System.ServiceModel.Security.MessageSecurityException: The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'Negotiate,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.ListsService.ListsSoap.GetListAndView(GetListAndViewRequest request)
at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.ListsService_ListsSoap_GetListAndView(GetListAndViewRequest request)
at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.GetListAndView(String listName, String viewName)
at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointList(String listName, String viewId)
at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointFields(String listName, String viewId)
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)
Error at Data Flow Task [SSIS.Pipeline]: component "SharePoint List Source" (1) failed validation and returned error code 0x80131501.
May 2, 2013 at 5:50 AM
I'm getting the same kind of errors on a system where I don't have admin access for troubleshooting or even to view logs. I need to tell my SharePoint admins what needs to be changed but don't have a way to verify which permisions or which systems, IE or SP, need the fix.
Jun 19, 2013 at 6:37 PM
Short answer: try adding an entry for the SharePoint website in the hosts file (windows/system32/drivers/etc/hosts).

Long, somewhat unclear answer: Our SP site is sitting behind a load balancer. A cookie is passed to the SSIS server telling it where to look for the site. The SharePoint List source component doesn't play nicely with the cookie, and somehow that messes it up. Adding the host file entry got it working again.

Aug 28, 2013 at 5:30 PM
I got the unauthorized error and it ended up being the SQL Service Agent Account running the job did not have read permission to the SharePoint list.