Using SSL to Communicate with SQL Server

Understanding the risk to your network and SQL Server databases, you might decide to encrypt the connection between your SQL Server database and the clients accessing that server. It is often vital for applications to be able to secure the data passed between the SQL Server database server and the client applications and users. With SQL Server, you can use Secure Sockets Layer (SSL) to create an encrypted channel. This document is to show you how to install a certificate on the database server and configure SQL Server to use SSL.

You can use the Secure Sockets Layer (SSL) protocol to secure the communication link between clients (direct callers) and Microsoft SQL Server. When you configure SQL Server for SSL, all of the data transmitted between clients and server (and vice versa) may be encrypted to ensure that the data remains confidential while in transit between the client and SQL Server.

• Clients must have the SQL Server 2000 or later connectivity libraries installed. Earlier versions or generic libraries will not work.
• For SSL to work, you must install a server certificate on the database server computer. The client computer must also have a root certificate authority
  (CA) certificate from the same authority.
• SSL only works for TCP/IP (the recommended communication protocol for SQL Server) and named pipes.
• You can configure the server to force the use of encryption for all connections.
• On the client, you can:
  • Force the use of encryption for all outgoing connections.
  • Allow client applications to choose whether or not to use encryption on a per-connection basis, by using the connection string.

Step 1 – Request and Install a Server Authentication Certificate

This should be requested to the security administer of your domain. SSL requires that the server possess a server authentication certificate issued by a certificate authority (CA) that is trusted by connecting clients. You can read more about the requirements of certificates for SQL Server here.

To install a server certificate

1. Logon to the database server computer using an administrator account.
2. Start Internet Explorer and browse to Microsoft Certificate Services, for example: http://MyCA/certsrv
3. Click Request a certificate, and then click Next.
4. Click Advanced request, and then click Next
5. Click Submit a certificate request to this CA using a form, and then click Next.
6. Fill out the certificate request form noting the following:
   1. Enter the fully-qualified domain name of the computer running SQL Server into the Name field. For example: sql01.nwtraders.com
   2. In the Intended Purpose (or Type of Certificate Needed) field, click Server Authentication Certificate.
   3. For the Cryptographic Service Provider (CSP), click Microsoft RSA SChannel Cryptographic Provider.

Note Microsoft Base Cryptographic Provider version 1.0 and Microsoft Enhanced Cryptographic providers also work. Microsoft Strong Cryptographic Provider does not.

   4. Select the Use local machine store check box.

Note Do NOT select Enable strong private key protection.

   7. Click Submit to submit the request.

8. If the certificate server automatically issues certificates, you can install
   the certificate now. Select the certificate and then click Install this
   Certificate.

If the server does not issue certificates automatically, perform the following steps:

1. From the Administrative Tools program group, start the Certification Authority tool.
2. Expand your certificate authority, and then select the Pending Requests folder.
3. Select the certificate request you just submitted, and then on the Action menu, point to All Tasks, and then click Issue.
4. Confirm that the certificate is displayed in the Issued Certificates folder, and then double-click the certificate to view it.
5. On the Details tab, click Copy to File to save the certificate as a Base-64 encoded X.509 certificate.
6. Close the properties window for the certificate.
7. Close the Certification Authority tool.

To install the issued certificate, perform the following steps:

1. To view the certificate, start Windows Explorer, navigate to the .cer file saved in the previous procedure, and then double-click it.
2. Click Install Certificate, and then on the first page of the Certificate Import Wizard, click Next.
3. Select Automatically select the certificate store based on the type of certificate, and then click Next.
4. Click Finish to complete the wizard. Close the confirmation message box, and then click OK to close the certificate.

For testing SSL you can Self-creating a certificate:

When we don’t have a certificate, we have to create one and following steps would help in doing so. There is a utility called makecert which helps in generating/installing a certificate.

1. MakeCert is available as part of the Windows SDK, which you can download from here. Once you run the setup it will get installed in C:\Program Files\Microsoft.NET\SDK\v2.0 64bit\Bin (If it is not there, search for makecert.exe)
2. Login with SQL startup account.
3. Execute the below command :-

makecert -r -pe -n “CN=FQDN of
machine” -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr
currentuser  -sky exchange -sp “Microsoft RSA SChannel Cryptographic
Provider” -sy 12

     4. Check in cert store whether this certificate is present or not.

Please follow the steps mentioned below to verify if the certificate is present

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

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. Click Add, and then click Certificates. Click Add again.
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.
5. Select Local computer, and then click Finish.
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 then check in configuration manager.

To configure the server to accept encrypted connections:

1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then select Properties.
2. In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.

Step 2 – Verify that the Certificate Has Been Installed

This procedure verifies that the server certificate has been installed successfully.

To verify that the certificate has been installed

1. On the taskbar, click the Start button, and then click Run.
2. Enter mmc, and then click OK.
3. On the Console menu, click Add/Remove Snap-in.
4. Click Add.
5. Click Certificates, and then click Add.
6. Click Computer account, and then click Next.
7. Ensure that Local computer: (the computer this console is running on) is selected, and then click Finish
8. Click Close, and then click OK.
9. In the left-pane tree view, expand Certificates (Local Computer), expand Personal, and then select Certificates.
10. Verify that there is exactly one certificate with the fully qualified domain name that you specified in the previous procedure.

You can double-click the certificate to view its details.

Step 3 – Install the Issuing CA’s Certificate on the Client

After the certificate has been installed and the SQL Server service has been restarted, SQL Server can negotiate SSL with clients. Clients that use SSL to
connect to SQL Server must:

• Have Latest MDAC or SQL Server connectivity libraries installed.
• Trust the issuer of the SQL Server’s certificate.

To install the certificate of the issuing CA on the client computer

1. Log on to the client computer as an administrator.
2. Start Internet Explorer and browse to Microsoft Certificate Services, for example: http://MyCA/certsrv
3. Click Retrieve the CA certificate or certificate revocation list, and then click Next.
4. Click Install this CA certification path, and then click Yes in response to the confirmation dialog to install the root certificate.

Step 4 – Force All Clients to Use SSL

You can configure the server to force all clients to use SSL (as described in this procedure), or you can let clients choose whether or not to use SSL on a per-connection basis (as described in the next procedure). The advantages of configuring the server to force clients to use SSL are:

• All communications are guaranteed to be secure.
• Any unsecured connections are rejected.

The disadvantages are:

• All clients must have MDAC 2.6 or SQL Server 2000 connectivity libraries
  installed; earlier or generic libraries will fail to connect.
• Connections that you do not need to secure suffer a slight performance overhead due to
  the added encryption.

To force all clients to use SSL

1. On the computer running SQL Server, click Server Network Utility in the Microsoft SQL Server program group.
2. Click to select Force protocol encryption.
3. Verify that TCP/IP and/or named pipes are enabled.

NOTE: SSL is not supported with other protocols.

4. Click OK to close the SQL Server Network Utility, and then click OK in response to the SQL Server Network Utility message box.
5. Restart the SQL Server service.

All subsequent client connections will be required to use SSL, whether they specify secure connections or not.

Step 5 – Allow Clients to Determine Whether to Use SSL

This procedure shows you how to configure SSL to allow clients to choose whether or not to use SSL. You can either configure the client libraries to enforce the use of SSL on all connections, or you can let individual applications choose on a per-connection basis. The advantages of configuring the client are:

• The overhead of SSL is incurred only for connections that truly require it.
• Clients that do not support SSL with SQL Server can still connect.

If you adopt this approach, make sure that you are willing to allow unsecured connections.

To reconfigure the server

1. On the computer running SQL Server, run the Server Network Utility.
2. Clear the Force protocol encryption check box.
3. Restart the SQL Server service.
4. Return to the client computer.

To use SSL for all client connections

With this approach, you configure the client libraries to use SSL for all connections. This means that SQL Servers that do not support encryption and SQL
Servers earlier than SQL Server 2000 will not be accessible.

1. In the Microsoft SQL Server program group, click Client Network Utility.
2. Ensure that TCP/IP and/or named pipes are enabled.
3. Select Force protocol encryption.

To allow applications to choose whether or not to use encryption

With this approach applications use the connection string to determine whether or not to use encryption. This allows each application to only use encryption when it is needed.

1. If you are using the OLE-DB data provider to connect to SQL Server, set Use Encryption for Data to true as shown in the following sample OLE-DB connection string.
2. “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=sql01;Use Encryption for Data=True”
3. If you are using the SQL Server .NET data provider to connect to SQL Server, set Encrypt to Yes as shown in the following example.
4. “Server=sql01;Integrated Security=SSPI;Persist Security Info=False;Database=Northwind;Encrypt=Yes”

You can get additional information from Microsoft here.