How can client retrieve SQL Server public SSL certificate?

Question

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.

Answer 1

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
>

Answer 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