Hi I am trying to find information on the SQL Server Fallback SSL certificate. The 2 questions I trying to find info on are: Where is the fallback certificate stored? Is it in-memory, is it in master.sys.certificates or somewhere else. When "Force Encryption" is set to Yes on the SQL Server instance and we don't have our own certificate and instead rely on the SQL fallback certificate, why do clients trust this certificate even if the "Trust Server Certificate" option (in SSMS) is unchecked. I have looked at MS Docs extensively I can't find any resource that gives me answers for the above. The links I am finding are related to how we can install and configure our own cert e.g. below MS Docs - Manage Certificates MS Docs - Enable Encrypted Connections to the Database Engine Any help or pointers is appreciated. Thanks Prem

Hi @Prem , > If we can find an MS Docs source that tells us where the fallback cert resides I did some research and test, I can’t find any MS document that offer this information. But I can share you some information about this based on my researching and test. We can’t find the location of self-signed server certificate that generated automatically by SQL server in registry and MMC. But we can find the self-signed server certificate that generated manually in MMC, under Local Computer account , Personal, Certificates. You can do a test following this blog . If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Hi @Prem , >Where is the fallback certificate stored? Is it in-memory, is it in master.sys.certificates or somewhere else. Quote from this thread ; 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. >When "Force Encryption" is set to Yes on the SQL Server instance and we don't have our own certificate and instead rely on the SQL fallback certificate, why do clients trust this certificate even if the "Trust Server Certificate" option (in SSMS) is unchecked. Quote from MS document ; To enable encryption when a certificate has not been provisioned on the server, the Force Protocol Encryption and the Trust Server Certificate options must be set in SQL Server Configuration Manager. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server. If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

SQL Server Fallback SSL certificate

Prem 26

I am trying to find information on the SQL Server Fallback SSL certificate. The 2 questions I trying to find info on are:

Where is the fallback certificate stored? Is it in-memory, is it in master.sys.certificates or somewhere else.
When "Force Encryption" is set to Yes on the SQL Server instance and we don't have our own certificate and instead rely on the SQL fallback certificate, why do clients trust this certificate even if the "Trust Server Certificate" option (in SSMS) is unchecked.

I have looked at MS Docs extensively I can't find any resource that gives me answers for the above. The links I am finding are related to how we can install and configure our own cert e.g. below

MS Docs - Manage Certificates
MS Docs - Enable Encrypted Connections to the Database Engine

Any help or pointers is appreciated.

Thanks
Prem

Chris Roeszler 10 Reputation points

2023-08-30T21:10:23.81+00:00

I realize this is over a year old, but I stumbled on this while doing some research for the very same thing, looking for documentation so I can have "proof" for what I'm about to send someone.

To answer your first question, the self-signed fallback certificate exists in memory only. Shut down the service and the cert disappears. Start the service and it's back. It's not in the master database, I've looked and compared on several instances now. I can never match the self-signed fallback cert serial number to what's in the master DB, not that you can easily extract it. I've never seen the thumbprint extracted either.

To answer your second question, when Force Encryption is set on the server, data is indeed encrypted on the wire. However, older providers (OLE DB 18, ODBC 17, JDBC 9.4, or System.Data.SqlClient) do not send a request to verify the certificate, they simply accept what the server sends back. This was confusing, so the newer providers (OLE DB 19, ODBC 18, JDBC 10.2, Microsoft.Data.SqlClient) have had their behavior update and now attempt to present a certificate before connecting. You can verify this behavior with a network trace.

Bonus info: SQL 2016 and older will always make a SHA1 certificate. If you have some sort of scanner picking up on a weak cert on your SQL server system, chances are it's the self-signed fallback certificate. Even if you make a strong cert and specify it, this cert gets created, and scanners detect it.

Starting with SQL 2017, the default is a SHA2 certificate. I'm sure some day there will be a SHA3 and SHA5 default, far far in the future.

Accepted answer

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-03-24T02:39:58.9+00:00

Hi @Prem ,

> If we can find an MS Docs source that tells us where the fallback cert resides

I did some research and test, I can’t find any MS document that offer this information. But I can share you some information about this based on my researching and test.

We can’t find the location of self-signed server certificate that generated automatically by SQL server in registry and MMC. But we can find the self-signed server certificate that generated manually in MMC, under Local Computer account , Personal, Certificates. You can do a test following this blog.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Prem 26 Reputation points

2022-03-24T02:49:47.297+00:00

Hi @CathyJi-MSFT
,

I did some research and test, I can’t find any MS document that offer this information.

That's fine if you couldn't find it either and I think that's good enough for me. Shall mark this as the accepted answer.

But we can find the self-signed server certificate that generated manually in MMC, under Local Computer account , Personal, Certificates.

Yes I understand that and I have tested that successfully.

Thank you for your perseverance on this and spending the time.

Cheers
Prem

PS: For anyone else interested in this, please refer to the earlier comments for answers to Q2.
Sign in to comment

1 additional answer

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-03-23T02:57:02.457+00:00

Hi @Prem ,

>Where is the fallback certificate stored? Is it in-memory, is it in master.sys.certificates or somewhere else.

Quote from this thread ;

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.

>When "Force Encryption" is set to Yes on the SQL Server instance and we don't have our own certificate and instead rely on the SQL fallback certificate, why do clients trust this certificate even if the "Trust Server Certificate" option (in SSMS) is unchecked.

Quote from MS document;

To enable encryption when a certificate has not been provisioned on the server, the Force Protocol Encryption and the Trust Server Certificate options must be set in SQL Server Configuration Manager. In this case, encryption will use a self-signed server certificate without validation if no verifiable certificate has been provisioned on the server.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Prem 26 Reputation points

2022-03-23T03:11:08.947+00:00

Hi @CathyJi-MSFT ,

Thank you for the feedback and comment. I did see that thread earlier and I had run some tests to check it. This is what I have found:

If I assign a cert to the SQL instance, then it is stored in the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib otherwise this value is empty

After removing the thumbprint value from this key (i.e. not assigning a cert to the instance) I also disabled the certificates in Local Computer -> Personal -> Certificates but the connection to SQL still worked and showed as an encrypted connection is sys.connections.

Am I missing something in my tests?

The below article talks about a SQL self-generated cert but doesn't say where it stored but it does advocate against using the SQL self-signed cert. So I think I will use a trusted authority cert but would be nice to know anyways.

MS Docs - Using encryption without validation

^ Prem

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-03-23T03:25:29.39+00:00

Hi @Prem ,

Did you mean that you removed certificate, but SQL server still using encrypted connection? Did you add TrustServerCertificate=true in SQL server connection string and enable Force Encryption? If so, SQL server will use self-signed server certificate to encrypt connection.

Yes, we do not suggest customer using self-signed server certificate in product environment.

We using below T-SQL to validate the connection on the server;

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections

Prem 26 Reputation points

2022-03-23T04:11:21.787+00:00

Hi @CathyJi-MSFT

I did the following 3 things:

Step 1 - Removed the self-signed certificate I had generated (screenshot below)

Figure: No certificate assigned to SQL Server instance. So SQL Server must use the fallback certificate.

Step 2 - I disabled the certificates in Local Computer -> Personal -> Certificates. This is to check if the fallback certificate is the one in this certificate store.

Step 3 - Set Force Encryption to "yes"

After the above steps were completed, I tried connecting using SSMS which was successful and used the below query to confirm. The query showed TRUE for the encrypt_option column.

SELECT c.session_id, e.login_name, c.encrypt_option,e.program_name, e.host_name, e.login_time, c.client_net_address FROM sys.dm_exec_connections c INNER JOIN sys.dm_exec_sessions e on c.session_id = e.session_id

I understand we shouldn't use a self-signed cert in production but wasn't sure if the SQL generated certificate fell in to the "self-signed" category or not. After reading the earlier mentioned MS Docs article (Using encryption without validation) I understand that this is also considered a self-signed certificate and therefore not safe.

Thanks
Prem

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-03-23T06:43:22.34+00:00

Hi @Prem ,

>but wasn't sure if the SQL generated certificate fell in to the "self-signed" category or not. After reading the earlier mentioned MS Docs article (Using encryption without validation) I understand that this is also considered a self-signed certificate and therefore not safe

Yes, you are right. The SQL server generated certificate is self-signed server certificate.

By the way, If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
And If you have further questions or issues please let us know.

Prem 26 Reputation points

2022-03-23T12:47:45.527+00:00

(v3 - Edited comment)

Thanks @CathyJi-MSFT for the links and comments.

In terms of my 2 questions re

1) where does fallback cert reside and
2) why is the fallback cert trusted on some clients

I think I have found the answer to question 2. It depends on the .Net Framework the client is built on. Clients built on .Net Framework (i.e. using System.Data.SqlClient) trust the cert where as clients built on .Net Core (using Microsoft.Data.SqlClient) don't trust it which might explain why SSMS trusts the cert and Azure Data Studio doesn't. I am guessing here that they are built on different frameworks.

With question 1 based on my tests we know that if a cert is not assigned to the instance, it will use the cert in Local Computer -> Personal -> Certificates. If this cert is also not available, then SQL Server will use a self-signed fallback certificate. If we can find an MS Docs source that tells us where the fallback cert resides that would be great and we can mark an accepted answer.

Thanks

Michael Maxey 27 Reputation points Microsoft Employee

2022-10-12T16:14:47.29+00:00

When you force encryption and did not provide a certificate to secure the connection, there is a 1024 bit certificate that is "baked in" to a SQL dll that is utilized. it will not show up in the machine or personal certificate stores.

Tim David 0 Reputation points

2023-03-07T13:11:57.5033333+00:00

I've been looking into this because Nessus has flagged the Fallback cert as only being sha1. There is no certificate in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib, but there is a certificate which has a sha512 in Local Computer -> Personal -> Certificates so it should use that.

I need the remove the fallback cert, but it appears not to exist in normal certificate terms!
Sign in to comment