If you have the need to extract data from a SharePoint list into a SQL Server table most probably your are using (or tried to) the SharePoint List Source and Destination components available in CodePlex (http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652). And maybe you had the unpleasant experience to get the following error once you try to run the package:
[SharePoint List Source ] 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.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)
And then you most probably tried to figure out what happens. You can see the list columns in design mode, but once you run the package you receive the error. Why this happens I still don’t know, but I’ve found a solution/explanation:
Even though in the SharePoint Credentials components you specified a correct username and password to access the SharePoint list, somehow that user is not the one that is being used once the package is running. The logged user is the one being used. In the case that this user, most probably your user, cannot access the SharePoint list for any reason, the above error message will appear.
In my case what happened is that I was logged into the development machine using a local account, instead of a domain account. Once I logged in using a domain account with access to the SharePoint list all worked perfectly.
I’m guessing that this behavior shouldn’t be like this, but so far is what we have, right? 🙂
I hope this saves you some time trying to figure out how to workaround this error.
PS: I’ve found the inspiration to for this workaround by having a look at the “Package Explorer” tab once the package is running. Check the “System:UserName” “Variable” inside the “Data Flow Task” that you have created.
PS2: The connector only works in 32-bits inside Visual Studio, remember to change this property in the projects’ Properties page.