Certificate for SQL Server 2005

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application. Certificate is used to encrypt the data transmission. Certificates are stored locally for the users on the computer


In order to be successfully loaded by SQL Server 2005, a certificate must meet all the following requirements:


1)      The certificate must be in either local machine or current user certificate store.

2)      The certificate must have a good time stamp, i.e. the current system time must be in the valid time window of the certificate.

3)      The certificate must be meant for Server Authentication, i.e. the certificate's Enhanced Key Usage property has to be turned on for Server Authentication (

4)       The Certficate’s key spec must include AT_KEYEXCHANGE property. Usually, the certficate's key usage should include Key Encipherment.

5)      The certificate’s subject CN must match the FQDN of the server machine, or the FQDN of the virtual server if the server runs on failover cluster.  This implies that required certificates must be provisioned on all nodes in the failover cluster.



If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).


Just a note that we have chosen to alleviate the requirement of 5). We will match against the computer name part (i.e., the first part) of the FQDN, rather than the whole FQDN. In addition, if a user configures the server to load the certificate by hash value, item 5) will not be a requirement anymore. In SQL Server 2005, a user can use SQL Server Configuration Manager to configure the server to load the certificate by hash value. The Thumbprint value of the certificate will be saved as the value of the following registry:


HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibCertificate


The changes will provide better flexibility to users, and will be helpful if the server machine is multihomed or need to switch between domain and non-domain situation. The changes will be available in SQL Server 2005 Service Pack 1.


Please refer to the following links to learn more about SSL, Certificate Provisioning, and/or Data Encryption in SQL Server 2005.






Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights


Comments (12)
  1. I presented for a TechNet evening in Reading “SQL Server 2005 A Technical Overview” on the 11 May. I…

  2. In this blog, I am focusing on one specific error message which I think a little confusing for you to…

  3. Reid says:

    Can you install a client SSL certificate on a SQL server client (website) so that it can connect to a SQL server securely, and how is it done?

  4. Yan Liberman says:

    Этот пост посвящен использованию сертификата с собственной подписью (self-signed

  5. In the blog Certificate for SQL Server 2005 , I explained the requirements of a certificate for SQL Server

  6. Alessandro says:

    After a DTS run , the SQL Server services from remote is disabled with this message:

    [Microsoft][ODBC SQL Server Driver][DBNETLIB] SSL Security Error

Comments are closed.

Skip to main content