Thursday, May 24, 2012

SSIS sharepoint Codeplex error: The HTTP request is unauthorized with client authentication scheme 'Ntlm'

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