SQLServerWiki

“The Only Thing That Is Constant Is Change”

How To: Use SSL to Secure Communication with SQL Server

Posted by database-wiki on September 30, 2011

Summary:

It is often vital for applications to be able to secure the data passed to and from a SQL Server database server. With SQL Server, you can use SSL to create an encrypted channel. This document is to shows you how to install a certificate on the database server, configure SQL
Server for SSL and to verify that the channel is secure.

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
client and server (and vice versa) may be encrypted to ensure that the data
remains confidential while in transit between the client and SQL Server.

  • 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.
  • Clients must have the SQL Server 2000 or later connectivity libraries installed.
    Earlier versions or generic libraries will not work.
  • 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 customer’s domain. )

SSL requires that the server possess a server authentication certificate issued by
a certificate authority (CA) that is trusted by connecting clients.

Below are the requirements of certificates for SQL Server.

http://msdn.microsoft.com/en-us/library/ms189067.aspx

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:
  3. http://MyCA/certsrv
  4. Click Request a certificate, and then click Next.
  5. Click Advanced request, and then click Next
  6. Click Submit a certificate request to this CA using a form, and then
    click Next.
  7. 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:
    2. sql01.nwtraders.com
    3. In the Intended Purpose (or Type of
      Certificate Needed
      ) field, click Server Authentication Certificate.
    4. 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.

    1. Select the Use local machine store check box.

Note Do NOT select Enable strong private key protection.

  1. Click Submit to submit the request.
  2. 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 http://go.microsoft.com/fwlink/?linkid=84091.
    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)

b.  Login with SQL startup account.

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

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:

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 then check in
configuration manager

To configure the server to accept encrypted connections:

a. In SQL Server Configuration
Manager, expand SQL Server 2005 Network Configuration, right-click Protocols
for <server instance>, and then select Properties.

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

c. On the Flags tab, in the
ForceEncryption box, select Yes, and then click OK to close the dialog box.

d. 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:
  3. http://MyCA/certsrv
  4. Click Retrieve the CA certificate or certificate revocation list, and
    then click Next.
  5. 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.

SSL is not supported with other protocols.

  1. Click OK to close the SQL Server Network Utility, and then click OK
    in response to the SQL Server Network Utility message box.
  2. 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
  3.   Info=False;Initial Catalog=Northwind;Data Source=sql01;Use
  4.   Encryption for Data=True”
  5. 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.
  6. “Server=sql01;Integrated Security=SSPI;Persist Security
  7.   Info=False;Database=Northwind;Encrypt=Yes”

Step 6.

Verify that Communication is Encrypted

In this procedure you will use Network Monitor to verify that data sent between
the application server and database server is encrypted. You will start by
sending data in clear text form and then enable encryption first by configuring
the server and then by configuring the client.

To verify that communication is encrypted

  1. On the database server computer, use the SQL Server Network Utility to
    disable the use of encryption by ensuring that the Force protocol
    encryption
    option is not selected.
  2. On the database server computer, click Network Monitor in the Administrative
    Tools program group.
  3. On the Capture menu, click Filter to create a new filter
    configured to view TCP/IP network traffic sent between the database server
    and database server.
  4. Click the Start Capture button.
  5. Return to the client computer and Run a query “select * from sys.sysdatabases”
    in Query Analyzer Tool or SQL Server Management studio.
  6. Return to the database server and click the Stop and View Capture button
    within Network Monitor.
  7. Double-click the first captured frame to view the captured data.
  8. Scroll down through the captured frames. You can clearly see the SELECT statement
    and the list of databases can be retrieved from the instance.
  9. Now force the use of encryption for all connections by configuring the server
    with the SQL Server Network Utility:
    1. Use the SQL Server Network Utility to select Force
      protocol encryption
      .
    2. Stop and restart the SQL Server service.
  10. Return to Network Monitor and click the Start Capture button. In
    the Save File dialog box, click No.
  11. Return to the client computer and run the test console application once again.
  12. Return to the database server computer and click Stop and View Capture
    within Network Monitor.
  13. Confirm that the data is now unintelligible (because it is encrypted).
  14. Reconfigure the server to no longer force encryption:
    1. Use the SQL Server Network Utility and clear the Force
      protocol encryption
      check box.
    2. Stop and restart the SQL Server service.
  15. Start a new capture within Network Monitor and rerun the client application.
    Confirm that the data is once again in clear text.
  16. Return to the client computer and select Client Network Utility from the Microsoft
    SQL Server
    program group.
  17. Select Force protocol encryption, and then click OK to close the
    Client Network Utility.
  18. Return to Network Monitor and click the Start Capture button. In
    the Save File dialog box, click No.
  19. Return to the client computer and run the test console application once again.
  20. Return to the database server computer and click Stop and View Capture
    within Network Monitor.
  21. Confirm that the data is now unintelligible (because it is encrypted).
  22. Note that, in all cases, SQL Server sends its server authentication certificate
    in the clear to the client at the beginning of the communication sequence.
    This is part of the SSL protocol. Note that this occurs even when neither
    the server nor the client requires encryption.

NOTE:

====

  1. The account under which certificate is issue and the SQL server startup account
    should be the same.
  2. To check if your SQL Server instance is using certificate open the latest SQL
    Server ERRORLOG.
  3. 2011-07-22 01:09:56.00 Server      The certificate [Cert
    Hash(sha1) “B73210AAE74367DDFBEC9A6CB258AB29F17BFC15”] was
    successfully loaded for encryption.
  4. The encryption and decription happens in the network
    layer and the data is not encryption or encrypted inside the SQL Server 
    engine so you will be able to see the query and the output in the SQL Server profiler.
    But running the SQL Server profiler need some high previlege. Refer below

             http://msdn.microsoft.com/en-us/library/ms187611.aspx

             Refer the KB that talks about encryption in SQL Server and the
test case for the same. http://support.microsoft.com/kb/316898

2 Responses to “How To: Use SSL to Secure Communication with SQL Server”

  1. David said

    Very good article! 🙂

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

 
%d bloggers like this: