Did you tried to enable SQL encryption between the client and your SQL instance? I thought that configuring SQL 2005 cluster to encrypt its traffic is simple however I discovered that it is not that straight forward and you always get this error “The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid” to understand more about SSL in SQL 2005 & how to configure it read below.
Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients except DB Library and MDAC 2.53 clients.
SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server. Encryption with a self-signed certificate is possible as the Credentials (in the login packet) that are transmitted when a client application connects to SQL Server 2005 are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide authentication or nonrepudiation. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate. Note that SSL connections that are encrypted by using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.
Note: Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance. When all traffic between SQL Server and a client application is encrypted using SSL, the following additional processing is required:
To configure SSL encryption to work with a certificate from a public certification authority follow the below steps:
Note that after doing all of the above steps you will notice that the certificates is not listed in the SQL Server configuration manager - > Protocols for the instance. However the SSL is working & you can check by looking into the SQL logs. To get to know that your certificate loaded successfully try to search SQL Server Error log (in SSMS) for
Message contains: certificate
Note: When Microsoft SQL Server 2005 is running under the Network Service account, you cannot enable encryption by using a certificate. If you provision a certificate for use in encryption, SQL Server will not start. Additionally, you may notice an error message in the SQL Server error log. To solve this problem compile the code in KB 900495 http://support.microsoft.com/?kbid=900495
PingBack from http://chaespot.com/mssql/2007/05/02/the-internal-network-adapter-to-let-the-2/
"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:
use winhttpcertcfg.exe to grant the sql service acct access to the local_system cert store without being a local admin.
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,
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.