If you use SSL Encryption on a SQL Server cluster, you can use the same procedures except that the certificate must be issued to the fully qualified domain name of the Virtual SQL Server and not the individual computer name. In addition, the way Microsoft recommends that you use Certificates and SSL encryption on a SQL Server cluster is:
- Install the certificates on each node in the cluster.
- Install the Trusted Root Authority on each client.
- Enable the Force Protocol Encryption option from the client computers by using the Client Network Utility.
Additionally, when you enable Force Protocol Encryption from the server, it encrypts the logins and data. However, it does not require the client to trust the same root authority. If you prefer the client to trust the same root authority, you must use Client Network Utility or the connection string option to force protocol encryption on the client. This is by design.
SQL Server does not start if the certificate is invalid or if the service account that was used to start the MSSQLServer service cannot locate the certificate. Therefore, Microsoft recommends that you request the certificate while you are logged on by using the same user account that you used to start the MSSQLServer service.
If Microsoft Internet Information Services (IIS) is installed on the computer that is running SQL Server, you can also use the IIS Service Manager Wizard on the Directory Security tab. The certificate must be a server certificate that was issued to the fully qualified domain name (FQDN) of the server. You cannot use the IP address for the certificate name. A client computer must request the connection to the server by the FQDN or NetBIOS name of the server. You cannot connect to the server by using the IP address of the computer that is running SQL Server.
If the computer has multiple certificates installed in the user store or in the machine store, you may have to specify which certificate should be used for SQL Server.
Create a Certificate value of type REG_BINARY in the following registry key:
As an example, the registry should appear similar to the following when you export it:
The only way to verify that you have successfully made an encrypted connection is to capture the traffic between two computers by using Microsoft Network Monitor or a Network Sniffer tool.
For more information about how to setup Microsoft Network Monitor, click the following article number to view the article in the Microsoft Knowledge Base:
Request and install a certificate by using a certificate server
- Make a HTTP connection to the certificate server while you are logged on by using the same account that you used to start the MSSQLServer service. For example, you can make the following connection:
Note Microsoft recommends that you start the MSSQLServer service by using a domain user account or a local user account that has minimal privileges and not the local system account.
- Select Request a certificate, and then click Next.
- In the Choose Request Type: page, click to select Advanced request, and then click Next.
- Select Submit a certificate request to this CA using a form, and then click Next.
- Enter the fully qualified domain name of your computer in the Name box. Ping your computer to get the fully qualified domain name if you are not sure what it is.
- In the Intended Purpose section, change the selection to Server Authentication Certificate by using the drop-down list box from the Client Authentication Certificate. For an Enterprise Certificate Authority you would choose a template instead.
- Click the Store certificate option in the local computer certificate store.
- Leave all other items as the default. Click Submit.
- The last page presents you with a Certificate to Install hyperlink. Click Install this certificate.
- To open the MMC console, click Start, and then click Run.
- In the Run dialog box, type mmc, and then click OK.
- On the Console menu, click
- Click Add, and then click
- Click Add.
You are prompted to open the snap-in for the current user account, for the service account, or for the computer account.
- Click Computer Account.
- Click Local computer, and then click
- Click Close.
- Click OK.
Your installed certificates are located in the Certificates folder in the Personal container.
- A private key corresponds to this certificate.
- The certificate Subject Name is equal to the FQDN of the computer.
- The intended purpose of the certificate is for server authentication.
- The certificate path has a valid chain to the root authority.
Enable SSL encryption on the SQL ServerAfter the certificate is installed on the server, you can enable SSL encryption by following these steps:
- Use the SQL Server Network Utility and click to select the Force protocol encryption check box.
- Stop, and then restart the MSSQLServer service for the Default Instance or Named Instance.
- Use the SQL Server error log to verify that SQL Server did not report any errors when it started.
Enable SSL encryption for a specific clientIf you do not want to enable SSL encryption globally at the server, you can enable SSL encryption from specific clients. Do not enable SSL encryption on both the server and client, use one or the other. If you enable SSL encryption on a per client basis, the client computer must trust the server certificate. The certificate must already exist on the server. The client computer does not require a certificate, but it must have the server certificate as a Trusted Root Certificate Authority. Follow these steps to enable SSL encryption on a per client basis:
- Make sure that you disable or clear the Force protocol encryption option in the Server Network Utility.
- Make a test connection from a client computer by using the Network Monitor or a Network Sniffer tool to verify that the communication between a client and server computer is not encrypted.
- Right-click the Internet Explorer icon that is located on the Desktop on the computer that is running SQL Server.
- Right-click Properties.
- Click the Content tab.
- Click Certificates.
- Click Trusted Root Certification Authorities.
- Click to select Certification Authority.
- Click Export, and then click Next.
- In the Export File Format dialog box, click Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B).
- Click to select the Include all the certificates in the certification path if possible check box.
- Select a file name for the exported certificate. Make sure that the file location is somewhere that the client computer can later access to import it.
- Click Next, and then click Finish.
- On the client computer, select your Internet browser, right-click Properties, point to Content, and then click Certificates.
- Click the Trusted Root Certification Authorities tab.
- Click Import, click Next, click Browse, and then click Change Files of type to: PKCS #7 Certificates(*.p7b).
- Select the certificate you just exported from SQL Server, and then click Open. Click Next.
- Click to select the Automatically select the certificate store based on the type of certificate check box.
- Click YES to add the following certificate to the root store.
- Click Next, and then click Finish.
- A dialog box opens with the text:The import was successful.
- Verify that the certificate appears under the Trusted Root Certificate Authorities, and that Intended Purposes indicates All.
- Click View to verify that no errors were reported with the certificate.
- Click the Certification Path tab, and then check the Certificate status to see if it is set to
- Open the Client Network Utility, and then click to select the Force protocol encryption check box.
Test the encryption from a clientTo test the encryption from a client, use one of the following methods:
- Use the Query Analyzer tool.
- Use any ODBC application where you can change the connection string.
Query AnalyzerTo test with the SQL Query Analyzer tool, follow these steps:
- Use the SQL Server Client Network Utility.
- Click to select the Force protocol encryption check box.
- Connect to the server that is running SQL Server 2000 by using Query Analyzer.
- Monitor the communication by using Microsoft Network Monitor or a Network Sniffer.
ODBC applicationTo test with an ODBC application, follow these steps:
- Modify the ODBC or OLEDB connection string:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True
- Connect to the computer that is running SQL Server 2000 and monitor the communication by using Microsoft Network Monitor or a Network Sniffer.
Article ID: 276553 - Last Review: Sep 17, 2011 - Revision: 1