PowerShell - Getting Remote MS SQL Server 2017 SSL Certificate

Product: Microsoft MS SQL Server
Version: 2017

Overview

There are few scenarios where you would like to download the SSL certificate and save as file while working with MS SQL Server client or server programs.  I come into situation where the old version of IBM Cognos Analytics required to add remote MS SQL Server's self-signed SSL cert into its own Java keystore.  The challenge is that the MS SQL Server does not has any self-signed SSL cert, and it dynamically creates a SSL cert (MS called it as fallback SSL cert) in memory during MS SQL Server startup.  So it is not possible to save the certificate file.

Following are few more scenarios where you might want to save the remote MS SQL Server's SSL cert:

1. MS SQL Server DBA does not has Windows admin privilege to run Windows Certificate Manager (certmgr.msc) to save the SSL cert
2. Windows admin is not familiar with Windows Certificate Manager (certmgr.msc) and not able to provide the SSL cert
3. MS SQL Server DBA does not know anything about SSL fallback cert, when MS SQL Server does not has any SSL cert

The MS SQL Server API Microsoft.Data.SqlClient in any programming, e.g. Visual C#, Visual C++, Java, Python, Perl, is not possible to obtain the SSL cert.

openssl.exe utility has drop MS SQL Server TDS (Tabular Data Stream) support, so it is not possible to use this utility to get the SSL cert.

As the result, I develop a PowerShell program that uses low level TCP network communication that can download SSL certificate from local or remote MS SQL Server through TCP/IP protocol.

Technical Design

Low level TCP programming uses PowerShell System.Net.Sockets.TcpClient.  MS SQL Server login is always SSL encrypted.  It contains 2 phase, which is called PRELOGIN and LOGIN7.  During PRELOGIN phase, MS SQL Server will provide SSL certificate to the DB client program, which then can proceeds with LOGIN7 phase that will login to MS SQL Server with username and password (assumes DB authentication, not Windows user).

The most challenging part is research MS SQL Server PRELOGIN protocol, as this is poorly documented by Microsoft.  You can find the official document from learn.microsoft.com below:

Title: Open Specifications > Protocols > SQL Server Protocols > Technical Documents > [MS-SSTDS]: Tabular Data Stream Protocol Version 4.2 > Messages > Message Syntax > Packet Header Message Type Stream Definition > PRELOGIN
Ref: https://learn.microsoft.com/en-us/openspecs/sql_server_protocols/ms-sstds/75e62f67-f057-4d46-82b3-6920fe0ebada

The PowerShell program will make use of hex and binary to convert the PRELOGIN data structure back and forte with remote MS SQL Server server.

While reading MS' document, we need to ignore any document that tell encryption is optional, as well as TLS v1.2 is optional.  In fact, many versions of MS SQL Server have long enforced TLS v1.2, and encryption during login.  So there are many obsolete content which confuses reader like us.  The login sequence is called as Tabular Data Stream with short name of TDS.  My program is written for MS SQL Server 2017, which is using TDS v7.4, but newer MS SQL Server uses TDS v8.0.  So this program might not work with newer MS SQL Server due to a change in PRELOGIN phase.

My program set the encryption as 0x01, which in MS' doc called "Encryption is available and on."  Again, all MS SQL Server support SSL cert, so you can safely accept encryption is available.

Some variables in my program call "stream" which represent TCP network communication.  At the beginning of the low level TCP network connection, a variable of NetworkStream is created against TcpClient connection.  This is to facilitate back and fort communication with remote MS SQL Server.

Soon after open TCP connection, my program will send a static PRELOGIN to remote MS SQL Server to tell it that my program needs to login.  This part of the communication is not encrypted, and in clear text. Don't simply change this static PRELOGIN array value, as 2 of the bytes (2 array elements) contains number of bytes inside this array.  You can refer to MS doc for exact array element, if you want to change this array.

After that, MS SQL Server will reply back to tell my program its MS SQL Server version, whether it supports encryption (it will be always supported), number of bytes it will send back.  My program will retrieve this reply (MS doc called message or message stream) using 2 separate steps (command NetworkStream.Read()).  The reason is because the 1st part is a constant 8 bytes, which I called "payload header" while MS doc implies it is payload as well, with short name of PL.  My program then find out number of bytes remote MS SQL Server going to send to me, and in the 2nd part, my program will read the remaining reply (message stream).  I called the 2nd part of it as "prelogin payload option token" and "prelogin payload data."  MS doc is not clear about how they called them.  For my program, I mainly determine the remote MS SQL Server will tell my program that it accept my PRELOGIN request, and it does support SSL encryption.  This is very important as SSL cert will only be send to my program if it replied that it supports SSL encryption.  This will double confirm new MS SQL Server standard that LOGIN7 is always SSL encrypted.

The next part is the most challenging part as I need to continue to talk to MS SQL Server using TDS header structure, while enforcing TLS v1.2 as the minimum standard.  For this part, I can't hard code the static array like earlier.  So there is an extra class called TdsTlsStream that helps me to perform read() and write() to the TCP network communication.  Inside this class, you often to see in read() function, it will read first 8 bytes to determine network packet length, and in write() function, it will create extra 8-bytes packets to insert before sending over the data to remote MS SQL Server.