January 25, 2021 at 3:03 pm
Hi
I have a clustered MS SQL server setup (2016, Enterprise, on AWS instance) with two nodes. I had configured SSL encryption on the two nodes using the guides provided by Microsoft as well as SQL tips.
Everything works absolutely fine. I've checked the SQL Server logs which shows the SSL certificate was loaded when the DB started up after the rebooting with Force Encryption turned on. No issues with connecting to the DB using SSMS either.
But when I ran nmap from my security testing server (nmap -Pn --script ssl-cert <ip> -p <port> or check using Openssl), I was not able to see any SSL certificate on port 1433. The command should return the SSL certificate details.
If the cert is loading fine, wouldn't that show that the configuration was done correctly? What did I missed?
January 25, 2021 at 4:08 pm
This (https://serverfault.com/questions/1023512/how-to-enumerate-ciphers-for-mysql-server-using-nmap) says nmap doesn't support this.
If your client attempts to connect, do you get the secure connection? Or if you have no encryption, do you get the connection refused?
January 25, 2021 at 4:25 pm
NOTE - the below is my opinion; I was not able to find reliable documentation on it, but based on how the settings are set on both the client side and server side, I believe the below to be accurate.
My guess - this is by design for nmap and openssl. I just looked into nmap and if you look at the libraries being used by the script ssl-cert, none of them are SQL Server. So nmap would have nothing configured to handle a connection to SQL server, let alone pass along the appropriate information to get back the certificate. nmap does have a library for mssql and has a script related to mssql, but it doesn't appear to check for SSL or not. My guess is you would need to build/design a custom script or find a script that will connect to an SQL Server instance AND get the ssl certificate.
My expectation is that nmap and openssl are not designed with connecting to SQL Server by default to get the certificate, so without building up a custom script or method, it isn't going to understand the response from SQL Server. One thing you could try with nmap (and likely openssl) is to do verbose output which MAY give you more details about why it isn't giving you the expected output, or it may produce nothing additional.
I know I designed a windows service that uses SSL certificates and it is designed to accept a connection request unencrypted and then ONLY reply when specific commands are sent to it on an encrypted connection. if you ran nmap or openssl against it, it would not respond because it is designed to ONLY respond to specific commands and it is HIGHLY unlikely that either of these tools send the correct commands by default. All network traffic is encrypted by SSL certificates, but unless you know the correct commands to send and the correct order to send them in, you wouldn't get a reply or you would get error responses.
If you want to know if the certificate is working or not, use the test in the second link you provided - connect with SSMS with the "encrypt connection" checkbox checked and unchecked. If you can connect with it unchecked, then it is not forcing the encryption. If you can only connect with it checked, then it is encrypting the connection.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 26, 2021 at 2:34 pm
Thanks for the inputs and comments. I will check with SSMS when I can.
I was also using nmap -Pn --script ssl-enum-ciphers to check the ciphers suites available. If there is a certificate, it will also indicate it and sslscan. (https://github.com/rbsec/sslscan)
Unfortunately, I was not able to proceed further as there were some production issues. I had only configured one of the nodes with the SSL certificate/force encryption, while the other node was not configured. Our application ended up being unstable and we were not able to connect to our portal via https; we did not find any outright error messages/trace and the SQL server logs did not show anything usual. But once remove the SSL certificate and disabled Force Encryption, everything was fine and the website loaded without any issues. Currently we suspect the issue has to do with having only one node out of two being configured.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply