17

Can a user retrieve the connection certificate for an SQL Server instance (similar to how someone can retrieve an HTTP certificate)? If so, how?

Context

If encryption is requested by the SQL Server client, the server certificate must be validated before allowing an encrypted connection. Development servers are often configured with self-signed certificates, which won't validate under default client setup.

To use a self-signed certificate to encrypt a connection, either:

  • the certificate must be added to the local store
  • the client must set "TrustServerCertificate=true"
  • the client must not request encryption, and the server must set "ForceEncryption" to "yes".

The latter two options are open to MitM attacks every time a connection is made. For the 2nd, some clients don't save the connection setting, so it must be re-set for each new connection. Thus the most desirable solution is to add the certificate to the local store (it takes a little more work initially, but after that is easiest & safest option). For users in a domain, the certificate is easy enough to distribute, but otherwise the certificate will need to be installed manually on each computer. An admin could provide the certificate to each user, either by sending directly or making it available as a network resource, but sometimes the admin may be too busy or unresponsive for other reasons to provide the certificate.

In such a case, it would be useful if the user could get the certificate from the SQL server directly, just as a certificate can be retrieved from an HTTP server using a browser or openssl s_client. This would still be vulnerable to a MitM attack when the certificate was retrieved, but provides a much narrower window than trusting the certificate every time there's a new connection.

3
  • hi outis and @Abacus did you every solve this or got more detail how to retrieve/check SQL cert from client?
    – TiloBunt
    Commented Jun 15, 2017 at 0:15
  • 2
    here Q from stackoverflow.com/q/34981859/1747983
    – TiloBunt
    Commented Jun 15, 2017 at 22:15
  • This question already has an answer at the Q from SO mentioned by @TiloBunt. here, here
    – minus one
    Commented Feb 27, 2021 at 10:00

2 Answers 2

4

The following nmap command will return (eventually) the certificate for SQL server at <address>:

nmap -sV -p <port> -vv --script ssl-cert <address>

Example output below. Certificate data is in the block of lines prefixed by "|":

C:\Users\admin
> nmap -sV -p 1433 -vv --script ssl-cert localhost
Starting Nmap 7.92 ( https://nmap.org ) at 2023-07-09 12:40 Pacific Daylight Time
NSE: Loaded 46 scripts for scanning.
NSE: Script Pre-scanning.
NSE: Starting runlevel 1 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.00s elapsed
NSE: Starting runlevel 2 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.00s elapsed
Warning: Hostname localhost resolves to 2 IPs. Using 127.0.0.1.
Initiating Parallel DNS resolution of 1 host. at 12:40
Completed Parallel DNS resolution of 1 host. at 12:40, 0.01s elapsed
Initiating SYN Stealth Scan at 12:40
Scanning localhost (127.0.0.1) [1 port]
Discovered open port 1433/tcp on 127.0.0.1
Completed SYN Stealth Scan at 12:40, 0.00s elapsed (1 total ports)
Initiating Service scan at 12:40
Scanning 1 service on localhost (127.0.0.1)
Completed Service scan at 12:40, 11.03s elapsed (1 service on 1 host)
NSE: Script scanning 127.0.0.1.
NSE: Starting runlevel 1 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.01s elapsed
NSE: Starting runlevel 2 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.02s elapsed
Nmap scan report for localhost (127.0.0.1)
Host is up, received localhost-response (0.00s latency).
Other addresses for localhost (not scanned): ::1
Scanned at 2023-07-09 12:40:36 Pacific Daylight Time for 11s

PORT     STATE SERVICE  REASON          VERSION
1433/tcp open  ms-sql-s syn-ack ttl 128 Microsoft SQL Server
| ssl-cert: Subject: commonName=SSL_Self_Signed_Fallback
| Issuer: commonName=SSL_Self_Signed_Fallback
| Public Key type: rsa
| Public Key bits: 3072
| Signature Algorithm: sha256WithRSAEncryption
| Not valid before: 2023-07-09T19:40:27
| Not valid after:  2053-07-09T19:40:27
| MD5:   d30a edf3 1c60 a62a 9d37 4b8a 6aaf 0d88
| SHA-1: fbd8 175e f3cc fc36 129b 9f43 daff 35a0 441f 98d0
| -----BEGIN CERTIFICATE-----
| MIIEADCCAmigAwIBAgIQNRDgq8iGZYZAaojcISa7rDANBgkqhkiG9w0BAQsFADA7
| MTkwNwYDVQQDHjAAUwBTAEwAXwBTAGUAbABmAF8AUwBpAGcAbgBlAGQAXwBGAGEA
| bABsAGIAYQBjAGswIBcNMjMwNzA5MTk0MDI3WhgPMjA1MzA3MDkxOTQwMjdaMDsx
| OTA3BgNVBAMeMABTAFMATABfAFMAZQBsAGYAXwBTAGkAZwBuAGUAZABfAEYAYQBs
| AGwAYgBhAGMAazCCAaIwDQYJKoZIhvcNAQEBBQADggGPADCCAYoCggGBAMlSFIA9
| JirELvYhQpGOAKd0Fanxnj5YGIlpQAjjUQ7vCSinRnfKm6WXq6sKcgLaTZv71Uyg
| 5Ru3ImEwPmRtTaYCwU46gGj1y2mtoRBkgMm8QeIhNwUigeWrgrUPygmgpAkkOLg0
| R+/5ZGK2oWu9xcrCQugpK/zxiDfarIdvS+UMuHEK+JTMOtkGr8Sa1hxif3qixWGc
| lfHPWV3nA67AWdsXCjDYTJUFFlrzH54JMcFiXKoYs+lQqD+ompwUv3GtRaIh51rt
| x34mOerlzporw5smda0IPf02Vrd0WLn4NI4ZcmZhykht7kLb+qZB6CU8Epy4YXj8
| atS4ocsS3hNFTPvKRMo/21DWqms1+jzEY/OhgCX9BoSDSzw1MPnXxRyel3gpPpvD
| Stx5j0Olyo4L0IwVi9gKfC7Ft07RFBoFoCo8+rN9h4Bab0AD2lpQWPhoCOpo7PdS
| 9POuBrD3oSPzYhPjvieuc5Pet+bRn09yWvw1FOea5ZhCeRcL5/Ybb+uX/QIDAQAB
| MA0GCSqGSIb3DQEBCwUAA4IBgQBhv/Ljm6ExDoONsPAGQBOIFspYyrHsQJFwbkUI
| As0YVjlNaV7gOa9RaiXfhno2VX6dRuRKPnt6bdzH0uHiZlWdbh+Tevn4i6oZx2Nr
| OoCyXLw2zJakfdjtwop1b3rLBRF3BSt4EeFwJBMBXzl1R37mbJmHhIyl1xj781Pb
| rlh0o9s42Ka53oEm2SGg2kJBC7WcukxB4c9UN7ytBi9TIxuLzVECmob0PpXurP7i
| eKl9WT+X99EastZ3ETuiA9dszzZGGVJuFBcV9luuI+hz+Q4gST1tHS+Pvyugl+qk
| WuJJjsdlVCVuOmJfnzK5DOWh53srQWvfNTiXLlvIgl9eBuGTef2Mirq+7ek90Xux
| asgddwo/0qRqmOpr9W9/Dl4ZTZKgZwBsFnXaOsqljjxfXf00lZhLFFF0RaZkPmeG
| Ex38/KOO0QvQS7/7SaZJoh77axkP5j93N8DwbIsUEXxFSdb9dxaMlDz0G8F145KW
| 3TUHZNnmEyqksG90eMOUanGJYwI=
|_-----END CERTIFICATE-----
1 service unrecognized despite returning data. If you know the service/version, please submit the following fingerprint at https://nmap.org/cgi-bin/submit.cgi?new-service :
SF-Port1433-TCP:V=7.92%I=7%D=7/9%Time=64AB0D3F%P=i686-pc-windows-windows%r
SF:(ms-sql-s,25,"\x04\x01\0%\0\0\x01\0\0\0\x15\0\x06\x01\0\x1b\0\x01\x02\0
SF:\x1c\0\x01\x03\0\x1d\0\0\xff\x10\0\x03\xe8\0\0\0\0");
Service Info: OS: Windows; CPE: cpe:/o:microsoft:windows

NSE: Script Post-scanning.
NSE: Starting runlevel 1 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.01s elapsed
NSE: Starting runlevel 2 (of 2) scan.
Initiating NSE at 12:40
Completed NSE at 12:40, 0.00s elapsed
Read data files from: C:\Program Files (x86)\Nmap
Service detection performed. Please report any incorrect results at https://nmap.org/submit/ .
Nmap done: 1 IP address (1 host up) scanned in 11.47 seconds
           Raw packets sent: 1 (44B) | Rcvd: 2 (88B)

C:\Users\admin
>
5
  • I ran this exact command which succeeded but did not appear to output anything that I could discern as a public certificate. Is there an extra parameter necessary or does the certificate get stuck into some file based report somewhere? I more detailed answer on where to find the certificate after running the command would be useful.
    – jpierson
    Commented Jul 7, 2023 at 12:29
  • Example output added. You could pipe it through sed, for example, to get a readable certificate file: nmap -sV -p <port> -vv --script ssl-cert <address>| sed -En "s/^\|.//p" > cert.crt # Still not seeing a certificate in your output? ¯\_(ツ)_/¯
    – gdeff
    Commented Jul 9, 2023 at 20:15
  • I didn't see that in my output. I'll run it again and double check things. I wonder if the behavior is different if certificate is installed and encryption is enabled but not forced? Thanks for the example.
    – jpierson
    Commented Jul 11, 2023 at 13:16
  • there's just some cruft in the nmap output, just need to clean up the indents and pipes. nmap's tool is the only one that seems to support this over SQL protocol, sslscan just explodes
    – Coruscate5
    Commented Jul 14, 2023 at 20:07
  • A normal cert should be like ---- BEGIN CERTIFICATE ---- [base64data] ---- END CERTIFICATE ---- - I just copied over to notepad from nmap to clean up the formatting and verify the encoding
    – Coruscate5
    Commented Jul 14, 2023 at 20:10
2

Technically it is possible, both TDS and TLS/SSL are documented. But you are asking the wrong question.

There is no point in willy-nilly manually installing a self-signed certificate everywhere, it buys you nothing. You will only install the MitM's cert everywhere...

Focus on deploying a properly configured PKI infrastructure. Either use a trusted certificate (ie. Verisign et all), or install a PKI trust in your network and use a infrastructure signed certificate.

See this series: Designing and Implementing a PKI

1
  • 19
    I disagree -- there is one very good purpose to manually getting a certificate of a SQL server, which is to look at it. In my case, I want to see what domain(s) it contains, and have no easy way (that I know of) to just grab the cert from the server. All major browsers give you this easy and useful tool; if it was not useful, browsers would not have it either.
    – Abacus
    Commented Dec 8, 2016 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.