Enable SSL encryption for SQL Server instance

In this article we will try to use certificate to encrypt client connection for sql server via secure socket layer (SSL).

If we are doing this on domain joined machine we need below configuration:
1. One domain controller machine (dc.sqllab.local). Install Certificate authority with Web enrollment and online responder.
2. Second domain joined machine. (be.sqllab.local). Install SQL on this machine. (Default & named instance).

Request Certificate:

1. On client machine start MMC and go to certificates. (File > add/remove snap in > computer account > local computer > finish).
2. In personal certificates folder right click > Advanced operations > create custom request.

Enable SSL encryption for SQL Server instance

3. Click Next to wizard > select Web server > next > properties > choose type name common name and value must be FQDN of the client machine.

Enable SSL encryption for SQL Server instance

Enable SSL encryption for SQL Server instance2

4. Save the request at some location either in .txt format or .req format.

Authenticate Certificate (Online):

1. Open the requested file in notepad and copy the text.
2. Browse the cert authority URL to client machine. (either by authority name or by IP address)
3. Request a certificate > advanced certificate request > submit a certificate request by…..
4. Paste the text copied from cert req file > choose user template to web server.
5. Once clicked to submit download the certificate from the home page of cert page. (download a CA certificate chain).
6. Open the certificate. Verify the installation path if it contain the Root certificate authority.
7. Once again go to mmc and personal certificates > right click > all tasks > import > choose the certificate that we had download from the browser.
8. Once it imported successfully > select the certificate > right click > export.
9. Run the export wizard successfully.

Force Encryption for SQL Server:

1. Open SQL server configuration manger.
2. Protocol > properties > force the encryption > certificate Tab > choose certificate > OK
3. Restart the services.

Troubleshooting:

Error Message:
2005-10-13 06:30:58.22 Server TDSSNIClient initialization failed with error 0x7e, status code 0x60.
2005-10-13 06:30:58.22 Server Error: 17182, Severity: 16, State: 1.
2005-10-13 06:30:58.22 Server TDSSNIClient initialization failed with error 0x7e, status code 0x1.
2005-10-13 06:30:58.22 Server Error: 17826, Severity: 18, State: 3.
2005-10-13 06:30:58.22 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.
2005-10-13 06:30:58.22 Server Error: 17120, Severity: 16, State: 1.
2005-10-13 06:30:58.22 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.

1. Check if the SQL services are running with local computer machine.
2. If it’s running with domain account then certificate must be into domain account into MMC.
3. Check if the certificate is valid.

  1. Open the certificate > details tab > subject name must server name.
  2. Certificate path > check if the cert has root certificate authority.
  3. Check thumbprint has no special character. Go to below location.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

Export the key and open it with notepad. If it contains certificate= “?‎de 5f 29 07 10 c4 fa f6 df c8 a4 0e 3d 51 89 2d 2c 81 c6 8c”. This means the certificate contains special characters.
We can edit the thumbprint value at the same location into regedit.(please take backup of registry before making any changes)

4. Via should be disabled.

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools
%d bloggers like this: