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 (1.3.6.1.5.5.7.3.1).
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.
https://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
https://support.microsoft.com/kb/276553/
https://msdn2.microsoft.com/en-us/library/ms191192.aspx
https://msdn2.microsoft.com/en-us/library/ms189067.aspx
Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights