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:
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.
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
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 SSL TLS v1.2. For this part, I can't hard code the static array like earlier as I need to call System.Net.Security.SslStream.AuthenticateAsClient which will indirectly calls System.IO.Stream over TCP. So there is an extra class called TdsTlsStream that helps me to perform read() and write() over the TCP network communication. Inside this class, 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.
System.Net.Security.SslStream.AuthenticateAsClient is the main call that will obtain SSL certificate from remote MS SQL Server. Anything that is wrong in the program will leads to timeout, then remote MS SQL Server will close the network connection. If it success, then the program will be able to extract the SSL certificate using System.Security.Cryptography.X509Certificates.X509Certificate2.
You can further modify the program to add following line to save the SSL certificate as a file locally:
E.g. Export-Certificate -Cert $<yr variable name> -FilePath db_certificate_DER_binary.cer -Type CERT
Above command will create a new SSL certificate file called db_certificate_DER_binary.cer in current directory
[CmdletBinding()]
param()
# Define your SQL Server instance details, default port 1433
$SqlServer = "your-ms-sql-server-hostname-or-IP"
$SqlPort = 20277
# Enforce TLS 1.2 for the handshake protocol selection
$TlsVersion = [System.Security.Authentication.SslProtocols]::Tls12
# --- SQL Server Pre-Login Handshake Protocol Payload ---
# This standard 8-byte TDS header tells SQL Server a client login attempt is starting.
[byte[]]$TdsPreLoginPacket = 0x12, 0x01, 0x00, 0x2F, 0x00, 0x00, 0x01,0x00, 0x00, 0x00, 0x1A, 0x00, 0x06, 0x01,0x00, 0x20,0x00, 0x01, 0x02, 0x00, 0x21, 0x00, 0x01,0x03, 0x00, 0x22, 0x00, 0x04, 0x04, 0x00,0x26, 0x00,0x01, 0xFF, 0x09, 0x00, 0x00, 0x00, 0x00,0x00, 0x01, 0x00, 0xB8, 0x0D, 0x00, 0x00,0x01
# Create a TCP Client to connect to the SQL port
$TcpClient = New-Object System.Net.Sockets.TcpClient
try {
$TcpClient.Connect($SqlServer, $SqlPort)
$TcpClient.SendTimeout = 10000;
$TcpClient.ReceiveTimeout = 10000;
$NetworkStream = $TcpClient.GetStream()
$NetworkStream.Write($TdsPreLoginPacket, 0, $TdsPreLoginPacket.Length)
# 3. Read the server's TDS Pre-Login Response Header
$responseHeader = New-Object byte[] 8
$bytesRead = $NetworkStream.Read($responseHeader, 0, $responseHeader.length)
if ($bytesRead -lt $responseHeader.length) {
Write-Error "PRELOGIN - $bytesRead bytes read, but expects 8 bytes"
throw "Failed to read a valid PRELOGIN TDS header response"
}
$respPayloadLength = ($responseHeader[2] -shl 8) + $responseHeader[3] - $responseHeader.length
Write-Host "--- TDS HEADER RECEIVED ---" -ForegroundColor Cyan
Write-Host "Raw Bytes Header: " ($responseHeader -join ' ')
Write-Host "Remaining Payload Length: $respPayloadLength bytes" -ForegroundColor Cyan
# Continue to read the token data and data
if ($respPayloadLength -gt 0) {
$responsePayload = New-Object byte[] $respPayloadLength
$bytesRead = $NetworkStream.Read($responsePayload, 0, $respPayloadLength)
$intEndofTokenHeader = [array]::IndexOf($responsePayload, [byte]0xff)
$intEndofTokenData = $intEndofTokenHeader + 1
Write-Host "Finished reading payload: $respPayloadLength bytes" -ForegroundColor Cyan
Write-Host "Offset of FF terminator: " $intEndofTokenHeader -ForegroundColor Cyan
Write-Host "Raw Bytes: " ($responsePayload -join ' ') -ForegroundColor Gray
Write-Host "Raw Bytes Token Header: " ($responsePayload[0..$intEndofTokenHeader] -join ' ') -ForegroundColor Gray
Write-Host "Raw Bytes Token Data: " ($responsePayload[$intEndofTokenData..($respPayloadLength - 1)] -join ' ') -ForegroundColor Gray
}
$isServerEncrypted = 0
$posPayload = 0
while ($posPayload -lt $intEndofTokenHeader) {
$plOptionType = $responsePayload[$posPayload]
Write-Host "PRELOGIN respond header - Position $posPayload=$plOptionType"
# Exit loop if reach header terminator character 0xFF
if ($plOptionType -eq 0xFF) {
Write-Host "PRELOGIN respond header - Reached end of header"
break
}
elseif ($plOptionType -ne 1) {
$posPayload += 5
Write-Host "PRELOGIN respond header - Skipping 5 bytes to offset $posPayload"
continue
}
# Calculate the address of encryption byte
$valueOffset = [System.BitConverter]::ToUInt16([byte[]]@($responsePayload[$offset + 2], $responsePayload[$offset + 1]), 0)
Write-Host "PRELOGIN respond header - Found encryption byte in offset address $posPayload with value $valueOffset"
$isServerEncryptedRaw = $responsePayload[$valueOffset]
$isServerEncrypted = $isServerEncryptedRaw -band 0x0F
Write-Host ("PRELOGIN respond header - offset $valueOffset's encryption byte value = {0} (0x{0:X2}) -> {0} (0x{1:X2})" -f $isServerEncrypted,$isServerEncrypted ) -ForegroundColor Cyan
break
}
Write-Verbose "Entryption is supported, so it is possible to get SSL cert. $isServerEncrypted is not 0 means DB server will perform encryption"
Write-Verbose "Setup TLS stream"
$tlsStreamToWrap = [TdsTlsStream]::new($NetworkStream)
# Wrap the network stream in an SslStream object for the TLS handshake
# We pass a callback to automatically trust self-signed SQL dev certificates
Write-Verbose "Setup SSL stream inside TLS stream"
$SslStream = New-Object System.Net.Security.SslStream($tlsStreamToWrap, $false, {
param($sender, $certificate, $chain, $sslPolicyErrors)
# Always return true to capture the certificate even if untrusted/self-signed fallback
return $true
})
Write-Host "SslProtocol negotiated #1: $($sslStream.SslProtocol)"
# Authenticate as client to trigger the handshake
Write-Verbose "Specified SSL to use $TlsVersion, and obtain remote DB server's SSL cert"
$SslStream.AuthenticateAsClient($SqlServer, $null, $TlsVersion, $false)
Write-Host "SslProtocol negotiated #2: $($sslStream.SslProtocol)"
# Extract the server certificate
$ServerCertificate = [System.Security.Cryptography.X509Certificates.X509Certificate2]($SslStream.RemoteCertificate)
if ($ServerCertificate) {
Write-Host "`n=== SQL Server TLS 1.2 Certificate Details ===" -ForegroundColor Green
$ServerCertificate | Format-List Subject, Issuer, SerialNumber, Thumbprint, NotBefore, NotAfter
# Verify if it is the fallback certificate
if ($ServerCertificate.Subject -like "*SSL_Self_Signed_Fallback*") {
$Host.PrivateData.VerboseForegroundColor = 'Cyan'
Write-Verbose "This is the internal SQL Server Self-Signed Fallback Certificate, so it is in RAM, and will change after DB server start/restart/reboot"
}
} else {
Write-Warning "Successfully connected with TLSv1.2, but no remote certificate was returned by the server."
}
} catch {
$lineText = $_.InvocationInfo.Line
$lineNumber = $_.InvocationInfo.ScriptLineNumber
$position = $_.InvocationInfo.PositionMessage
Write-Host "❌ Error occurred on line $lineNumber" -ForegroundColor Red
Write-Host "Code block: $lineText" -ForegroundColor Yellow
Write-Host "Position details:`n$position"
} finally {
# Properly close and dispose of connections
if ($SslStream) { $SslStream.Dispose() }
if ($TcpClient) { $TcpClient.Close(); $TcpClient.Dispose() }
}
class TdsTlsStream : System.IO.Stream {
[System.IO.Stream]$InnerStream
[int]$PayloadLength = 0
TdsTlsStream([System.IO.Stream]$InnerStream) {
$this.InnerStream = $InnerStream
}
[bool] get_CanRead() { return $this.InnerStream.CanRead }
[bool] get_CanWrite() { return $this.InnerStream.CanWrite }
[bool] get_CanSeek() { return $this.InnerStream.CanSeek }
[Int64] get_Length() { return $this.InnerStream.Length }
[Int64] get_Position() { return $this.InnerStream.Position }
[void] set_Position([Int64]$Value) { $this.InnerStream.Position = $Value }
[int] get_ReadTimeout() { return $this.InnerStream.ReadTimeout }
[int] get_WriteTimeout() { return $this.InnerStream.WriteTimeout }
[void] Flush() { $this.InnerStream.Flush() }
[Int64] Seek([Int64]$Offset, [System.IO.SeekOrigin]$Origin) { return $this.InnerStream.Seek($Offset, $Origin) }
[void] SetLength([Int64]$Value) { $this.InnerStream.SetLength($Value) }
[int] Read([byte[]]$Buffer, [int]$Offset, [int]$Count) {
# Removes TDS header before setting the network Buffer
if ($this.PayloadLength -eq 0) {
$tlsHeader = [byte[]]::new(8)
$read = 0
while ($read -lt 8) {
$read += $this.InnerStream.Read($tlsHeader, 0, 8)
}
$lengthBeforeHeader = [System.BitConverter]::ToUInt16([byte[]]@($tlsHeader[3], $tlsHeader[2]), 0)
$lengthBeforeHeader -= 8
$this.PayloadLength = $lengthBeforeHeader
}
if ($Count -gt $this.PayloadLength) {
$Count = $this.PayloadLength
}
$read = $this.InnerStream.Read($Buffer, $Offset, $Count)
$this.PayloadLength -= $read
return $read
}
[void] Write([byte[]]$Buffer, [int]$Offset, [int]$Count) {
$newPayload = $this.GenerateTdsHeader($Buffer, $Offset, $Count)
$this.InnerStream.Write($newPayload, 0, $newPayload.Length)
}
[byte[]] GenerateTdsHeader([byte[]]$Payload, [int]$Offset, [int]$Count) {
# The length is big endian encoded so it is inserted in reverse order
$lengthBytes = [System.BitConverter]::GetBytes([uint16]($Count + 8))
$newPayload = [byte[]]::new(8 + $Count)
$newPayload[0] = 0x12 # Type - Pre-Login
$newPayload[1] = 0x01 # Status - End of message (EOM)
$newPayload[2] = $lengthBytes[1]
$newPayload[3] = $lengthBytes[0]
$newPayload[4] = 0 # SPID
$newPayload[5] = 0 # SPID
$newPayload[6] = 0 # PacketID
$newPayload[7] = 0 # Window
[System.Array]::Copy($Payload, $Offset, $newPayload, 8, $Count)
return $newPayload
}
}
