Factoring impact of SQL Secure Login Certificate Key Length on SQL Performance
In this post, Application Development Managers, Lou Sawyer and Everett Yang spotlight the performance impact of certificate key length for login operations with SQL Server.
The effect of RSA Key Length on CPU utilization during decryption is well documented. With a larger key size, we expect higher CPU utilization during decryption. This is a factor we must consider when balancing security requirements and capacity planning.
In the context of SQL Server, we can configure SQL Server to require encryption for connections or the SQL client can request encryption when establishing connection. Not as well documented is that all SQL login packets are encrypted, regardless of whether the server or client requested encryption.
This is briefly mentioned here: Technet Article.
“Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. “
An additional point to note is that the certificate being used for login decryption may be substantially different between the SQL Server instances. SQL Server can generate its own self-signed certificate or use one from the personal certs in the local machine store which can complicate performance diagnosis and capacity planning.
Per TechNet Article:
- SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide protection against identity spoofing by the server.
- The certificate must be in either the local computer certificate store or the current user certificate store.
- The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
- The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (126.96.36.199.188.8.131.52.1).
- The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate’s key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
- The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
- SQL Server 2008 R2 and the SQL Server 2008 R2 Native Client support wildcard certificates. Other clients might not support wildcard certificates. For more information, see the client documentation and KB258858.
On a SQL Server instance where a certificate exists in the personal certificates in the local machine store that matches the selection criteria, SQL Server will automatically select this certificate. The certificate selection requires no input or even awareness from an administrator, it is fully automatic. When choosing certificates to deploy to SQL Server, we should take this into consideration. A strong certificate may be required for certain applications or environments. When doing so, we need to factor in the impact to SQL Server and plan accordingly.
If a certificate with a long key length is deployed, you may notice high CPU utilization by the LSASS process. This occurs because SQL Server hands over the decryption work to the LSASS process as part of secure login. The impact of a certificate with a large key size is not seen with the SQL Server process itself but LSASS. The exact impact, of course, is proportional to the rate of connections. High CPU utilization by the LSASS process causes resource constraint on the SQL Server and can be mapped directly to negative performance impact.
The Performance Comparison chart below compares a 200-concurrent user load test of simple logins executed with SQL Self Signed Certificate used for login decryption (Baseline) and the same load test with a SHA256 8K Key Certificate (Comparison Run):
As you can see, by varying only the encryption certificate used during login, we observed a 35% degradation in test time.
The test result above is not a straightforward endorsement of leveraging SQL Server generated self-signed certificate. TechNet documentation above pointed out that the generated self-signed certificate offers only limited protection. The self-signed certificate, for example, is susceptible to man-in-the-middle attacks. The test result above does point out that if you choose to use a stronger certificate for enhanced security, you should plan accordingly for the potential impact on performance.
Regardless of your decision, it is important to be able to verify whether the intended certificate is being used by SQL Server. This information is captured in SQL ERRORLOG and the currently loaded certificate can be quickly queried using the command xp_readerrorlog 0, 1, “certificate”. For example, if a self-signed certificate is used, you would find the following entry:
2017-01-12 14:23:39.01 spid15s A self-generated certificate was successfully loaded for encryption.
Alternatively, if SQL Server identified an eligible certificate from the personal certs in the local machine store you would find an entry like this:
2017-03-02 15:51:04.660 spid15s The certificate [Cert Hash(sha1) “82DE90F6C1264BFDD138803BF33A5B629776CFF5”] was successfully loaded for encryption.
The cert hash in the ERRORLOG is the thumbprint of the certificate with the spaces removed; you can dump out all the personal certs from the machine store and their hash values from an elevated command prompt using the following command:
- SQL Server will always encrypt login packets, regardless whether client or server is configured to request encryption.
- SQL Server will generate a self-signed certificate to use for encryption unless a certificate matches the selection criteria.
- If the selected certificate has large key size, it can contribute to high CPU utilization by the LSASS process during login and impact SQL Server performance.
- It is important to understand the impact of certificate deployment to a SQL Server instance, and make your decision based on security needs vs scalability.
Premier Support for Developers provides strategic technology guidance, critical support coverage, and a range of essential services to help teams optimize development lifecycles and improve software quality. Contact your Application Development Manager (ADM) or email us to learn more about what we can do for you.