How To: Setup A Windows 2003 and SQL Server 2005 Veritas Cluster with a Domain CA-Issued Certificate

One of the potentially most annoying things I've ever had to do with SQL 2005 is setup an instance to use a CA (certificate authority) issued certificate for SSL encryption. SQL 2005 will always encrypt the login traffic, but it'll use a self-signed certificate and you won't be able to "force encryption" without some other certificate being loaded. Since I think 99% of serious SQL installs would be part of a domain the instructions below assume that to be the case.
Here's the imaginary setup that'll be referenced in the steps below:

1. Windows 2003 x64 R2 SP2 (should be identical for 32bit)
2. SQL 2005 x64 (Std or Enterprise) SP2 Cumulative Update 9
3. Veritas Storage Foundations HA 4.3 MP2 is used for clustering
   
4. Server is part of a Windows 2003 Domain
5. The domain has a properly configured CA and certificate requests are approved automatically. CA is CA01.drew.net
   
6. Domain name is drew.net (drewnet)
   
7. SQL Server service account is a domain account: drewnet\sqlAdminX
8. SQL server cluster node hostnames are SQL1.drew.net and SQL2.drew.net
9. SQL server cluster virtual name is SQLCluster.drew.net
10. Domain account for Veritas Cluster Service is drewnet\sqlCsvc
    
11. drewnet\sqlAdminX is granted NOT an admin on any of the cluster nodes
12. You are an admin on all cluster nodes
13. SQL has been installed identically on both nodes and the Veritas cluster is working 100% (just without SSL certs for encrypted traffic)
14. Only one instance of SQL is installed and it is a default installation on port 1433
    

Ok, now that's out of the way let's get down to business...

• Connect to SQL1.drew.net
  
• Connect to the CA request page at http://CA01.drew.net/certsrv
• Follow the path to make a custom advanced certificate request. See here for more info.
• Request a "Web Server" certificate with a name field of SQLCluster.drew.net, ensure the private key is exportable, and store the certificate in the local computer store.
• Start->Run: mmc
• Add the Certificate snap-in for the local computer and verify there is a trusted certificate named SQLCluster.drew.net there that has a details tab that indicates "Server Authentication (1.3.6.1.5.5.7.3.1)" in the "Enhanced Usage" section.
• Scroll to the bottom of the details and highlight all the text in the "Thumbprint" section. Copy this (ctrl-c).
  
• Start->Run: Notepad
• Paste the data you just copied and remove all the spaces. Save this file on your desktop.
• Open the Veritas Cluster Manager GUI and connect to the cluster.
• Freeze the SQL service group you're working on (temporary)
• Download the WinHttpCertCfg.exe utility from Microsoft if you don't already have it.
• Install the utility, open a command prompt and navigate to your installation path.
• Run the following command:
  winhttpcertcfg.exe -g -c LOCAL_MACHINE\MY -s "SQLCluster.drew.net" -a "drewnet\sqlAdminX"
• If you got a success message, move on. Otherwise troubleshoot!
• Start->Run: regedit
• Navigate to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib and open the "Certificate" entry.
• Copy & Paste the thumbprint value you saved earlier.
• Restart MS SQL Server any way you see fit.
• If your server starts properly that's a very good sign. Connect to SQL and view the current log for the line "The certificate was loaded successfully"- You've done it!!
• You can now configure the various options like "force encryption" via SQL Server Configuration Manager if you want now.

But that's not all! There's another cluster node to configure!

• In the certificates snap in right-click the SQLCluster.drew.net certificate and choose export.
• Follow the wizard- be sure to export the private key and assign a password you can remember for a while. Save the .pfx file somewhere easy to get to.
• Connect to SQL2.drew.net
• Copy the exported .pfx file locally
• Start->Run: mmc
• Add the local computer certificate snap-in again.
• Double-click the certificate copied locally, then choose "Install certificate"
• Enter the password you just used when exporting the key.
• Verify that the certificate shows up in the personal folder of the local system and that it's valid.
• Again, run the winhttpcertcfg command from above on this node.
• Back on SQL1.drew.net in the Veritas Cluster Manager GUI Unfreeze the SQL service group.
• "Switch" the service group from SQL1.drew.net to SQL2.drew.net
• If it starts on the other node you're done! VCS has replicated the registry change you made and the certificate was loaded successfully.
• Delete the exported .pfx file and the copy you made.

That's it. There's obviously a lot to modify if you've got more than one node and/or more than one SQL instace (service group) in the cluster but these steps will work 100%. If you're having problems by all means leave a comment and I'll post a response. This was a very tricky thing to figure out how to do as the only thing the MS documentation tells you is "if SQL is configred as a failover cluster use the FQDN of the failover cluster for the certificate name" and "SQL server will attempt to locate a certificate based on the FQDN of the host". Those two things really screw you if you're trying to load your cert via the SQL Configuration Manager tool!