How SQL Server uses a certificate when the Force Protocol Encryption option is turned on (318605)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup
This article was previously published under Q318605

SUMMARY

This article describes how SQL Server uses, locates, and validates a certificate when the Force Protocol Encryption option is turned on either on the client or on the server to enable Net-Library encryption.

Note The concepts and discussions in this article that apply to SQL Server 2000 also apply to SQL Server 2005. However, in SQL Server 2005, use the ForceEncryption option instead of the Force Protocol Encryption option. You can set the value of the ForceEncryption option to Yes to enable encryption connections for an instance of SQL Server. For more information, see the "How to: Enable Encryption Connections to the Database Engine (SQL Server Configuration Manager)" topic in SQL Server 2005 Books Online.

MORE INFORMATION

How SQL Server uses certificates

SQL Server 2000 supports the Force Protocol Encryption option to control the Net-Library encryption. When the Force Protocol Encryption is on, SQL Server uses Secure Sockets Layer (SSL) to encrypt all communication between the client and SQL Server. A certificate is required because SSL encryption works only with instances of SQL Server 2000 that are running on a computer that has a certificate assigned from a public certification authority. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

276553 How to enable SSL encryption for SQL Server 2000 with Certificate Server

For more information about how to turn on SSL encryption for SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:

316898 How to enable SSL encryption for SQL Server 2000 with Microsoft Management Console

If the Force Protocol Encryption option is turned on on the client by using the SQL Server Client Network Utility, only communication for that client to SQL Server is encrypted. If that client tries to connect to another computer that is running SQL Server, the client tries to encrypt the communication. If the Force Protocol Encryption option is turned on on the client, that client is no longer backward compatible. Therefore, the client cannot connect to Microsoft SQL Server 6.5 or Microsoft SQL Server 7.0. If the computer that is running SQL Server to which the client is trying to connect does not have a certificate installed, the client receives this error message:

Encryption not supported on SQL Server
So, if a client requests encryption, a certificate must be installed on the computer that is running SQL Server. After you install the certificate on the computer that is running SQL Server, you must restart SQL Server in order to use the certificate. If you do not restart SQL Server after you install the certificate, clients with encryption turned on will fail to connect and the same error message appears:

Encryption not supported on SQL Server
If the Force Protocol Encryption option is turned on on the server by using the Server Network Utility, communication between all clients and SQL Server is encrypted. Therefore, if a certificate is not installed on the computer that is running SQL Server, or if SQL Server cannot validate the certificate, SQL Server fails to start. The SQL Server error log will have this text:

2001-08-23 15:12:09.48 server Encryption requested but no valid certificate was found. SQL Server terminating.
2001-08-23 15:12:09.62 server Error: 17826, Severity: 18, State: 1
2001-08-23 15:12:09.62 server Could not set up Net-Library 'SSNETLIB'..
2001-08-23 15:12:09.67 server Error: 17059, Severity: 18, State: 0
2001-08-23 15:12:09.67 server Operating system error -1073723998: ..
2001-08-23 15:12:09.67 server Unable to load any netlibs.
2001-08-23 15:12:09.74 server SQL Server could not spawn FRunCM thread.

How SQL Server locates a certificate

For the SQL Server 2000 golden release, SQL Server looks at the certificate store to find a certificate with the same name as the Fully Qualified Domain Name System (FQDN) of the SQL Server computer name. If you deploy SQL Server with a failover cluster, you must install the server certificate with the FQDN of the virtual server on all nodes in the failover cluster.

Starting with Microsoft SQL Server 2000 Service Pack 1, SQL Server looks for a binary value that is named Certificate in this registry key:

HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib

If you are using SQL Server 2005, you can find the Certificate registry entry under the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib

Note MSSQL.x is a placeholder for the corresponding value of the instance of SQL Server.

If the certificate value is missing or set to a zero length string, SQL Server looks at the certificate store to find a certificate with the same name as the FQDN of the SQL Server computer name. If the registry value is set, SQL Server tries to use that certificate.

How SQL Server verifies that a certificate is valid

  • The certificate's Enhanced Key Usage property has to be turned on for Server Authentication. To verify that the certificate is used for server authentication, use the Microsoft Management Console (MMC) Certificate snap-in. Double-click the certificate name, and then select Details. Click the Enhanced Key Usage property, and then verify that the value is:
        Server Authentication(1.3.6.1.5.5.7.3.1).
  • Make sure that the certificate name is the same as the SQL Server FQDN or the value configured in the registry (as described earlier).
  • You must install the certificate to the Certificates - Current User \Personal folder while you are logged on as the SQL Server startup account. This will make sure that the certificate will be put in the Personal Certificates folder of the SQL Server startup account. If you have logged on with a user account that is different from the SQL Server startup account, put the certificate in the Certificates\Local Computer Personal Certificates folder. This action solves the problem of having certificates stored under the wrong user account.

    To view the Current User folder, follow these steps:
    1. Logon as the SQL Server startup account.
    2. Use the MMC Certificates snap-in to verify the location of the certificate.
Modification Type:MajorLast Reviewed:1/2/2006
Keywords:kbinfo KB318605 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.