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
Thank you!! Works perfectly!!! /Jesper Wilfing
ReplyDeleteHi I´m having problems compiling this, I an error in this DLL Microsoft.DataTransformationServices.Design, I have version 11.0 instead of 10.0.
DeleteCan you post a compiled version of this?
Thanks.
Hi I´m having problems compiling this, I an error in this DLL Microsoft.DataTransformationServices.Design, I have version 11.0 instead of 10.0.
ReplyDeleteCan you post a compiled version of this?
Thanks.
What is the error? I assume you have changed the reference to the newer version of the dll in the project in visual studio.
DeleteYes, I have SQL Server Management Studio 2012, and I don´t have the version 10.0 of that DLL. I tried changing the reference to version 11.0 without success.
DeleteIn this using:
using Microsoft.DataTransformationServices.Design;
I get this error:
The type or namespace name 'DataTransformationServices' does not exist in the namespace 'Microsoft'
(are you missing an assembly reference )C:\Users\xxxx\Desktop\main\SharePointListAdapters\Src\SharePointListConnection\CredentialConnectionManagerUI.cs
You'll need to use a 'real' ;) version of visual studio rather than the version that comes with sql server. hope this helps.
DeleteI have a full version of Visual Studio Professional. And only have the client version of SQL Server 2012, not the server. Do I need the server for this?
DeleteThis comment has been removed by the author.
Delete