How to enable SSL encryption for SQL Server 2000 if you have a valid Certificate Server
Article ID: 276553 - View products that this article applies to.
This article was previously published under Q276553
This step-by-step article lists the steps that you must use to enable Secure Socket Layer (SSL) encryption for Microsoft SQL Server 2000 if you have a valid Certificate Server in your network environment. If you have purchased certificates from a third-party certificate vendor, follow the instructions provided by the vendor. SQL Server 2000 permits encrypted connections over all network libraries by using certificates and SSL encryption. You can enable SQL Server encryption by using the SuperSocket Net-Library, Ssnetlib.dll or Dbnetlib.dll
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:
319349To encrypt communication between a client computer and a server, you must first decide if you want the encryption to be per server or on a per client basis. Keep in mind that there is a current SQL Server limitation if you enable encryption on the server. Encryption will be for all incoming connections. If you enable encryption on the client computer, all outgoing connections from that client try to make an encrypted connection to any SQL Server.
(http://support.microsoft.com/kb/319349/ )BUG: Turning on the "Force Protocol Encryption" option is irreversible if there is no certificate
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:
Click on the Certificate value, and then type the thumbprint property value of the certificate in the data column.
As an example, the registry should appear similar to the following when you export it:
If this registry key is set to 0 on the computer, the computer ignores the certificates on the computer. The computer that is running SQL Server will start but not read the certificate on the computer. If you want to use encryption and the computer only has one certificate, you do not need this registry key.
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:
(http://support.microsoft.com/kb/243270/ )How to install Network Monitor in Windows 2000
Request and install a certificate by using a certificate server
To verify that your certificate installation is correct, use either the MMC Certificate snap-in to verify the certificates or use the CertUtil.exe tool that is installed on the certificate server to list the certificates. To load the MMC Certificate snap-in, follow these steps:
Enable SSL encryption on the SQL ServerAfter the certificate is installed on the server, you can enable SSL encryption by following these steps:
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:
Test the encryption from a clientTo test the encryption from a client, use one of the following methods:
Query AnalyzerTo test with the SQL Query Analyzer tool, follow these steps:
ODBC applicationTo test with an ODBC application, follow these steps:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/316898/ )How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console