"3.  Add the SQL service account into the local administrator group of both cluster nodes"

-- Why ON EARTH is this step necessary?  Possibly the single most beautiful feature of SQL2005 over previous versions of the product is how well the installer doles out just enough account priveleges to the service accounts to let the sw run, without requiring poor operational practices just like this one.  For many environments (especially ones concerned enough with security to bother implementing SSL) putting the SQL acct in the admin group is a major no-no.  I'm trying to enable ssl encryption on multiple (non-clustered) servers.  I have a 'Server Authentication' certificate installed on each server within the 'Personal' cert store of the service acct. that SQL Server runs as.  Any time I attempt to start SQL with that account *not* in the local admin group, I get the following:

2007-06-21 14:24:23.93 Server      The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030e. Check certificates to make sure they are valid.

2007-06-21 14:24:23.93 Server      Error: 26014, Severity: 16, State: 1.

2007-06-21 14:24:23.93 Server      Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

2007-06-21 14:24:23.93 Server      Error: 17182, Severity: 16, State: 1.

2007-06-21 14:24:23.93 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80.

2007-06-21 14:24:23.95 Server      Error: 17182, Severity: 16, State: 1.

2007-06-21 14:24:23.95 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

2007-06-21 14:24:23.95 Server      Error: 17826, Severity: 18, State: 3.

2007-06-21 14:24:23.95 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2007-06-21 14:24:23.95 Server      Error: 17120, Severity: 16, State: 1.

2007-06-21 14:24:23.95 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

C'mon man... there's gotta be a way to enable SSL encryption without making SQL Server root on every box in my environment.  What have ya got for me?

Found the answer:

http://blogs.technet.com/mscom/archive/2007/05/30/how-to-get-sql-to-accept-the-cert-or-a-day-or-two-in-the-life-of-an-mscom-debug-engineer-part-2.aspx

use winhttpcertcfg.exe to grant the sql service acct access to the local_system cert store without being a local admin.

Hi Mohamed,

Thanks for such a nice blog, it helps a lot. One more thing if you can mention, we have certificates installed from private authority on the database server but still connections from clients where those private certificates are not installed gets connected to SQL Server, it should be refused as per my understanding when we have third party certificates are installed on the database server, isn't it? So, if you can put some light on it, how to refuse connections from clients where certificates are not installed?

I guess those clients get connected using self signed certificates, if its true then we want to stop using self signed certificates.

Thanks a lot for your time in advance.