Quick guide to enable SSL on SQL Server 2008

 Create certificates
1. To open the MMC console, click Start, and then click Run. In the Run dialog box type: MMC
2. On the Console menu, click Add/Remove Snap-in....
3. Select Certificates snap-in. Click Add.
clip_image002
4. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
clip_image004
5. Select Local computer, and then click Finish.
clip_image006
6. Click Close in the Add Standalone Snap-in dialog box.
7. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container
8. Right click personal -> All tasks -> Request new certificate
9. On Active directory enrollment policy panel select Computer -> Enroll
10. New certificate is created in personal\certificates
11. Give the SQL Server service account access to the certificate.Right click the certificate All tasks –> Manage Private Keys. Select the SQL service account and give Full Control.clip_image008

Configure SQL Server to use the SSL certificate

1. Sql server configuration manager -> sql server network configuration -> protocols for mssqlserver -> right click -> properties
o Force encryption “Yes”
o Certificate -> select newly created certificate
2. Right click SQL native client 10.0 configuration -> Properties
o Select Yes for Force Protocol Encryption
o Select No Trust Server Certificate
3. Restart mssqlserver service
Errors

I was unable to start the SQL server and found this error in the ERROR log. 

Server 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. 
Server Error: 26014, Severity: 16, State: 1. 
Server Unable to load user-specified certificate [Cert Hash(sha1) "XXXXX"]. 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. 
Server Error: 17182, Severity: 16, State: 1. 
Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

Server Error: 17182, Severity: 16, State: 1. 
Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 
Server Error: 17826, Severity: 18, State: 3. 
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. 
Server Error: 17120, Severity: 16, State: 1.

The reason for this error was I forgot to give the SQL Server service account access to the certificate as mentioned in step 11.

Etiketter: