SSIS Fails to Start After SP1

I installed SP1 (of SQL 2005) on my "play/test" SQL box this week only to have SSIS refuse to start again afterwards. No clues in the event log. The SP1 installation log files weren't much help (and take a fair bit of time to go through). I was affected by what seemed an obscure situation at the time but the more I think about it the more common it seems.

The deal is this. When you start SSIS (after installing SP1), CryptoAPI, on behalf of the SSIS service, attempts to go out to the internet to check a certificate revocation list for one of the assemblies (to validate the signature). This seems reasonable enough and for a normal domain user it would probably work. However, SSIS often runs under the local NETWORK SERVICE account, which would most likely not be able to access the internet through whatever proxies and/or firewalls have been established for the corporate network.

The two ways to get around this are to 1) run SSIS under an account that can access the internet, or 2) turn off the certificate revocation checking. Option 1 is straight forward. Option 2 is simple enough for a proper user (Control Panel | Internet Options | Advanced | Security | Check for publisher's revocation certificate). But NETWORK SERVICE is not a normal user; you cannot log on interactively as NETWORK SERVICE and fiddle with its control panel settings. But you can hack the registry (don't you just love living on the edge and hacking the registry?).

That Internet Options setting is stored in the State DWORD value in the registry under the

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\
WinTrust\Trust Providers\Software Publishing

key. The HKEY_USERS branch for the NETWORK SERVICE account is S-1-5-20. So you can access that setting for the NETWORK SERVICE account in

HKEY_USERS\S-1-5-20\Software\Microsoft\Windows\CurrentVersion\
WinTrust\Trust Providers\Software Publishing

Just change your current user value with control panel and then copy the State value from your HKCU registry key to the equivalent KHEY_USERS\S-1-5-20 registry key.

The next time you try starting SSIS, if you've turned off certificate revocation checking with the above reg hack, it will ignore the crypto internet access bit and just start (assuming something else isn't broken as well).

I initially read this fix in a newsgroup thread and later found the same thing documented in Jamie Thomson's blog SSIS: Another SP1 Problem but I still thought it worthwhile to write it up again briefly here as I expect this will be a fairly common issue for many people. I'm very grateful to Michael Entin who figured this out and posted the solution because I would never have figured that one out for myself!

And now Microsoft have a KB article on this issue at:
http://support.microsoft.com/kb/918644