SQL: How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console

http://support.microsoft.com/kb/316898

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console

This step-by-step article describes how to install a certificate on a computer that is running Microsoft SQL Server 2000 or Microsoft SQL Server 2005 by using Microsoft Management Console (MMC) and describes how to enable SSL Encryption at the server, or for specific clients.

Note You cannot use this method to put a certificate on a SQL Server clustered server.

If your company has implemented an Enterprise Certificate Authority, you can request certificates for a SQL Server stand-alone server, and then use the certificate for Secure Sockets Layer (SSL) encryption.

You can enable the Force Protocol Encryption option on the server, or on the client.

Note Do not enable the Force Protocol Encryption option on both the client and the server. To enable Force Protocol Encryption on the server, use the Server Network Utility. To enable Force Protocol Encryption on the client, use the Client Network Utility.

Important If you enable SSL encryption by using the Client Network Utility, then all connections from that client will request SSL encryption to any SQL Server to which that client connects.

Warning If you enable Force Protocol Encryption on the client computer, you cannot connect to previous versions of SQL Server from that specific client. Previous versions of SQL Server do not recognize SSL encryption.

If you enable Force Protocol Encryption on the server, you must install a certificate on the server.

If you want to enable Force Protocol Encryption on the client, you must have a certificate on the server and the client must have the Trusted Root Authority updated to trust the server certificate.

Note If you are using SQL Server 2005 to enable encrypted connections for an instance of SQL Server 2005, you can set the value of the ForceEncryption option to Yes. For more information, see the "How to: Enable Encryption Connections to the Database Engine (SQL Server Configuration Manager)" topic in SQL Server 2005.

Back to the top

Install a certificate on a server with Microsoft Management Console (MMC)

To use SSL encryption, you must install a certificate on the server. Follow these steps to install the certificate by using the Microsoft Management Console (MMC) snap-in.

How to Configure the MMC Snap-in

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

a. To open the MMC console, click Start, and then click Run. In the Run dialog box type:

MMC

b. On the Console menu, click Add/Remove Snap-in….

c. Click Add, and then click Certificates. Click Add again.

d. 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.

e. Select Local computer, and then click Finish.

f. Click Close in the Add Standalone Snap-in dialog box.

g. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.

2. Use the MMC snap-in to install the certificate on the server:

. Click to select the Personal folder in the left-hand pane.

a. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate….

b. The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".

c. In the Friendly Name text box you can type a friendly name for the certificate or leave the text box blank, and then complete the wizard. After the wizard finishes, you will see the certificate in the folder with the fully qualified computer domain name.

d. If you want to enable encryption for a specific client or clients, skip this step and proceed to the Enable encryption for a specific client section of this article.

If you want to enable encryption at the server, open the Server Network Utility on the server where the certificate is installed, and then click to select the Force protocol encryption check box. Restart the MSSQLServer (SQL Server) service for the encryption to take effect. Your server is now ready to use SSL encryption.

Back to the top

Enable encryption for a specific client

For the client to request the SSL encryption, the client computer must trust the server certificate and the certificate must already exist on the server. You have to use the MMC snap-in to export the Trusted Root Certification Authority used by the server certificate:

1. To export the server certificate’s Trusted Root Certificate Authority (CA), follow these steps:

a. Open MMC, and then locate your certificate in the Personal folder.

b. Right-click the certificate name, and then click Open.

c. Review the Certification Path tab. Note the top most item.

d. Navigate to the Trusted Root Certification Authorities folder, and then locate the Certificate Authority noted in step c..

e. Right-click CA, point to All Tasks, and then click Export.

f. Select all the defaults, and then save the exported file to your disk where the client computer can access the file.

2. Follow these steps to import the certificate on the client computer:

. Navigate to the client computer by using the MMC snap-in, and then browse to the Trusted Root Certification Authorities folder.

a. Right-click the Trusted Root Certification Authorities folder, point to All Tasks, and then click Import.

b. Browse, and then select the certificate (.cer file) that you generated in step 1. Select the defaults to complete the remaining part of the wizard.

c. Use the SQL Server Client Network Utility.

d. Click to select the Force Protocol encryption option. Your client is now ready to use SSL encryption.

Back to the top

How to test your client connection

To test your client connection you can either:

  • Use the Query Analyzer Tool.

    -or-

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

Query Analyzer Tool

To use the Query Analyzer Tool, follow these steps:

1. Use the SQL Server Client Network Utility.

2. Click to select the Force protocol encryption option.

3. Connect to the server that is running SQL Server 2000 by using Query Analyzer.

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

ODBC or OLEDB Application Sample Connection Strings

If you use ODBC or OLEDB connection strings, follow these steps:

1. Modify the ODBC or OLEDB connection string. For example:

ODBC

Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES

OLEDB

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True

2. Connect to the server that is running SQL Server 2000, and then monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

Back to the top

Troubleshooting

After you successfully install the certificate, the certificate does not appear in the Certificate list on the Certificate tab.

Note The Certificate tab is in the Protocols for <InstanceName> Properties dialog box that is opened from SQL Server Configuration Manager.

This issue occurs because you may have installed an invalid certificate. If the certificate is invalid, it will not be listed on the Certificate tab. To determine whether the certificate that you installed is valid, follow these steps:

1. Open the Certificates snap-in. To do this, see step 1 in the "How to Configure the MMC Snap-in" section.

2. In the Certificates snap-in, expand Personal, and then expand Certificates.

3. In the right pane, locate the certificate that you installed.

4. Determine whether the certificate meets the following requirements:

o In the right pane, the value in the Intended Purpose column for this certificate must be Server Authentication.

o In the right pane, the value in the Issued To column must be the server name.

5. Double-click the certificate, and then determine whether the certificate meets the following requirements:

o On the General tab, you receive the following message:

You have a private key that corresponds to this certificate.

o On the Details tab, the value for the Subject field must be server name.

o The value for the Enhanced Key Usage field must be Server Authentication (<number>).

o On the Certification Path tab, the server name must appear under Certification path.

If any one of these requirements is not met, the certificate is invalid.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s