SSIS sharepoint Codeplex error with having multiple authentication methods in one authentication provider. ie. NTLM and FBA
The error below:
[SharePoint List Source [1]] Error: System.ServiceModel.Security.Messag eSecurityException: 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.GetRespon se()
at System.ServiceModel.Channels.HttpCh annelFactory.HttpRequestChannel.Htt pChannelRequest.WaitForReply(TimeSp an timeout)
--- End of inner exception stack trace ---
Server stack trace:
at System.ServiceModel.Channels.HttpCh annelUtilities.ValidateAuthenticati on(HttpWebRequest request, HttpWebResponse response, WebException responseException, HttpChannelFactory factory)
at System.ServiceModel.Channels.HttpCh annelUtilities.ValidateRequestReply Response(HttpWebRequest request, HttpWebResponse response, HttpChannelFactory factory, WebException responseException)
at System.ServiceModel.Channels.HttpCh annelFactory.HttpRequestChannel.Htt pChannelRequest.WaitForReply(TimeSp an timeout)
at System.ServiceModel.Channels.Reques tChannel.Request(Message message, TimeSpan timeout)
at System.ServiceModel.Dispatcher.Requ estChannelBinder.Request(Message message, TimeSpan timeout)
at System.ServiceModel.Channels.Servic eChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.Servic eChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
at System.ServiceModel.Channels.Servic eChannelProxy.InvokeService(IMethod CallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.Servic eChannelProxy.Invoke(IMessage message)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.Rea lProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.Rea lProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.ListsService.ListsS oap.GetListAndView(GetListAndViewRe quest request)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.ListsService.ListsS oapClient.ListsService_ListsSoap_Ge tListAndView(GetListAndViewRequest request)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.ListsService.ListsS oapClient.GetListAndView(String listName, String viewName)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.Adapter.ListsAdapte r.GetSharePointList(String listName, String viewId)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.Adapter.ListsAdapte r.GetSharePointFields(String listName, String viewId)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointUtility.ListServiceUtility. GetFields(Uri sharepointUri, NetworkCredential credentials, String listName, String viewName)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointListAdapters.SharePointList Source.GetAccessibleSharePointColum ns(String sharepointUrl, String listName, String viewName)
at Microsoft.Samples.SqlServer.SSIS.Sh arePointListAdapters.SharePointList Source.ValidateSharePointColumns()
at Microsoft.Samples.SqlServer.SSIS.Sh arePointListAdapters.SharePointList Source.Validate()
at Microsoft.SqlServer.Dts.Pipeline.Ma nagedComponentHost.HostValidate(IDT SManagedComponentWrapper100 wrapper)
To fix:
Need to change the code in the ListsAdapter.vb and ViewsAdapter.vb in the SharePointUtility project.
add the following function:
Private Sub ResetConnection()
' Setup the binding with some enlarged buffers for SharePoint
Dim binding = New BasicHttpBinding()
' Change the security mode if we're using http vs https
If (_sharepointUri.Scheme.ToLower() = "http") Then
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly
ElseIf (_sharepointUri.Scheme.ToLower() = "https") Then
binding.Security.Mode = BasicHttpSecurityMode.Transport
Else
Throw New ArgumentException("Sharpeoint URL Scheme is not recognized: " + _sharepointUri.Scheme)
End If
' Send credentials and adjust the buffer sizes (SharePoint can send big packets of data)
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm
binding.MaxReceivedMessageSize = Int32.MaxValue
binding.ReaderQuotas.MaxBytesPerRead = Int32.MaxValue
binding.ReaderQuotas.MaxArrayLength = Int32.MaxValue
binding.ReaderQuotas.MaxDepth = Int32.MaxValue
binding.ReaderQuotas.MaxNameTableCharCount = Int32.MaxValue
binding.ReaderQuotas.MaxStringContentLength = Int32.MaxValue
binding.OpenTimeout = New TimeSpan(24, 0, 0)
binding.CloseTimeout = New TimeSpan(24, 0, 0)
binding.ReceiveTimeout = New TimeSpan(24, 0, 0)
binding.SendTimeout = New TimeSpan(24, 0, 0)
' Create the client with the given settings
Dim ep = New EndpointAddress(_sharepointUri)
' Create the client object
If (Not _sharepointClient Is Nothing) Then
Dim dispose As IDisposable = _sharepointClient
dispose.Dispose()
_sharepointClient = Nothing
End If
_sharepointClient = New ViewsService.ViewsSoapClient(binding, ep)
' Only need to add this once, the endpoint will be shared for future instances
Dim clientCredentials As Description.ClientCredentials = _
(From e In _sharepointClient.Endpoint.Behaviors _
Where TypeOf (e) Is Description.ClientCredentials).Single()
clientCredentials.Windows.AllowedImpersonationLevel = _
TokenImpersonationLevel.Impersonation
clientCredentials.Windows.ClientCredential = _credential
AddCustomHeader(New System.ServiceModel.OperationContextScope(_sharepointClient.InnerChannel))
End Sub
The error below:
[SharePoint List Source [1]] Error: System.ServiceModel.Security.Messag
at System.Net.HttpWebRequest.GetRespon
at System.ServiceModel.Channels.HttpCh
--- End of inner exception stack trace ---
Server stack trace:
at System.ServiceModel.Channels.HttpCh
at System.ServiceModel.Channels.HttpCh
at System.ServiceModel.Channels.HttpCh
at System.ServiceModel.Channels.Reques
at System.ServiceModel.Dispatcher.Requ
at System.ServiceModel.Channels.Servic
at System.ServiceModel.Channels.Servic
at System.ServiceModel.Channels.Servic
at System.ServiceModel.Channels.Servic
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.Rea
at System.Runtime.Remoting.Proxies.Rea
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.Samples.SqlServer.SSIS.Sh
at Microsoft.SqlServer.Dts.Pipeline.Ma
To fix:
Need to change the code in the ListsAdapter.vb and ViewsAdapter.vb in the SharePointUtility project.
add the following function:
Private Sub AddCustomHeader(ByVal scope As System.ServiceModel.OperationContextScope)
Dim reqprop As System.ServiceModel.Channels.HttpRequestMessageProperty = New System.ServiceModel.Channels.HttpRequestMessageProperty()
reqprop.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
System.ServiceModel.OperationContext.Current.OutgoingMessageProperties(System.ServiceModel.Channels.HttpRequestMessageProperty.Name) = reqprop
End Sub
To the end of the method ResetConnection():
for example:
Private Sub ResetConnection()
' Setup the binding with some enlarged buffers for SharePoint
Dim binding = New BasicHttpBinding()
' Change the security mode if we're using http vs https
If (_sharepointUri.Scheme.ToLower() = "http") Then
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly
ElseIf (_sharepointUri.Scheme.ToLower() = "https") Then
binding.Security.Mode = BasicHttpSecurityMode.Transport
Else
Throw New ArgumentException("Sharpeoint URL Scheme is not recognized: " + _sharepointUri.Scheme)
End If
' Send credentials and adjust the buffer sizes (SharePoint can send big packets of data)
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm
binding.MaxReceivedMessageSize = Int32.MaxValue
binding.ReaderQuotas.MaxBytesPerRead = Int32.MaxValue
binding.ReaderQuotas.MaxArrayLength = Int32.MaxValue
binding.ReaderQuotas.MaxDepth = Int32.MaxValue
binding.ReaderQuotas.MaxNameTableCharCount = Int32.MaxValue
binding.ReaderQuotas.MaxStringContentLength = Int32.MaxValue
binding.OpenTimeout = New TimeSpan(24, 0, 0)
binding.CloseTimeout = New TimeSpan(24, 0, 0)
binding.ReceiveTimeout = New TimeSpan(24, 0, 0)
binding.SendTimeout = New TimeSpan(24, 0, 0)
' Create the client with the given settings
Dim ep = New EndpointAddress(_sharepointUri)
' Create the client object
If (Not _sharepointClient Is Nothing) Then
Dim dispose As IDisposable = _sharepointClient
dispose.Dispose()
_sharepointClient = Nothing
End If
_sharepointClient = New ViewsService.ViewsSoapClient(binding, ep)
' Only need to add this once, the endpoint will be shared for future instances
Dim clientCredentials As Description.ClientCredentials = _
(From e In _sharepointClient.Endpoint.Behaviors _
Where TypeOf (e) Is Description.ClientCredentials).Single()
clientCredentials.Windows.AllowedImpersonationLevel = _
TokenImpersonationLevel.Impersonation
clientCredentials.Windows.ClientCredential = _credential
AddCustomHeader(New System.ServiceModel.OperationContextScope(_sharepointClient.InnerChannel))
End Sub