Beta SP List Adapter - Problem with custom credentials

Aug 2, 2010 at 5:57 PM
Edited Aug 2, 2010 at 6:02 PM

I'm trying to use Beta SP List Adapter to connect to SP site with custom credentials. I've created new SPCRED connection manager, filled credential data, added SP List Source. It seems to work in design time as I was able to configure SP List Source and I saw all column names from my SP list.

However, when I try to execute it (from debugger or externally), I got this error:

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task 1, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0047062 at Data Flow Task 1, 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: 0xC0047017 at Data Flow Task 1, SSIS.Pipeline: component "SharePoint List Source" (1) failed validation and returned error code 0x80131501.
Error: 0xC004700C at Data Flow Task 1, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task 1: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.

Any ideas?

Thanks

Coordinator
Aug 3, 2010 at 6:29 AM

If it works in design but not elsewhere, then it is because you did not create a location to store the credential which is safe.  

Check out this site for how to store the username/pw so that you can run it outside of the designer:

http://support.microsoft.com/kb/918760

 

Aug 4, 2010 at 9:40 PM

Thanks for a quick response.

I tried to set different levels of ProtectionLevel property, but it didn't solve my problem. However I don't think this is the cause, because ProtectionLevel was set to EncryptSensitiveWithUserKey by default, so it should be working when executed from the same instance of Visual Studio in which it is working in design time.

I tried to set ProtectionLevel to EncryptSensitiveWithPassword. ProtectionLevel seemed to work as it asked me for password and I had to pass proper password to dtexec.exe when I tried to execute task from command line. But even if I passed proper password and all error messages about password was gone I still received 401 HTTP error.

Another test I made was configuring ADO.NET source to connect to external database with SQL authentication. I passed user/password to ADO.NET connection manager and then I tried to execute task and it worked - so it seems that credentials for this connection were stored correctly.

Maybe I'm missing something important. Do you have any other hints?

Info about my configuration:

1. Server I try to connect: Sharepoint Server 2010 installed on standalone Windows Server 2008 without any domain - for the moment we are using local groups and accounts.

2. My workstation: Windows Server 2008 with SQL Server 2008 Developers Edition. Again no domain.

Both machines are virtualized, but I don't think it is a problem as most machines in our company are. There is also OpenVPN tunel between my workstation and server, but again I don't think it is a cause.



Thanks for any advice.

Aug 9, 2010 at 7:31 PM

I'm experiencing the same problem.  I'm using VS2008 to design and run the package.   I've also tried using EncryptSensitiveWithPassword with no success.

I can see the list columns in my SP List Source using the SP Credential with custom credentials when designing. 

But when I run the Data Flow, I get the same error as above.

May 11, 2012 at 7:29 AM

Hi,

did you find a solution for this?

 

Jun 5, 2012 at 2:11 PM

Hi,

I have the same problem!

Can anybody help us?

Oct 30, 2013 at 6:07 PM
Edited Oct 30, 2013 at 7:03 PM
Just an update, found that the obstacle existed on my side In the Component Properties the SiteURL wasn't set correctly. All works fine now... :)

We just upgraded to SharePoint 2013 and what seemed to work just fine in SharePoint 2010 is no longer working. :(