Voluntary DBA

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.