October 9, 2018 at 7:31 am
Hello, I am getting flagged in a vulnerability scan that this certificate doesn't have strong enough encryption. We do not have our SQL Server set to force encryption, so I'm trying to figure out if I can delete this certificate? I can't even really figure out where it is located... I have the SQL configuration manager set to our properly generated certificates but I'm not sure if that remedies the problem or not.
October 9, 2018 at 8:15 am
Ok, I've read more and while I still don't know how to fix this... I know I can't delete it. 🙂
The problem is the hashing algorithm that it is using is "deprecated". I see SQL 2017 has upgraded the algorithm but is there no way to make it use a different encryption method? I don't want to force SSL connections, so I'm not sure why it is still using the fallback certificate?
January 26, 2022 at 9:09 pm
A little old, but dealing with the same thing. The encryption is used for the basic login part - encrypting the connection between the client just for the login. The only way I've found so far to replace it is to get an actual trusted cert and replacing it on the server. That gets trickier with clusters because that cert needs to be the same on all nodes of the cluster that can host that SQL instance, but once set you should stop seeing that issue. The "dbatools" PowerShell module has some methods to help set the cert. You have to make sure that you update the cert before it expires or your SQL Server may not start without replacing/removing the cert.
August 30, 2023 at 9:20 pm
I just posted this on an MS forum. I didn't bother to modify the response as I feel the information I shared there would be relevant here too; a bit more than original asked about I think. Sorry this is so late, hope this helps!
From my MS forum post:
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply