SQL 連線加密觀察及加密憑證檢查

SQL Server 從很早的版本便支援加密傳輸(早期還支援 SSL，SQL Server 2016 起只支援 TLS)，在安全要求較嚴謹的環境可啟用加密連線，保護傳輸內容不被竊聽或攔截(概念上是 HTTP 與 HTTPS 的差異)。(延伸閱讀：啟用資料庫引擎的加密連線 - MS Docs)

SSMS 在登入時有兩個額外選項「Encrypt connection」 及「Trust server certificate」，勾選後便可使用 TLS 加密連線：

SQL Server 安裝時已預設支援加密連線，不需額外設定，但預設用的 TLS 憑證是自我簽署憑證(Self-Signed Certificate)，故要勾選 Trust server certificate，否則會因無法驗證憑證有效性出現錯誤：

程式若要以加密連線，則需在連線字串加入 Encrypt=true，加入 TrustServerCertificate=true 可忽略憑證有效性檢查。

寫一小段 PowerShell 來驗證。先用一段方法測試直接連線，接著在連線字串增加 encrypt=true;trustservercertificate=true 改用加密連線，並透過查詢當下連線 ID (@@SPID) 在 sys.dm_exec_connections 的 encrypt_option 觀察連線是否加密：(註：sys.dm* 為 Dynamic Management Views，登入帳號必須具系統管理者權限或經 GRANT VIEW SERVER STATE TO userName / GRANT VIEW DATABASE STATE TO userName 授權，參考)

$ErrorActionPreference = 'STOP'
Add-Type -AssemblyName System.Data
Add-Type -AssemblyName System.Security
function ShowEncryptOption($cs) {
    $cn = New-Object System.Data.SqlClient.SqlConnection($cs)
    $cn.Open()
    $cmd = $cn.CreateCommand()
    $cmd.CommandText = "select encrypt_option from sys.dm_exec_connections b where session_id = @@SPID"
    $dr = $cmd.ExecuteReader()
    if ($dr.Read()) {
        Write-Host "Encrypt_Option = $($dr[0])"
    }
    $dr.Close()
    $cn.Dispose()
}
$cs = "data source=SQLSvrHost;integrated security=SSPI"
ShowEncryptOption $cs
[System.Threading.Thread]::Sleep(2000)
$cs += "encrypt=true;trustservercertificate=true"
ShowEncryptOption $cs

實測未加 encrypt & trustservercertificate 前 encrypt_option 為 FALSE，加上後 encrypt_option 變為 TRUE。

用 Wireshark 側錄觀察，我故意在未加密與加密測試間 Thread.Sleep(2000) 停了兩秒，用發生時間可識別上半部綠框部分是非加密連線，下方為加密連線。如綠箭頭所指，未加密時可看到 SQL Batch 及 Response 等明碼封包；而下方加密區在 三次 TLS Exchange 後，就都是加密內容，Wireshark 已無法解析。

檢視 SQL Batch 及 Response 封包可看到 SELECT 指令及回傳結果，加密後就看不出所以然了。

既然使用 TLS 加密，就一定會有憑證。若未額外設定，SQL Server 會使用自我簽署憑證，依據爬文結果，雖然能由 sys.certificates 得知憑證資訊，也有 BACKUP CERTIFICATE [##MS_SQLAuthenticationCertificate##] TO FILE='...' 指令，但實際操作時會卡在 User does not have permission to perform this action。因此，我們可以指定更換憑證，但無法透過 SQL 指令或 SQL 工具取得預設憑證內容。

山不轉路轉，網路有高手寫了 Python 程式模擬客戶端連上 SQL，依前面 Wireshark 觀察到的交握程序送出 TDS 協定(Tabular Data Stream Protocol) 封包啟動 TLS 加密，以草船借箭的方式拿到憑證：

如上圖所示，將程式顯示的 PEM 內容(-----BEGIN CERTIFICATE----- 與 -----END CERTIFICATE----- 那段)存成 .cer，在檔案總管開啟便可看到 SSL_Self_Signed_Fallback 憑證內容。