[SQL]實作資料庫連線加密

2021-05-14

剛好遇到有朋友的資料庫被稽核說沒有使用加密連線，因此整理一下相關設定的步驟與測試過程

。

最近遇到一個需要針對 SQL Server 連線加密的處理，以前大致知道有相關設定，但並沒有真正去實作證明，剛好遇到客戶被稽核需要處理這一段，因此就把相關步驟整理一下，過程中也謝謝許多 SQL Pass 的朋友們，提供各種寶貴的建議和經驗，讓我可以更清楚這中間的處理。

設定只允許 TLS 1.2

有些時候我自己很懶得去背要改那些機碼，都常都會用 NARTAC Software 這間公司所出的 IIS Crypto ，來設定伺服器只允許 TLS 1.2 的加密，有些朋友看到這個程式的名稱，會以為這個工具只能針對 IIS 來使用，其實這個工具主要會幫你修改 Windows 的機碼，因此您也可以把這個工具拿來 SQL Server 的主機上來使用，當然您的前提是你的 SQL Server 是安裝在 Windows 的作業系統上。

我個人是覺得如果是新版本的 Windows , 基本上有沒有設定差異不大，但為了避免被稽核到有缺失，是可以把 SSL 和 TLS 1.0 , 1.1 的通訊協定給關閉，只留下 TLS 1.2，這樣也不用去解釋太多啦。

建立憑證提供給 SQL Server 使用

早期我們通常都會用 makecert 的工具程式來建立私有憑證，但目前那個工具目前已經被取代，改用 Power Shell 的 New-SelfSignedCertificate 指令來處理，本來想研究一下看這個要怎麼來使用，剛好在網路上看到有人已經有寫好現成的範本 ( 網址 )，那就直接拿來使用囉。

# Create Self Signed RSA Cert for SQL Server usage
# 
# Customize:
# + -Subject should contain hostname (or virtal name for FCI)
# + -FriendlyName is anything which helps you to recognize the key
# + -DnsName should list all variants (FQDN) of hostnames used by clients (VIP+Machines)
# + -NotAfter set expire accoring to your policy
# + (Non)Exportable is more secure but harder to manage
#
# - Using RSASSA-PSS (-AlternateSignatureAlgorithm) does not work with Java 8 clients:
#    Caused by: java.security.NoSuchAlgorithmException: 1.2.840.113549.1.1.10 Signature not available
# - Using CNG (Software KSP, Platform KSP) does not work with SQL Server
# - No ECDSA possible since CNG KSP is used
# - sets extended key usage id-kp-serverAuth
New-SelfSignedCertificate -Type SSLServerAuthentication `
    -Subject "CN=$env:COMPUTERNAME" -FriendlyName 'SQL Server RSA2048 G1' `
    -DnsName "$env:COMPUTERNAME",'localhost.' `
    -KeyAlgorithm 'RSA' -KeyLength 2048 -Hash 'SHA256' `
    -TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' `
    -NotAfter (Get-Date).AddMonths(36) `
    -KeyExportPolicy NonExportable -KeySpec KeyExchange `
    -Provider 'Microsoft RSA SChannel Cryptographic Provider' `
    -CertStoreLocation Cert:\LocalMachine\My `
| fl -Property Thumbprint,FriendlyName,DnsNameList,NotAfter,PrivateKey,SerialNumber,Subject,Issuer

Write-Warning 'You need to open MMC "Manage Machine Certificates", select new cert in "Personal > Certificates"'
Write-Warning 'and specify "All Tasks > Manage private Keys...". Add MSSQL service login (NT Service\MSSQL$INST) with READ.'

這個腳本實在太棒了，不只幫我省掉找參數的麻煩，還把後續步驟也都寫出來了，實在是太佛心了。因此用上述指令建立好憑證之後，我們就參考他的說明，用 MMC 來管理憑證囉。我先在命令列下輸入 MMC ，並且加入憑證的管理

就可以在個人→憑證的目錄下面，看到剛剛前面用 Power Shell 所建立的憑證。接著選擇所有工作→管理私密金鑰

此時要透過這個功能，來調整剛才新建立憑證的權限，要讓 SQL Server 服務的使用者，可以來讀取這個憑證

設定 SQL Server 使用憑證進行加密

首先我們需要開啟 SQL Server 的組態管理員來進行設定

選擇 SQL Server 網路組態 → MSSQLSERVER 的通訊協定，按下滑鼠右鍵選擇「內容」，就可以到「憑證」的頁簽下進行設定

此時我們可以從下拉選項中挑選到剛剛設定好權限的憑證，就可以挑選進來使用了。

此時設定好之後，就可以把 SQL Server 服務給重新啟動，這樣通訊協定就可以透過該憑證來進行加密處理 (備註一)

驗證加密效果

基本上這裡有很多種工具可以用，但測試幾種下來各有各的優缺點，加上我自己對相關底層技術沒有那麼熟練，因此在 Cary Hsu 的建議下，直接使用微軟所提供的工具程式「Microsoft Network Monitor 3.4」

進入後我避免攔到太多的封包，因此我在 Server 選執行這個工具程式的時候，選擇「Capture Settings」，限制只抓取 Port 1433 的在某一張特定的網站上的資料，這樣就不會看到一堆的封包資訊搞得頭昏，確定好之後按下「Apply」確認使用這個規則後，就可以關閉該視窗

此時我們就可以按下「Start」的選項來開始攔截封包了

此時我利用 SSMS 來連接該資料庫，並且透過查詢視窗送出一個命令

select encrypt_option from sys.dm_exec_connections where session_id = @@spid

畢竟是微軟自己家的工具，因此執行之後我們就可以錄到一些封包，可以從 1 的部分，看到他可以很清楚的告訴你是哪個 SQL Session ，我們也可以從 2 的部分，看到我們有送出一個 SQL 的命令，然後對照 3 的部分，也可以很清楚的看到我們所送出的指令。除了這個部分，也可以從下一個封包，看到所回傳的資料。

確認資料加密

相信眼尖的朋友，在前面的步驟中應該會發現一個問題，雖然我們實作了相關的處理，但是實際的 SQL 指令和資料封包，幾乎都是裸奔的狀況，全部都是明碼的資料啊，那我們剛剛設定那些是有用的嗎 ? 這裡我們先用 SSMS 來做測試，原本我們直接連線的狀況就如同之前的步驟，可以很容易看到指令和資料，但此時如果我們連線的時候，在選項裡面加入「加密連線」和「信任伺服器憑證」(註二)

當採用這樣的連線之後，我們再重新連線，並且使用相同的指令，重新去錄製一次封包來看看結果，從下圖 1 的地方，會看到這裡資料頭的地方，資料的代號變成了 17 03 03 (備註三)，而在 2 的地方，原本可以看到完整的指令，現在就只能看到一堆沒有辦法理解的符號了，因此看起來這樣的加密設定是有效果的了。

應用程式調整

如果程式採用 ADO.Net 的連線，則可類似以下的範例程式，在連線字串中加入「Encrypt=True;TrustServerCertificate=True;」的參數(註二) ，則連線就會自動產用加密處理來進行了。

            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = "Data Source=10.40.83.81;User Id=帳號;Password=密碼;Encrypt=True;TrustServerCertificate=True;"; 
                SqlCommand command = new SqlCommand("select encrypt_option from sys.dm_exec_connections where session_id = @@spid",
                  connection);

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}", reader.GetString(0) );
                    }
                }
            }

但如果程式是採用 ADO 或者是 OLE DB 的連線的時候，我用以下的方式來進行測試，不論是用哪一種的 Provider，連線都沒有辦法採用加密處理

Dim conn 
Dim sql
Dim rs
sql = "select encrypt_option from sys.dm_exec_connections where session_id = @@spid"
Set conn = CreateObject("ADODB.Connection")
   With conn
'     .ConnectionString = "Provider=SQLOLEDB.1;Data Source=主機;User ID=帳號;Password=密碼;Encrypt=True;TrustServerCertificate=True"
'     .ConnectionString = "Provider=SQLNCLI11.1;Data Source=主機;User ID=帳號;Password=密碼;Encrypt=True;TrustServerCertificate=True"
      .ConnectionString = "Provider=MSOLEDBSQL;Data Source=主機;User ID=帳號;Password=密碼;Encrypt=True;TrustServerCertificate=True"
      .Open 
   End With

   Set rs = CreateObject( "ADODB.Recordset" )  
   rs.Open sql, conn, adOpenForwardOnly  
   WScript.Echo rs(0)
   rs.close()
   WScript.Sleep 10000
conn.Close
Set conn = Nothing
Set rs = Nothing
WScript.Echo "Connection was closed."

此時只能使出殺手鐧了，透過 SQL Server 伺服器管理員，設定連接 SQL Server 要採用「強制加密」，這樣上述不管用甚麼樣的 Provider，連接 SQL Server 的時候都會採用加密連線的方式來進行了。

備註

一、基本上這個因為我們是 Lab，因此會採用私有憑證，如果是正式環境，則可以考慮買一個公開憑證。但如果是測試，其實如果沒有先產生一個憑證給 SQL Server 來使用，他也是會自己產生一個臨時憑證的作加解密處理。

二、因為我們是用私有憑證，所以對 Client 端來說連接 SQL Server 會無法驗證那個憑證的正確性，因此需要多加上 TrustServerCertificate 的屬性，但如果您的 SQL Server 主機是採用公開憑證，那麼就可以不用設定這個參數了。

三、參考Jilo 所提供的資訊，找到一篇文章來說明資料標頭的資訊，網址 https://www.netmeister.org/blog/tcpdump-ssl-and-tls.html

0x0300	SSL v3
0x0301	TLS 1.0
0x0302	TLS 1.1
0x0303	TLS 1.2
0x0304	TLS 1.3

回首頁

五餅二魚工作室

James Fu 的技術學習之路

[SQL]實作資料庫連線加密

系列文章

標籤雲