SSIS SharePoint List Source Authentication Error

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 [1764]] 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)

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.

Advertisements
Tagged with: , , , ,
Posted in Errors, SSIS
2 comments on “SSIS SharePoint List Source Authentication Error
  1. Tao says:

    Thanks a lot, this is my problem.

  2. sean says:

    Just be aware that you may get this NTLM error if you’re running the package via the SSIS catalog.
    i.e. right click and execute on the dtsx file.

    this is due to the double hop issue where you authenticate against SSISDB to execute the package, and the SSISDB drops your credentials when it authenticates via the SP List adapter.

    got burnt by this.

    works if you run the same package via SQLAgent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: