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 tiga\sqlsvc, 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 tiga\sqlsvc 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.)