Enabling SSL in SQL 2005 cluster

re: Enabling SSL in SQL 2005 cluster

j_babaria — Tue, 27 Dec 2011 16:38:59 GMT

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.

re: Enabling SSL in SQL 2005 cluster

Mohamed Baher [MSFT] — Fri, 22 Jun 2007 10:50:02 GMT

Hi Dan,

Thanks for posting the URL for the "winhttpcertcfg" workaround, i wish that it was posted before December timeframe where i was in bad need for it:)

Thanks again for adding this info to the post,

re: Enabling SSL in SQL 2005 cluster -- no localadmin required

Dan P — Fri, 22 Jun 2007 02:54:47 GMT

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.

re: Enabling SSL in SQL 2005 cluster

Dan P — Fri, 22 Jun 2007 00:38:49 GMT

"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?

Microsoft » Blog Archives » … the internal network adapter to let the

Microsoft » Blog Archives » … the internal network adapter to let the — Wed, 02 May 2007 13:07:53 GMT

PingBack from http://chaespot.com/mssql/2007/05/02/the-internal-network-adapter-to-let-the-2/