Steps to SSL Encryption. In SQL Server 2. 00. Entrust Root Certificate Authority—G2 Valid Until: 12/7/2030 Serial Number: 4a 53 8c 28 Thumbprint: 8c f4 27 fd 79 0c 3a d1 66 06 8d e8 1e 57 ef bb 93 22 72 d4. ![]() 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. Two levels of SSL encryption, 4. SQL Server 2. 00. 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 2. SQL Server to force encryption between the clients and the server. Windows 2. 00. 0 ships with its own CA that you can use for intranet applications. Most companies use third- party CAs for Internet applications. 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 2. 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. 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. 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. ![]() All nodes must run on either Win. K Datacenter Server, Win. K Advanced Server, or Windows NT 4. 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 . Information about setting up server-requested SSL encryption in a clustered SQL Server environment is hard to come by. But these clear steps pave the way to a. In order for SSL encryption to work, the mobile device must trust the CA that issued the certificate to Exchange. Unless your Exchange 2007 server is using a. As many of you may well know the long awaited public release candidate of Microsoft Lync 2010 (previously Communications Server Both machines run on Win. K AS Service Pack 2 (SP2), and both have the same hardware configuration. The two machines are part of the domain tiga. My. Cluster. Cluster service is installed on both machines. Download Citrix Receiver, free Citrix product trials, version updates, utilities and more.The unnamed default virtual instance of SQL Server 2. Enterprise Edition is called idmsql and is installed on My. Cluster. A standalone CA called TIGA2 is installed in the tiga domain on the PDC tiga- dc- 0. 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 idmdb. 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. 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- 0. 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. Make sure that Client Authentication Certificate is selected as the intended purpose of the certificate. 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. 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- 0. 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. 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. In this example, the full name of IDM's idmdb. 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. 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 . Microsoft has acknowledged that in a Win. K- based cluster, a virtual SQL Server might not start after you configure SSL encryption. The problem is fixed in SQL Server 2. 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). For more information about implementing SSL encryption on a client, see the Microsoft article . 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |