7 Steps to SSL Encryption

In SQL Server 2000, Microsoft introduced new features to satisfy its customers' growing concerns about data security. One little-understood feature is automatic support of Secure Sockets Layer (SSL)-encrypted network traffic between the clients and the server. Encryption slightly slows down performance because it requires extra actions on both sides of the network connection. However, for users who are concerned about the security of their network communications, the benefits of encryption outweigh this slight performance penalty. Encryption is especially useful when clients connect to the SQL Server across the Internet and data travels across public networks.

SSL encryption has become an industry standard—it is the type of encryption most companies use for Internet and e-commerce applications. Two levels of SSL encryption, 40-bit and 128-bit, offer different types of data protection. SQL Server 2000 supports both encryption levels. To make SSL encryption work, you need to obtain a valid key (or certificate) from a trusted certificate authority (CA). After you've installed the certificate on a system that's running SQL Server 2000, you can configure SQL Server to force encryption between the clients and the server. Windows 2000 ships with its own CA that you can use for intranet applications. Most companies use third-party CAs for Internet applications. (For more information about CAs, see the sidebar "CA Basics," below.)

SSL encryption is different from the Multiprotocol Net-Library encryption that exists in SQL Server releases earlier than SQL Server 2000. SSL encryption supports all network libraries and protocols including the most popular types: TCP/IP and Named Pipes (for clustered installations, only these two network libraries are available). You can also use SSL encryption for multiple instances of SQL Server, whereas Multiprotocol Net-Library encryption, which uses the Windows RPC encryption API, recognizes only the default instance of SQL Server 2000. Either the client or the server (but not both) can request SSL encryption. Client-requested encryption specifies that all outbound communications from that client to all connected servers will be encrypted. Server-requested encryption specifies that all inbound connections to the server will be encrypted, then decrypted at the server.

Encryption for a clustered installation is more difficult to configure than that for a standalone server. Many sources explain how to set up SSL encryption on a single box, but information about setting up SSL encryption for a clustered environment is harder to find. Unfortunately, SQL Server Books Online (BOL) doesn't give much information about how to configure SSL encryption and only briefly mentions configuration requirements for a clustered environment. You also have to look outside BOL to get information about how to set up proper authentication certificates. But with clear instructions, setting up SSL encryption is simple and straightforward. Let's take a detailed look at how you would set up SSL encryption in a clustered environment for a fictional company called IDM.

The Environment

For this example, imagine that our fictional company has a typical clustered environment. (When I refer to a cluster in this article, I'm talking about two to four independent computers that work as one system.) IDM is a manufacturer that communicates with its field sales force around the country by using the idmsql server as the data tier for its applications. Clients connect to the server over the Web by using the SSL protocol to pull new information from or push it to the idmsql server. In this scenario, it's more appropriate to request (and manage) encryption on the server.

To configure clustering in this environment, you must have Microsoft Cluster service installed on each machine, or node. All nodes must run on either Win2K Datacenter Server, Win2K Advanced Server, or Windows NT 4.0 Enterprise Edition. A clustered environment is an effective solution when you need high availability of data; in the event of a failure, clusters can reduce system downtime to 1 or 2 minutes. For information about how to configure a server cluster, see the "Creating a Failover Cluster" section in SQL Server 2000 BOL.

IDM hosts its SQL Server databases on two machines: idmdb1 and idmdb2. Both machines run on Win2K AS Service Pack 2 (SP2), and both have the same hardware configuration. The two machines are part of the domain tiga.tld and are members of the cluster called MyCluster. Cluster service is installed on both machines. The unnamed default virtual instance of SQL Server 2000 Enterprise Edition is called idmsql and is installed on MyCluster. (A virtual server is the default option when you install SQL Server in a clustered environment.) The MSSQLServer service is running under the account tigasqlsvc, which is part of the Administrators user group on each node. A standalone CA called TIGA2 is installed in the tiga domain on the PDC tiga-dc-01.

Configuring SSL

To configure server-requested SSL encryption for IDM's clustered environment, you'll use seven steps. The procedure for configuring SSL encryption on a clustered SQL Server is conceptually the same as the process you use on a single SQL Server, but it includes minor variations that I'll explain as we go.

Step 1: Log in. You log in to the idmdb1 node by using the tigasqlsvc username. Then, you supply the proper password for the system.

Step 2: Request a certificate for the fully qualified name of the virtual server. To enable SSL encryption, you must make sure your server and clients have a digital certificate from a trusted root CA. (For information about the two kinds of CA, see the sidebar "CA Basics.") For IDM, the virtual server name is idmsql.tiga.tld. To request a certificate, start Internet Explorer (IE) and type in the Address section a URL for the CA Web site in your domain. IDM would use http://tiga-dc-01/certsrv. Certsrv.exe is the Windows program running Microsoft Certificate Service. Figure 1 shows the site's Welcome page. Select the Request a certificate option, and click Next. Select the Advanced request option on the next page, and click Next. On the Advanced Certificate Requests page that Figure 2 shows, choose the default option Submit a certificate request to this CA using a form, and click Next. On the next page, in the Name text box, type the full network name of your virtual SQL Server (note that this isn't the name of the cluster node from which you logged in). Figure 3 shows IDM's SQL Server name, idmsql.tiga.tld. Make sure that Client Authentication Certificate is selected as the intended purpose of the certificate. (If you use an enterprise CA, you need to select Web Server as the intended purpose of the certificate to get a screen template that lets you specify the full network name of the SQL Server.)

The Advanced Certificate Request window also lets you specify various encryption and certificate options such as cryptographic provider and key size. For IDM, you accept all the default values. When you've completed your selections, click Submit to send your request to the CA. The next screen will inform you that the CA has received your certificate request and that you must wait for a network administrator to issue a certificate. (At this point, you can ask your network administrator to process your request.)

You can skip the next step if you have an enterprise CA, in which case your request for a certificate is automatically processed. But Windows pends all certificate requests for standalone CAs, and IDM has a standalone CA.

Step 3: Process the certificate request and issue a certificate. Few DBAs have the permissions to manage Certificate Authority, especially if the CA is installed on the PDC. In most cases, you have to wait for your network administrator to process the request. But if you have administrator network permissions, you can process the certificate request yourself. First, log in to the server where the CA is installed, and select Start, Programs, Administrative Tools, Certification Authority. In the Microsoft Management Console (MMC) Certification Authority snap-in, expand the Pending Requests section, find the request you just submitted, right-click the request, and select All Tasks, Issue, as Figure 4 shows. To verify success, expand the Issued Certificates section, and look for your certificate.

Step 4: Install the certificate. After getting confirmation of the certificate from your network administrator or issuing the certificate yourself, go again to the CA's Web page (http://tiga-dc-01/certsrv). On the Welcome screen that Figure 1 shows, select the Check on a pending certificate option, and click Next. On the following page, select your certificate in the list box, and click Next. On the Certificate Issued screen (which you'll get almost immediately after completing step 2 if you have an enterprise CA), click the Install this certificate option. The next page will display a confirmation that your certificate has been successfully installed.

Step 5: Verify your certificate. To verify that your machine has the certificate properly installed, right-click the IE icon on your desktop, and select Properties. Select the Content tab, and click Certificates. The certificate you just installed must be in the list for you to enable SSL encryption. Make sure that the Issued To value is your SQL Server's full network address (idmsql.tiga.tld, in this case). Select your certificate, and click View. Make sure that the bottom pane of the window says your certificate status is OK, as Figure 5 shows.

If you're working with a nonclustered installation of SQL Server, you issue the certificate to the server computer by using its full domain name. In this example, the full name of IDM's idmdb1 machine would be idmdb1.tiga.tld. The rest of the procedure is the same. For a clustered SQL Server environment, you need to repeat steps 1 through 5 for each node.

Step 6: Request SSL encryption on the server. After you successfully install certificates on all cluster nodes, go to the node that currently owns the SQL Server service and select Start, Programs, Microsoft SQL Server, Server Network Utility. Select the Force protocol encryption option, as Figure 6 shows, and click OK. This option enables encryption for all incoming connections.

Step 7: Restart the SQL Server service to start SSL encryption. To stop the SQL Server service in a clustered environment, go to any cluster node and select Start, Programs, Administrative Tools, Cluster Administration. Then, right-click SQL Server in the Active Resources section, and select Take Offline, as Figure 7 shows. Wait while SQL Server and all dependent resources (e.g., SQL Server Agent, Microsoft Search) are taken offline. Then, right-click SQL Server again, and click Bring Online. Finally, verify that the SQL Server service has started by trying to connect to it using Query Analyzer. Check the SQL Server error log to verify that the server didn't report any errors when it started.

SSL—Ready to Encrypt

To verify that you've established SSL encryption, monitor the communications between a client and SQL Server by using Network Monitor. For information about installing Network Monitor, see the Microsoft article "HOW TO: Install Network Monitor in Windows 2000" (Q243270, http://support.microsoft.com).

You also need to have the latest service packs installed in your environment. Microsoft has acknowledged that in a Win2K-based cluster, a virtual SQL Server might not start after you configure SSL encryption. The problem is fixed in SQL Server 2000 SP2. If you don't have SP2 installed on your SQL Server and you want to successfully restart your server after you force protocol encryption, you need to be sure that the SQL Server service account is part of the Administrators user group (as it is in this article's example).

You can use steps similar to those I outline in this article to enable SSL encryption for a particular client, but you'll need to use the Client Network Utility instead of the Server Network Utility to enable encryption. For more information about implementing SSL encryption on a client, see the Microsoft article "HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server" (Q276553, http://support.microsoft.com).

Using a clustered environment to support your Internet and e-commerce applications can improve performance and reduce system downtime, but using unencrypted communications can leave security gaps. If you use SSL encryption in your clustered environment, you can close those gaps. I hope the steps in this article will simplify setting up SSL encryption on your system.