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.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)
--- 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)
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)
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