How do you enable SSL encryption on SQL Server for secure communication. 
Author Message
 How do you enable SSL encryption on SQL Server for secure communication.
We are attempting to enable SSL encryption for
communicating between two servers.

I am used to working with encryption in SFTP applications
where the certificates to be used are selected from within
the application itself. It is not evident to me in SQL
server where you specify which certificate to use when
enabling SSL communication. Any help would be appreciated,

Sat, 29 May 2004 06:09:36 GMT
 How do you enable SSL encryption on SQL Server for secure communication.

Only for reference.

Section I: Steps to enable encryption on the server using a Certificate Server.

1. Use your Internet browser to connect to the Certificate Server.

2. Select: "Request a certificate". Press Next.

3. Request Type: Select "Advanced request". Press Next.

4. Select "Submit a certificate request to this CA using a form" Press Next.

5. Enter the Fully Qualified Domain Name of your computer in the Name:textbox. Ping your machine to
get this if you're unsure.

6. Leave all other items as the default. Press Submit.

7. The last page will present you with the Certificate to Install. Click install this certificate.

Next, verify that the certificate was installed correctly.

1. Right click on your Internet Browser on your desktop.

2. Select Properties.

3. Select the Content page.

4. Press Certificates.

5. Select the Personal tab.

6. Verify the Issued to: is the Fully qualified machine domain name.

7. Verify the Certificate intended purposes is "Client Authentication. This text
   will appear under the Import and Export buttons.

8. Select the certificate and Press View.

9. The certificate is intended to: Proves your identity to a remote computer. It
   should indicate that you have a private key that corresponds to this

10. Select the Certificate Path tab. Verify the Certificate status is OK

Steps to enable encryption on the SQL Server now that the certificate is installed.

1. Next, use the SQL Server Network Utility and check the Force Protocol
   Encryption Option.

2. Stop and Restart the MSSQLServer service for the default instance or Named

3. Verify that the server did NOT report an error by reviewing the SQL Server

Steps to enable SSL encryption on a per client machine basis, instead of globally
on the server.

Enabling SSL encryption on a per client basis requires that the client machine
must Trust the Server certificate. The certificate must already exist on the
server. The client machine does not require a certificate, but it must have the
server certificate as a Trusted Root Certificate Authority. Use the following
steps to enable SSL encryption on a per client basis.

1. Make sure that you have disabled or Unchecked the option in the SQL Server
   Network Utility. Uncheck the Force Protocol encryption Option.

2. Make a test connection from a client machine using Network Monitor or a
   Network Sniffer tool to verify that the communication between a client and
   server machine is not encrypted.

3. Use IIS to create a new certificate using the certificate wizard.

4. Use Internet Services Manager. Select your default Web Site

5. Right Click, Properties.

6. Select the Directory Security Tab.

7. Press Server Certificate Button under Secure Communications to start the Web
   Certificate Wizard .

8. Select Next, Create a New Certificate, Next.

9. Select Prepare the request now, but send it later, Next.

10. Select the defaults (512 bit length)

11. Organization = First Organization, Organization Unit = First Administrative
   Group, Next.

12. Common name. Accept the default.

13. Enter you State, City. Next.

14. Enter a filename. Accept the default of certreq.txt. Next,Next, Finish.

15. Connect to your Certificate Server.

16. Request a certificate. Next.

17. Select Advanced Request. Next.

18. Select Submit a certificate request using base64 encoded PKS#10 file...

19. Submit a Saved Request: click browse for file insert, select the filename;
   c:\certreq.txt from step 14. Select Read, then Submit.

20. Certificate Issued: Select Base 64 encoded.

21. Download the CA Certificate and the CA Certificate path and save it to your
   hard drive.

22. Select your Internet browser, right click Properties, Content, Certificates.

23. Select the Trusted Root Certification Authorities tab.

24. Select Import,Next,Browse, Change Files of type to: PKCS #7

25. Select the certificate and press Open, select Next.

26. Select Automatically select the certificate store based on the type of

27. Select Next, Finish.

28. A dialog should appear stating The import was successful.

29. Verify that the certificate appears under the Trusted Root Certificate
   Authorities and the Intended Purposes indicates All.

30. Select View, to verify that there are no errors reported with the

31. Select the Certification Path tab and check the Certificate status.

Section II:Installing certificates using the MMC snap-in on the SQL Server

To open the Certificates snap-in, follow these steps:

1. Open the MMC console by selecting Start,Run and typing "mmc"

2. In the Console menu, select File,Add/Remove Snap-in...

3. Click the Add button, then select Certificates and click Add again.

4. You will now be prompted to select whether to open the snap-in for the
   current user account, the service account, or for the computer account.
   Select Computer Account

Your installed certificates will be located in the Certificates folder within the
Personal container.

Installing the Certificate on the Server:

1. Select the Personal folder in the left hand pane.

2. Right click in the right hand pane and select all tasks, request new

3. Choose certificate type is "computer".

4. After finishing the wizard, you should now see the certificate in the folder,
   with the fully qualified computer domain name.

5. Follow the steps to enable encryption on the SQL Server now that the
   certificate is installed.

Enabling encryption from the client machine.

In order for the client to request the SSL encryption, the client machine must
trust the server certificate. The certificate must exist on the server already.
You'll need to use the MMC snap-in to export the Trusted Root Certification
Authority used by the server certificate.

Steps to Export the server certificate's Trusted Root Certificate Authority.

1. Using the MMC,locate your certificate under the personal folder

2. Right Click on the certificate and choose Open

3. Review the certification path. Note the top most item.

4. Next, go to the Trusted Root Authority Folder and locate the CA noted above.

5. Right Click on the Trusted Root Authority and select All tasks, Export.

6. Select all the defaults and save the exported file to your disk, where the
   client machine can access it.

7. Go to the client machine using the MMC snap-in go to the Trusted Root
   Certification Authorities folder

8. Right Click, Select All Tasks, Import

9. Accept all the defaults

10. Use the SQL Server Client Utilities

11. Select the Force Protocol encryption

12. Test your client connection by following steps in Section III.

Section III:How to test encryption from a client machine

You can now test your client machines with two different methods.

1. Use Query Analyzer Tool.

2. Use any ODBC application where you can change the connection string used.

To test with the SQL Query Analyzer tool:

1. Use the SQL Server Client Network Utility.

2. Check the Force protocol encryption option

3. Connect to the SQL 2000 Server using Query Analyzer.

4. Monitor the communication using Microsoft Network Monitor or a Network

To test with an ODBC Application:

1. Modify the ODBC or OLEdb Connection string.

2. Driver=SQL

3. Connect to the SQL 2000 Server.

4. Monitor the communication using Microsoft Network Monitor or a Network


This posting is provided "AS IS" with no warranties, and confers no rights.


Sun, 30 May 2004 14:50:54 GMT
 [ 2 post ] 

 Relevant Pages 

1. Does SSL encryption enabled from the client work?

2. Does SSL encryption enabled from the client work?

3. Enabl SSL Encryption for SQL Server 2K

4. Enable SSL Encryptionn for SQL Server 2K with MMC

5. Enable SSL Encryptionn for SQL Server 2K

6. SQL and CSR's to enable SSL

7. Q: Progress Client / Server communication + Encryption ?

8. Q: SQL*Net Encryption/Secure Network Services

9. SQL*NET - is password encryption default or does it need to be enabled

10. Access Encryption vs SQL Server encryption

11. Client Force Encryption SSL Security error

12. Urgent : Merge replication and SSL encryption

Powered by phpBB® Forum Software