The SQL Errorlog tells of whether a self-signed certificate is being used or a certificate from the certificate store is being used.
- I wish the errorlog told you which SSL Cert its using, so we filed a design-change request to ask for that in future releases of SQL Server. You may be able to run the Process Monitor tool (Microsoft/Sysinternals) to see which one is being loaded.
Other hints of failure may be visible:
- If you specify an SSL certificate but it fails to load, the errorlog will display
Message 26014: "Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online."
- Similarly, if you specify an SSL certificate but it fails to load, and you have required ForceEncryption=TRUE, then the errorlog will display
Message 26015: "Unable to load user-specified certificate. Because connection encryption is required, the server will not be able to accept any connections. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online."
Message 26017: "Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate."
===============
As described above, in "Configuring SSL for SQL Server", If you want to provide a certificate for the SQL server instance to use, you can list which certificates are eligable via the Configuration Manager in the start menu under the SQL program group. Click the SQL Server (2005/2008) Network Configuration node in the tree, and expand your instance. Right click > Properties to list if "Force Encryption" is No or Yes. On the Certificate tab, there is a list box that includes only the eligable certificates from the certificate store.
SQL uses the SQL WMI provider to check certificates in two stores:
- “Local Machine” My certificate store,
- "User" My certificate store.
If the SSL certificate you have installed into the certificate store does not match the exact list in the above section "Certificate Requirements" then it will not be displayed in the drop-down list within Configuration Manager.There is one more gotcha we found - even if your configuration manager doesn't list a certificate being used, or you had one picked and cleared that existing SSL certificate from the configuration manager list with the "Clear" button, SQL Server can still be using an SSL certificate, instead of the self-signed cert. Check the errorlog for either of the above informational messages to determine which one is being used. The reason is, If you used to have a certificate in either the Local machine or User's My Store, SQL Server may still pick it up from the certificate store and utilize that certificate to encrypt with if the machine name (fullly qualified domain name) matches the subject in that certificate.
If you decide you don't want the SSL certificate to be chosen any more, and want to go back to self-signed certificate, then you can remove it from the list by Clearing the certificate list in Configuration Manager, and also you have to remove the certificate from its respective cert store so SQL will stop picking it up.
You can get to the Certificate Stores, by using mmc.exe (start > run) and adding in the Snap-in for Certificates. The first choice presented is "My user account", "Service Account", "Compute Account". From what I can tell, SQL checks the nodes under the "My user account" store in the Personal node, and the nodes under the "Computer Account" store.
To add a cert to the store, Right click and select import and browse to the certficate you want to import.
The certificate is encoded as a binary large object and stored as a binary value in the following file location:
%Userprofile%\Application Data\Microsoft\SystemCertificates\My\Certificates (this maps to the My user Account store)
SQL has to be able to access that folder to be able to find and load the certificate. The SQL Service startup account has to have permissions to the App Data folder of its own profile. AppData folders can be customized which can lead to problems figuring out why a certificate fails to load should a failure occur.
=============
For advanced situations, Microsoft has certain SNI BID tracing on the server they can enable to get the Windows error condition being returned from a few specific failures if you should encounter problems when SQL is picking the certificate or generating the self-signed certificate.
Also in some situations, the ring buffer DMV sys.dm_os_ring_buffers may offer some error feedback about which OS error was detected when failures occur related to non-sql APIs happened during loading of SSL Certificates or self-signed certificates.
select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'
Don't expect user-friendly results. I got 0xc000003A which I looked up and found out to be STATUS_OBJECT_PATH_NOT_FOUND = 0xC000003A = {Path Not Found} The
path %hs does not exist. The root cause of that problem leading to the HResult was that the AppData environment variable pointer to the folder was pointing to an invalid folder.
=============
You can test if the certificate is eligable for SQL Server usage by querying the WMI provider directly outside of Configuration Manager. This WMI query should return the same list that's in pull-down box in Configuration Manager certificates listing.
If the SSL Certificate doesn't show up in the output of this script from the WMI provider directly, then probably something is not matching the criteria explained in the "Certificate Requirements" text in the MSDN article above.
To query the WMI provider, one way is to a make a text file named TEST_SQL_CERT.vbs and put VB script code in the file. Substitue the computer name in instead of the period in the first line if necessary to query remote SQL Server WMI providers.
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer &
"\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SecurityCertificate",,48)
For Each objItem in colItems
Wscript.Echo "-----------------------------------"
Wscript.Echo "SecurityCertificate instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
Wscript.Echo "FriendlyName: " & objItem.FriendlyName
Wscript.Echo "IssuedBy: " & objItem.IssuedBy
Wscript.Echo "IssuedTo: " & objItem.IssuedTo
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "SHA: " & objItem.SHA
Wscript.Echo "StartDate: " & objItem.StartDate
Wscript.Echo "SystemStore: " & objItem.SystemStore
Next
=============
There is another handy tool called CertUtil that allows you to open the cert store from the command line and print out the details of a certificate to text for review of the criteria required for SQL to use it.
http://www.microsoft.com/downloads/details.aspx?FamilyID=C16AE515-C8F4-47EF-A1E4-A8D
CBACFF8E3&displaylang=en
You can run it like this:
Certutil -store -v my <CertificateSerialNumber>
For the command line, I got the <CertificateSerialNumber> from the following Registry key (for the first instance MSSQL.1)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
- registry value Certificate
The other important registry value under that key is the ForceEncryption flag. That's registry key is how the SQL Configuration Manager tells the SQL Server engine to check for an SSL certificate when the service is starting up. The ForceEncryption flag enforce that encryption has to be enabled or refuse connections and refuse to start the Server. The Certificate value tells SQL which one to prefer when its starting up.
Even if no certificate value is present in the registry key, SQL Server will use the first one it finds in the two described Certificate Stores that matches the fully-qualified domain name.
=============