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 憑證內容。

Tips of how to test SQL connection encryption and check the TLS certificate.


Comments

Be the first to post a comment

Post a comment