OpenSSL: Fetching SQL Server public certificate

Question

I want to use OpenSSL or any native Linux command to grab the certificate of a SQL Server. I tried the same way as I do with an HTTP server but it doesn't work.

openssl s_client -showcerts -connect MY.MSSQL.SERVER:1433
CONNECTED(00000003)
write:errno=104
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 249 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
---

Answer 1

This gist by github user lnattrass gives a python script that is "A terrible way to connect to MS SQL Server and dump the certificate as a PEM" (his wording) in python. Yes, that's not what you asked about, you asked about OpenSSL. But one of the comments says in part

I was able to get the same results using openssl like this: openssl s_client -showcerts -connect <hostname>:<port> </dev/null 2>/dev/null|openssl x509 -outform PEM >dbcertfile.pem as suggested somewhere.

(no clue where "somewhere" would have been.)

I've tried the openssl method but it failed for me:

rpresser@11MTLDEV-L11626:~$ openssl s_client -showcerts -connect mysqlserver.mydomain.com:1433 </dev/null 2>/dev/null|openssl x509 -outform PEM >dbcertfile.pem
unable to load certificate
140246796931520:error:0909006C:PEM routines:get_name:no start line:../crypto/pem/pem_lib.c:745:Expecting: TRUSTED CERTIFICATE

Perhaps this was because the self-signed cert (see below) was not trusted? I'm really not sure.

After fixing one indentation bug, the python method worked for me:

rpresser@11MTLDEV-L11626:/mnt/c/temp$ python3 get_tds_cert.py redacted.domain.COM 1433
# get_tdspacket: 0, tdspacket len: 43
# Header:  {'type': 4, 'status': 1, 'length': 43, 'channel': 0, 'packet': 1, 'window': 0}
# Remaining tdspbuf length: 0

# Starting TLS handshake loop..
# Shaking (0/5)

# get_tdspacket: 0, tdspacket len: 894
# Header:  {'type': 18, 'status': 1, 'length': 894, 'channel': 0, 'packet': 0, 'window': 0}
# Remaining tdspbuf length: 0

# Shaking (1/5)

# get_tdspacket: 0, tdspacket len: 67
# Header:  {'type': 18, 'status': 1, 'length': 67, 'channel': 0, 'packet': 0, 'window': 0}
# Remaining tdspbuf length: 0

# Handshake completed, dumping certificates
-----BEGIN CERTIFICATE-----
MIIB+zCCAWSgAwIBAgIQYc0YElx/YYFF/Q0PIYETxDANBgkqhkiG9w0BAQUFADA7
MTkwNwYDVQQDHjAAUwBTAEwAXwBTAGUAbABmAF8AUwBpAGcAbgBlAGQAXwBGAGEA
bABsAGIAYQBjAGswIBcNMjAwMjAyMDQ0NzA5WhgPMjA1MDAyMDIwNDQ3MDlaMDsx
OTA3BgNVBAMeMABTAFMATABfAFMAZQBsAGYAXwBTAGkAZwBuAGUAZABfAEYAYQBs
AGwAYgBhAGMAazCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAohSQbug4qZug
ji16iKuNpX4OzGc9DPAORaho8LB2AGinol+rEmcTGRofeIg9zeXMbiOwWbrCnT3/
RUDLOW6V4papZ7H/qIcmVIKdzNCezWMlfPd0h4S00kVovHDIbC1t2uhcDdfEIsh+
fbgzS34bkXNACurtV5t8kpPWYDsRwqsCAwEAATANBgkqhkiG9w0BAQUFAAOBgQBr
qfAph+/NF6Cgxisp7UHq9kjQ6sYNCIXfq9mJnRqX+I8H6nxSQfpDlljdvKN3GYeg
SL4jowNL11z5xjpJS9/KhLFwodicWKt0Go/CqusPWJKVJo0HgIn9a1hHPipRbR8w
3+QRy50kaPXm5VOoSg83+CjEg9ri7jfgtWLetq+xoQ==
-----END CERTIFICATE-----

I haven't bothered to redact the certificate because it is the SQL Server self-signed fallback, as displayed by SSLShopper Certificate Decoder

• Certificate Information:
• Common Name: SSL_Self_Signed_Fallback
• Valid From: February 1, 2020
• Valid To: February 1, 2050 Serial Number: 61cd18125c7f618145fd0d0f218113c4