0

My project is using netnamedpipe service for performing CRUD operations on the databases and accessing database objects. Problem: When I am consuming the NetNamedpipe service in SSIS and running the job through SQL server Agent by using Localhost credentials(using Windows Authentication) in SSIS configuration manager the job is executing successfully and performing the CRUD operations on the tables as expected. But when I am running the same job using SSIS proxy user which is again on Windows Authentication mode, it is giving me the below attached error.

Executed as user: {domain\Proxyuser} 
. ...System.ServiceModel.Channels.PipeConnectionInitiator.GetPipeName(Uri uri, IPipeTransportFactorySettings transportFactorySettings)
     at System.ServiceModel.Channels.NamedPipeConnectionPoolRegistry.NamedPipeConnectionPool.GetPoolKey(EndpointAddress address, Uri via)
     at System.ServiceModel.Channels.CommunicationPool`2.TakeConnection(EndpointAddress address, Uri via, TimeSpan timeout, TKey& key)
     at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)
     at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.OnOpen(TimeSpan timeout)
     at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
     at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
     at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    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 System.ServiceModel.ICommunicationObject.Open(TimeSpan timeout)     at System.ServiceModel.ClientBase`1.System.ServiceModel.ICommunicationObject.Open(TimeSpan timeout)
     at ST_4afb40bbfa44442385f42de501c36110.csproj.ScriptMain.Execute()

The package executed successf... The step succeeded.

Do we have any separate way to consume WCF/netnamedpipe service with SSIS proxy user?

Note : Proxy user has login credentials for accessing the necessary databases objects for CRUD operation.

Kindly assist me with this issue, as I am facing this problem from past few months and running the job with SQL server Agent role , which is not preferable in PROD environment.

Cristik
  • 30,989
  • 25
  • 91
  • 127
Prateek Ahuja
  • 1
  • How do you host your WCF service? If IIS, the user executed the CRUD operation could be configured in the IIS application pool identity. In addition, Impersonation and delegation can enable the service to perform actions on behalf of the client, but it is not feasible for Netnamedpipebinding. – Abraham Qian Jan 30 '20 at 07:03
  • We are using the WCF service using services.msc in our local machines and same is the environment for Production, but the question here is how the Authentication is getting worked when I am executing the job using SQL server Agent and why it is not working with SSIS proxy user? – Prateek Ahuja Feb 04 '20 at 06:46
  • In WCF, the identity running the client cannot be passed to the server. It requires that the impersonation of WCF be turned on before the CRUD operation can be performed on behalf of the client identity. I'm not sure how you call it, but it must work if you change the execution identity on the server-side of the managed service, such as the login account of windows NT service. – Abraham Qian Feb 05 '20 at 09:08

0 Answers0