We are required to use encryption with SQL Server, so we have enabled ForceEncryption. According to the documentation, if a certificate is not provided, SQL Server will generate its own internal certificate to do the encrypting.
We need to understand what level of encryption this is providing. I think we generally use 2048bit certs.
Which certificate store is the internal/fallback certificate located? And how can we get the encryption level?
By default, the certificate is located in the registry, at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib
(Where the MSSQL.x is MSSQL.x is a placeholder for the corresponding value of the instance of SQL Server.)
If SQL server cannot find a certificate at that location, it will fall back to searching the certificate store for a certificate with the FQDN of the computer it is installed on. By default, that certificate would be the machine certificate, found in Local Computer -> Personal -> Certificates through the MMC Certificates snap-in.
You would check the key length (and other properties) of this (or any) certificate through the MMC Certificates snap-in, by browsing to it, double clicking on it, and then hitting the Details tab in the ensuing dialogue box that comes up for the certificate.
If this cert is also unavailable, then SQL Server will generate a self-signed fallback certificate and use this for encryption. You can view the thumbprint or certificate being used when viewing the error logs around when SQL service restarts. The location of this fallback certificate is not officially known and other forums have suggested that this likely resides in memory. Please refer here for more info
