Oct
25
2012

Consuming a secure web service in SSIS

If you need to use SSIS to consume methods of a web service that require a client certificate, the first thing you need to know is this: the Web Service Task will not get you there. (Regardless of its... other issues.)

The Properties GUI is misleading in that you can specify a certificate to test the connection and evaluate the methods, but that's as far as it goes -- the certificate information isn't passed along to the underlying HTTP Connection Manager at runtime, and you end up with "403 forbidden" errors for no apparent reason.

The HTTP Connection Manager does have a very tantalizing Certificate property... which can't be set using an Expression. (Or at least I haven't figured out how.)

We would have to resort to using a Script Task (or Script Component) to set the Certificate property, but going that route, it's actually easier to take a different approach entirely within the task.

First, though, let's take a step back, because we still need a way to get the certificate so it can be used with the secure service.

Below is a Script Task function that will return a reference to a certificate based on a certificate store location and a certificate serial number (run certmgr.msc to view the local certificate store).

Note that this is not a complete solution! You'll probably want to keep this code in its own script task which sets a package variable, so the certificate is available for all the web service calls you need to make. Also, it would be a good idea to externalize the input parameters so your package is configurable. I'm showing it this way here for simplicity.

 

using System.Security.Cryptography.X509Certificates;

private X509Certificate2 _GetClientCertificate(
	StoreLocation storeLocation, string serialNumber)
{
	X509Store store = new X509Store(storeLocation);
            
	store.Open(OpenFlags.ReadOnly);

	try
	{
		foreach (X509Certificate2 cert in store.Certificates)
		{
			if (cert.SerialNumber.CompareTo(serialNumber) == 0)
				return cert;
		}

		// No match
		throw new ArgumentException();
	}
	finally
	{
		store.Close();
	}
}

 

The next step is to configure a Script Task to actually call the web service. First, create the new task or component and go into the Visual Studio code editor. Right-click on the project file, and use Add Web Reference to generate proxy classes for your web service.

Now, here is where I've had a bit of frustration. Sometimes exiting out of Visual Studio at this point does not correctly save the project file, and you end up with the web reference files in the file system, but not actually in the project. There's no way to "add" them back to the project the way they were. Sadly, the easiest way I've found to clean it up... is to start again with a new Script Task. So what I've tried to do is use the Save All function to basically hope and pray that it sticks, then exit out, and go back in to make sure the folder still appears in the project. If it's still there, we're good to proceed.

At this point, try to build the project by using the Build | Build st_<guid> menu item. If you get an error "Task failed because "sgen.exe" was not found, or the correct Microsoft Windows SDK is not installed. ..." open the project properties, go into the Build tab, and change the Generate serialization assembly option to Off. The project should build successfully now.

So after all this leadup, here is the code to actually consume the web service in the Script Task (or Script Component):

using Services = st_<guid>.csproj.MyService;

Services.MyService svc = new Services.MyService();
svc.Url = "https://www.mydomain.com/MyService.svc";
svc.ClientCertificates.Add(
	_GetClientCertificate(StoreLocation.CurrentUser, "123456"));

svc.MyServiceMethod();

 

If you need to make many calls to the same web service, it's possible to add a reference to an external assembly in the Script Task project file, instead of generating the proxy classes directly inside the project. While the steps needed to do this are beyond the scope of this post, a common assembly is a great way to centralize the logic and service references in a larger project.

Comments (5) -

Brian D

Jon,

This is extremely helpful, as I have only found one other discussion on how to use certificates to connect SSIS as a client to web services.

I don't understand how to "instantiate" (if that's the right term) the web service reference -- i.e., the only way that I've seen for creating a web reference with a GUID is through the Add Web Reference dialog, but that will fail without the certificate authenticating the service. It's a catch 22.

You kind of noted this in code, but didn't detail how to get the GUID.

using Services = st_<guid>.csproj.MyService;

My scenario is a bit more complicated, because the web methods themselves require an additional user login to execute, and I haven't seen that work in a browser yet (user account set up issues, I suspect, but could be wrong). The result is that I don't know for sure if the Add Web Reference dialog fails because the authentication isn't working on the web service side, or if the Add Web Reference dialog is inadequate for the task (maybe both).

I'd appreciate a little help. If you like, I'll post a response with my code and explanation once I get the thing working.

In the meantime, I'm waiting for my Technet subscription to activate.

Jon Seigel

The GUID is from the containing Script Task (or Script Component) -- it's also part of the project name. It should be the default namespace in the project. The using statement requires a fully-qualified namespace, so it has to be in there.

Depending on the web service, yes, you may need to provide the certificate credentials to successfully complete the Add Web Reference step.

In a development environment, it doesn't matter if the service is secured or not. You just need enough permissions (possibly a certificate depending on your setup) for Visual Studio to be able to record the web service metadata (methods, parameters, etc.). If you authenticate to the service using a method, the other service methods should still be visible in the metadata.

If you specify a certificate in Add Web Reference, all it's used for is grabbing the metadata. That information is thrown away once the reference is added (it doesn't apply at runtime) -- this is why you have to manually assign the security stuff yourself in code, and also why the Web Service Task doesn't work with certificates.

Brian D

Jon,

I really appreciate you taking time out of your busy schedule to respond.

I was having difficulty last week getting an HTTP connection set up because the connection manager add certificate dialog wasn't finding a certificate. This issue was overcome by placing the certificate in the certmgr personal store. That may not be a long-term solution. Anyway, having not been able to add the connection using the dialog, I couldn't generate a namespace with a GUID.

Anyway, I now have other issues to resolve, and keep going deeper into WCF than I really care to. I'm having issues with bindings and user authentication that I have to resolve before returning to certificates - well beyond the scope of this discussion thread.

I would note one odd thing perhaps worth watching for among subsequent readers. While troubleshooting, I followed your example above to enumerate all certificates in the X509 store. I discovered that the stored certificate serial number was returned as a concatenated string with all upper case characters, leading to failed attempts to locate it.

In the end, I used a couple of string functions to match the sought-after serial number to the serial number for the certificate, like so:

        static void Main(string[] args)
        {
            X509Store store = new X509Store(StoreLocation.CurrentUser);
            store.Open(OpenFlags.ReadOnly);
            string serialNumber = "38 72 eg c9 pk er 45 e3 23 6t 98 ed we e4 s8 qa";

            Console.WriteLine(serialNumber);
            serialNumber = serialNumber.Replace(" ", "").ToUpper();
            Console.WriteLine(serialNumber);

            try
            {
                foreach (X509Certificate2 cert in store.Certificates)
                {
                    Console.WriteLine(serialNumber);
                    Console.WriteLine(cert.SerialNumber.Replace(" ", ""));
                    if (cert.SerialNumber.Replace(" ", "").CompareTo(serialNumber) == 0)
                        //return cert;
                        Console.WriteLine("Found: " + cert.FriendlyName);
                        //Console.ReadLine();
                }

            //    throw new ArgumentException();

            }

            finally
            {
                store.Close();
            }
        }

Jon Seigel

Placing your certificate in the personal store is fine for development; in a deployment scenario I like to be able to handle placing it in an arbitrary location without changing any code, which is why I externalized that parameter in the code in the post. So I might save the store location and the serial number in the database or a configuration file, and then pass them into that function to go find the actual certificate class instance.

I don't understand how you "don't have a GUID"? When you edit the code of a Script Task (or Script Component), SSIS auto-generates a project that contains the GUID of that component. You should be able to see it in the project tree view, or in the project properties area. It should also come up in IntelliSense when typing the using statement.

I wrote the serial number comparison that way because for something like that, I generally assume case-sensitivity/highest strictness for it to work, and also it keeps the code sample really simple and clean. I never claimed to present a complete solution. Smile If you want to relax things a bit, I don't see a big issue with that. Certainly if your application exposes a list of certificates for the user to select instead of needing to type in the serial number manually, that can help cut down on mismatches quite a bit as you can use whatever internal formatting you want.

hnash20

Amazing! Its truly awesome piece of writing, I have got much clear idea regarding from this paragraph.

Comments are closed