Friday, August 17, 2012

Using Client Certs to Pull Data from WCF in SSIS Data Flow Transform Script

I've recently had the opportunity to brush off my SSIS skills and revisit this toolset.   In my most recent usage, I had a requirement to use SSIS to pull data from a WCF web service that was a) using the net.tcp protocol, and b) used transport security with a client X.509 certificate for authentication.

This was fun enough by itself.  Configuring WCF tend typcially to be non-trival even when you don't have to tweak app.config files for SQL SSIS services.  One of my goals, in fact, was to avoid having to update that, meaning I had to put code in my SSIS Script block in the data flow to configure my channel & security & such.

Luckily, I was able to find examples of doing this with wsHttpBinding's, so it wasn't a stretch to tweak it for netTcpBinding with the required changes to support certificate authenticated transport security.

Here's the code...
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.ServiceModel;
using SC_13defb16ae45414dbac17137434aeca0.csproj.PaymentSrv;

public class ScriptMain : UserComponent
    ChannelFactory<IProfile> channelFactory;
    IProfile client;
    public override void PreExecute()

        bool fireAgain = false;
        this.ComponentMetaData.FireInformation(0, "Pull From Profile Service.PreExecute", "Service URI: '" + this.Variables.varProfileServiceUrl + "'", null, 0, ref fireAgain);
        this.ComponentMetaData.FireInformation(0, "Pull From Profile Service.PreExecute", "Cert Fingerprint: '" + this.Variables.varClientCertFingerprint + "'", null, 0, ref fireAgain);

        //create the binding
        NetTcpBinding binding = new NetTcpBinding();
        binding.Security.Mode = SecurityMode.Transport;
        binding.Security.Transport.ClientCredentialType = TcpClientCredentialType.Certificate;
        binding.Security.Transport.ProtectionLevel = System.Net.Security.ProtectionLevel.EncryptAndSign;

        EndpointAddress endpointAddress = new EndpointAddress(this.Variables.varPaymentServiceUrl);
        channelFactory = new ChannelFactory<IProfile>(binding, endpointAddress);

            //" x8 60 66 09 t6 10 60 2d 99 d6 51 f7 5c 3b 25 bt 2e 62 32 79");

        channelFactory.Credentials.ServiceCertificate.Authentication.CertificateValidationMode =
        //create the channel
        client = channelFactory.CreateChannel();

        IClientChannel channel = (IClientChannel)client;

        this.ComponentMetaData.FireInformation(0, "Pull From Profile Service.PreExecute", "Open Succeeded.", null, 0, ref fireAgain);


    public override void PostExecute()

        //close the channel
        IClientChannel channel = (IClientChannel)client;

        //close the ChannelFactory


    public override void Input0_ProcessInputRow(Input0Buffer Row)
        Guid txGuid = Guid.NewGuid();
        Profile profile = null;
            profile = client.getProfile(txGuid, Row.ProfileId);
            Row.PSProfileType = GetProfileType(profile);
        catch (Exception ex)
            string message = ex.Message();
            Log(message, 0, null);
    private string GetProfileType(Profile profile)
        return "x";
So one of the challenges I encountered while using this method had to do with the client certificate.  This error drove me nuts:

The credentials supplied to the package were not recognized.
Server stack trace:
   at System.Net.SSPIWrapper.AcquireCredentialsHandle(SSPIInterface SecModule, String package, CredentialUse intent, SecureCredential scc)
   at System.Net.Security.SecureChannel.AcquireCredentialsHandle(CredentialUse credUsage, SecureCredential& secureCredential)
   at System.Net.Security.SecureChannel.AcquireClientCredentials(Byte[]& thumbPrint)
   at System.Net.Security.SecureChannel.GenerateToken(Byte[] input, Int32 offset, Int32 count, Byte[]& output)
   at System.Net.Security.SecureChannel.NextMessage(Byte[] incoming, Int32 offset, Int32 count)
   at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
   at System.ServiceModel.Channels.SslStreamSecurityUpgradeInitiator.OnInitiateUpgrade(Stream stream, SecurityMessageProperty& remoteSecurity)
   at System.ServiceModel.Channels.StreamSecurityUpgradeInitiatorBase.InitiateUpgrade(Stream stream)
   at System.ServiceModel.Channels.ConnectionUpgradeHelper.InitiateUpgrade(StreamUpgradeInitiator upgradeInitiator, IConnection& connection, ClientFramingDecoder decoder, IDefaultCommunicationTimeouts defaultTimeouts, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.SendPreamble(IConnection connection, ArraySegment`1 preamble, TimeoutHelper& timeoutHelper)
   at System.ServiceModel.Channels.ClientFramingDuplexSessionChannel.DuplexConnectionPoolHelper.AcceptPooledConnection(IConnection connection, TimeoutHelper& timeoutHelper)
   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)
   at System.ServiceModel.Channels.CommunicationObject.Open()
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()
   at ScriptMain.PreExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()
If you look at it, this is an authentication error.  Tracing the code, it happens AFTER the code successfully retrieves the client certificate from the certificate store.  The call to SetServerCertificate succeeds without incident.

The error hits  when the code opens the channel, and tries to use the private key attached to the client certificate to prove to the server that "I'm a valid client."

I went nuts because I was an administrator on the machine, and had installed the client certificate to the certificate store myself.  It initially worked, and there was no indication that there was a problem getting the certificate from the cert store.

It turns out that when you use the machine store under these circumstances, I needed to give myself explicit permission to the client certificate in order for the SetServerCertificate to get the private key along with the client certificate.  This was counter-intuitive for two *additional* reasons:  1)  I was an administrator on the box, and already should have had this permission by the fact that my login account belonged to the administrators group (which you can see from the pic below, also had access.)  2)   It worked the day before.  When I imported the private key originally to the key store, it appears somewhere in the depths of Windows 7 (and this applied on Server 2008 R2 as well) I still had permission in my active session context.  When I logged out, that login context died, and, coming back the next day, I logged in again, not realizing I wouldn't be able to access the key.  Giving myself explicit permission as shown below allowed me to run my SSIS package within Visual Studio and from SSMS.
(Sorry, Blogger's not letting me include this bigger... click it for full size view.)


Brian D said...

Jim, Thanks for posting this. Connecting with SSIS to web services using secure certificates is completely undocumented, anywhere. Even the WROX SSIS Pro book says to check Books Online, which is a dead end. So kudos to you. I'm working on trying to get a web service connected using a certificate now, and still waiting for my Technet subscription to go through. Yours is the only one of two articles that I've found treat this topic.

Now, my question to you is, how did you get the namespace reference in your script component?

You know, when one tries to Add Web Reference, the dialog that pops up requires calling the service in order to instantiate the namespace object in the project. Then you go to the object to find the GUID. Anyway, that's how I learned.

The problem is that one can't actually get the service to come up without authenticating using a certificate.

To be clear, I can call this service from a browser after importing the certificate. The web methods have a separate login/pwd that are required - so that could be the hangup.

Any ideas? Am I right that this bit of information about how to get the namespace GUID is critical?


Jim Wilcox said...

Hi, Brian,
Unfortunately, it's been a while, but as I recall security was set up on the service metadata exchange (MEX). The MEX didn't didn't require the certificate to add the web reference. If that's not possible for you, see if you can get the service running in a different environment that doesn't have the same security issues, and then change the URL.

sandeep saxena said...

I love this!!The blog is very nice to me. Im always keeping this idea in mind. I appreciate for your help once again.
JMeter Training in Chennai
JMeter Certification
Appium Training in Chennai
Appium Training
javascript training in chennai
core java training in chennai
C C++ Training in Chennai

Imessageforpcz said...

Latest version of how to download imessage on pc

Anbarasan14 said...

Excellent blog!!! I got to know the more useful information by reading your blog. Thanks for posting this blog.

Spoken English Classes in Chennai Anna Nagar
Spoken English Class in Porur
Spoken English Class in Vadapalani
Spoken English Class in Thiruvanmiyur
Spoken English Class in Chennai
Best English Speaking Classes in Mumbai
English Speaking Course in Mumbai
IELTS Training in Chennai
IELTS Coaching in Chennai
IELTS Mumbai

Riya Raj said...

Extraordinary Blog.... It looks more creative... Thanks for sharing wit us...
Digital Marketing Course in Chennai
Digital Marketing Course
digital marketing institute in chennai
Digital Marketing Training in Chennai
Digital marketing course in Tnagar
Digital marketing course in Thiruvanmiyur
Big data training in chennai
Software testing training in chennai
Selenium Training in Chennai
JAVA Training in Chennai

anushya said...

It’s a classic great for me to go to this blog site, it offers more helpful and wonderful suggestions.
Java Training in Bangalore
Java Training Institutes in Bangalore
Best Java Institute in Bangalore
Hadoop Training in Bangalore
Ethical Hacking Course in Bangalore
Selenium Training in Bangalore
Data Science Training in Bangalore
German Language Course in Bangalore
Best AWS Training in Bangalore

ankit said...

I love the article. You may like imessage online

Chris Hemsworth said...

The article is so informative. This is more helpful. v

Learn best software testing online certification course class in chennai with placement
Best selenium testing online course training in chennai
Best online software testing training course institute in chennai with placement
Magento 2 Developer course training institute in chennai

divi said...

Very nice article it's very useful information thanks for it web design company in velachery

for ict 99 said...

Great Article
Cyber Security Projects for CSE Students
Project Centers in Chennai

JavaScript Training in Chennai
JavaScript Training in Chennai

priya said...

I have been reading for the past two days about your blogs and topics, still on fetching! Wondering about your words on each line was massively effective.
php online training in chennai
php programming center in chennai
php class in chennnai
php certification course
php developer training institution chennai
php training in chennnai
php mysql course in chennai
php institute in chennnai
php course in chennnai
php training with placement in chennnai
php developer course

priya said...

Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
appium online training
appium training centres in chennai
best appium training institute in chennnai
apppium course
mobile appium in chennnai
mobile training in chennnai
appium training institute in chennnai

w3webschool said...

I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision.keep blogging.
Digital Marketing Course In Kolkata

sasi said...

The blog you shared is very good. I expect more information from you like this blog. Thank you.
Web Designing Course in chennai
Web Designing Course in bangalore
web designing course in coimbatore
web designing training in bangalore
web designing course in madurai
Web Development courses in bangalore
Web development training in bangalore
Salesforce training in bangalore
Python training in Bangalore
Web Designing Course in bangalore with placement

subha said...

Very good blog, this blog shares lot of new informations.
PHP Training in Bangalore
PHP Training in Chennai
PHP Classes in Bangalore
Best PHP Training Institute in Bangalore
PHP Course in Bangalore
AWS Training in Bangalore
Data Science Courses in Bangalore
DevOps Training in Bangalore
Spoken English Classes in Bangalore
DOT NET Training in Bangalore

divya said...

We make sure your calls do not get bounced. Should your calls are failing to interact with us at QuickBooks Tech Support Number, then you can certainly also join our team by dropping a message without feeling shy. Our customer care support will continue to be available even at the wee hours. share more
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai

siva said...

Your blog inspired me to learn more and more about this blog, waiting for your new blog.
need of cloud computing
advantages of deep learning
who can learn devops
difference between asp and php
javascript basic interview questions
javascript coding questions and answers