MSSQL 2022 如何創建 自簽署SSL憑證證書 導入並啟用加密連接

繼前幾篇

• 第一篇：MSSQL 2022 在 Win11 安裝圖文教學
• 第二篇：MSSQL 2022 如何設定防火牆讓其他電腦連接
• 第三篇：MSSQL 2022 如何設定建立使用者、資料庫與表格

已經是可以用的狀態了，補上最後一偏安全的問題 SSL證書，這個一般是需要花錢購買的，由第三方CA機構簽名才能把自己的域名發布到公共信任清單上。本篇測試用的用自簽署證書來測試整個流程。

以下總共分成下列幾大項目

1. 創建SSL證書
2. 在伺服器上安裝私鑰
3. 設置伺服器端證書權限
4. 在SQLSERVER中啟用SSL證書
5. 在客戶端上安裝公鑰
6. 測試加密狀態

本篇是完全手動最麻煩的教學攻略，要快速建造SSL直接用PowerShell是最快的
詳細請參考這一篇：CHG: MSSQL 2022 快速建構 自簽署SSL證書

快速建構篇是以本篇已經完成後的狀態去做的，不過基本也包含本篇8程的內容了，唯一缺少的是【4. 在SQLSERVER中啟用SSL證書】，如果要偷吃步直接跳記得這個步驟要補上

0. 安裝環境 MSYS2 與 OpenSSL

安裝這個是因為上面有現成的 OpenSSL 可以用，這個要在Windows上官方沒有給編譯好的檔案，除了自行編譯外只能載第三方編譯的。

載點：https://www.msys2.org/#installation

打開之後直接下一步就行了，沒什麼選項

打開之後長這樣輸入該指令安裝 OpenSSL

pacman -S openssl

這樣就安裝好可以用了

1. 創建SSL證書

分以下幾個步驟創建

創建私鑰

openssl genpkey -algorithm RSA -out private_key.pem

這個執行完畢就創好了，私鑰要保存好不能公開

創建憑證請求

openssl req -new -key private_key.pem -out request.csr

這邊會需要輸入很多，唯一要注意的就是域名別亂填要填該伺服器主機的IP或域名
(這個會影響到之後接入的問題，如果IP是浮動會跑的趁現在趕緊固定一下)

簽署該請求 (本來應該是由第三方CA做的)

openssl x509 -req -days 365 -in request.csr -signkey private_key.pem -out certificate.cer

這樣就能拿到公鑰了 (這份私簽的證書不會上公服，要手動發給客戶端才能用)

這裡的私鑰其實應該是在產一個比較好，只是方便直接拿來用了
實際上提交給CA簽署的的時候，不用也不能連請求的私鑰也一起給出去

由CA簽署過的公鑰會被上傳到公共伺服器，實際上也不需要發送給客戶
只要證書還有效，客戶就能能從公共伺服器直接獲取並連上該域名

合成完整的證書

openssl pkcs12 -export -out certificate.pfx -inkey private_key.pem -in certificate.cer

這個可以把私鑰跟公鑰合併起來，合併後才是完整的證書

最小限度可以僅保存這個檔案就好，但公鑰cer建議保存，方便你發布到其他電腦
(不然丟了還要從 pfx 檔案打指令挖出來，或是安裝後按匯出公鑰)

確認檔案

最後 MSYS2 的預設使用者位置在這裡 (直接貼上就好)

C:\msys64\home\%UserName%

確認一下檔案都在這兩個框起來是需要的檔案

2. 在伺服器上安裝私鑰

安裝證書

對著pfx檔案點擊右鍵＂安裝pfx＂

進入安裝介面選擇＂本機電腦＂

選擇下一步，密碼跟設置成可匯出根據自己需求選擇

安裝到＂個人＂(否則在SqlServer中會看不到證書)

3. 設置伺服器端證書權限

再來需要設置權限給 SQLSserver 有權使用他

按下 CTRL+R 打開執行視窗輸入 ＂certlm.msc＂ 按下確定打開憑證管理員

然後在個人中找到剛剛安裝的證書右鍵管理私密金鑰

選擇新增

然後輸入 NT Service\MSSQLSERVER 後按下檢查名稱，之後按下確定

這裡 MSSQLSERVER 其實就是預設的實例名，如果忘了實例名
可以像圖中背景那樣從SQL設定管理員那裏抄過來就好

看一下有沒有多出 MSSQLSERVER 然後再按下確定

這樣就完成了SQL已經有權限可以使用他

4. 在SQLSERVER中啟用SSL證書

再來到 SQL 設定管理員中啟用該證書
對著通訊協定按右鍵內容 -> 選擇憑證分頁 -> 選擇你的憑證

前面一頁旗標的分頁打開強制加密

最後再把 SQLServer 重新啟動即可

至此就完成這樣伺服器端的設置了

5. 在客戶端上安裝公鑰

在客戶端上對著cer檔案按右鍵安裝憑證

選擇本機電腦

安裝到＂受信任的根憑證授權單位＂

這樣就完成了。 (注意這個不能裝到別的地方會無效)

6. 測試加密狀態

要測試的話可以參考這一篇
CHG: SQL Server 如何加密連接 Server/Client 端解說

這邊簡單把內容搬過來快速講一下，就是打入測試指令而已。
記得尾端要移除 -N -C 這樣才能測試伺服器端到底有沒有強制打開。

sqlcmd -S '192.168.3.54,1433' -U 'chg' -P 'chg' -Q 'SET NOCOUNT ON SELECT c.session_id, c.encrypt_option, s.login_name, c.client_net_address, s.program_name FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id WHERE c.session_id = @@SPID' -W

這邊有一點要注意的是連入的伺服器名稱必須跟證書上的一樣，我上面創建證書是用IP就只能用IP登入，原本其實打電腦名稱也可以登入導入後就不行了。

這邊可以看到有個 True 這個就是成功加密了

可能有人有注意到圖中是伺服器端自己連自己，這樣測試還有效嗎？答案是有效的。因為就如同剛剛所提 cer 沒有導入到＂受信任的根憑證授權單位＂就無法使用，自然就無法依靠安裝在＂個人＂中的 pfx 證書連上了。

可能會想說那我直接把 pfx 安裝在＂受信任的根憑證授權單位＂不就可以一舉兩得？想法很完美但 SqlServer 預設只會讀＂個人＂中的證書，會直接導致看不到證書無法設置定的。

如何產生自簽署 OpenSSL 證書

1. 產生私鑰 pem

通過這個指令可以直接產出來

openssl genpkey -algorithm RSA -out private_key.pem

2. 憑證請求 csr (Certificate Signing Request)

openssl req -new -key private_key.pem -out request.csr

這邊其實很多資訊可以跳過，只是要注意的是網域不能亂填

3. 簽署該請求 cer/crt

openssl x509 -req -days 365 -in request.csr -signkey private_key.pem -out certificate.cer

這邊簽署本來應該是由CA做的，簽署用的私鑰可以是其他私鑰，只是方便用同一個

4. 打包成 pfx 檔案

openssl pkcs12 -export -out certificate.pfx -inkey private_key.pem -in certificate.cer

最後一個步驟是把簽署完畢的 cer 檔案跟原本的私鑰合併成完整的證書

到這邊就完成了，注意自簽署的證書放的位置
公鑰cer要放到【客戶端::/本機/受信任的憑證授權單位】才能起作用
私鑰也就是那個 pfx 沒影響放哪都行，不過建議放到【伺服端::/本機/個人】比較好找

Windows 如何安裝 OpenSSL 使用

最簡單的辦法是用 MSYS2 來執行，裡面有自帶不少編譯好的Linux環境下的軟體
https://www.msys2.org/#installation

安裝好之後輸入指令

pacman -S openssl

就能自動安裝好了，直接現成的可以用

另一個別人編譯好的版本在這裡

Win32/Win64 OpenSSL Installer for Windows - Shining Light Productions (slproweb.com)

JAVA 如何加密連接 SQLServer

簡單講重點就是在連接字串後面追加 encrypt=true; 即可，如果是自簽署的證書需要再追
加 trustServerCertificate=true; 信任CA以外的證書。

最終連接字串會是長這個樣子

 String connectionUrl = "jdbc:sqlserver://192.168.3.123:1433;user=sa;password=1234;encrypt=true;trustServerCertificate=true;";

接下來講一下如何構建簡單的 JAVA 來執行這個代碼，為了簡化到最懶人包這邊用 VsCode + JAVA Maven 的方式快速構建環境

注意如果是生產環境 trustServerCertificate=true; 千萬別加上去，就算是自簽SSL也應該是把他加入信任清單，而不是無條件信任。

JAVA環境建構

安裝 vscode
官方鏈結 Download Visual Studio Code - Mac, Linux, Windows

進去之後有三個版本，沒差自己選一個就好，不知道選哪個就預設的第一個就好，裝在使用者目錄下。

JDK可以先不用裝，因為等一下在 vscode 裡面會提示安裝，順著連結按下去安裝就好。

在 Bat 中如何跟 Ufft8 的程序互動 避免亂碼

遇到的情況蠻坑人的某些程序預設會強制使用 utf8 ，這會導這不支援的 cmd 直接就攤手出亂碼了，我是在用 sftp 的時候遇到的

還特別坑的是他會強制修改 chcp 改成 65001 導致出錯誤信息的時候連帶後面的 echo 都一起跟著亂碼了

想了很多辦法最後試出來的是，委託給powershell執行，雖然還是無法避免被切 chcp 不過可以一起封裝在裡面執行完順便切回來

如何正確的在bat中打印utf8編碼的文字

先說一下最簡情況，我們一步一步來強化，首先就是丟進去執行

powershell -nop "winscp.com /script=script.txtx; exit $lastexitcode"

如此一來就可以得到正確的文字了，順便用 Exit 把執行的返回代碼給帶出來。
此時 PowerShell 會把執行結果回丟到 Cmd 上，可以在透過一般的寫法完整的捕捉到。

powershell -nop "winscp.com /script=script.txtx; exit $lastexitcode" > %LOG% 2>&1

這樣錯誤信息也會一起出來了。
再來下一步我們把重要的指令部分提取出來用管道送進去，剩下不重要的封裝在裡面

echo winscp.com /script=script.txt| powershell -nop "$input|iex; exit $lastexitcode"

這樣就抽取出來了，只需要在意管道的左邊即可，剩下都封裝在右邊
下一步是修正前面提到擅自更改 chcp 的問題

echo winscp.com /script=scripts.txt| powershell -nop "$input|iex; [console]::OutputEncoding=[Text.Encoding]::Default; Exit $LASTEXITCODE"

一樣封裝在 PowerShell 這樣可以眼不見為淨，就此就完成了

在BAT中如何做 Ascii 文本的變換 更換換行符號 (Linux -> Win)

反過來 dos2Unix 請參考這篇
CHG: 在BAT中如何做 Ascii 文本的變換 更換換行符號 (Win -> Linux) 

Bat能做的事情非常有限，這邊最有效率的辦法是直接呼叫老大哥 PowerShell 來直接擺平這事情。

廢話不多說直接上代碼一行而已

@echo off& Title EOLConverter By Charlotte
echo D:\UnixTextFile.txt | powershell -nop "[string]$fle=$input; if(-not(Test-Path $fle)){Write-Error 'Path is not exist' -ea 1}; $tmp=$fle+'.tmp'; $enc=[Text.Encoding]::Default; $red=New-Object IO.StreamReader($fle,$enc); $wrt=New-Object IO.StreamWriter($tmp,$false,$enc); while(-not $red.EndOfStream){$wrt.WriteLine($red.ReadLine())}; $red.Close();$wrt.Close(); mv $tmp $fle -fo -ea 1"
exit /b %errorlevel%

力求簡約一行直接幹掉，相當於引用一個外部二禁制檔案的概念，內容就不用太糾結了有興趣自己拆行出來看馬上能懂得

不過功能簡單只有針對系統語言編碼做轉換，非系統語言編碼或是涉及到兩種編碼轉換的，自行修改內容吧

內容做的是從Linux轉換到Windwos反過來的自行看著代碼推敲吧

如何在 docker 安裝 SQLServer 2022 的容器

現代在Windwos11上安裝相比於之前簡單了不少，現在一步一步跟著安裝吧。

裝在 docker 上有一個好處是不會掛在服務上要關掉直接關掉 docker 就好一切乾淨，軟體層面切割的比較徹底一些。

另外因為是 docker 是跑在 linux 上的，所以間接的 Mac M2 晶片的電腦也受益了是可以安裝的。

步驟1. WSL2

安裝 WSL2 指令參考這個

wsl --install

以前還要定系統設定現在通通不用了，直接就整合好一個指令裝到好
裝好之後需要重新啟動，重新動之後會看開裡面多出幾個東西

找到多出來的 ubuntu 打開它還會在安裝一次，裝好就能用了，過程會提示要輸入帳號密碼

一切都好了之後，用這個指令做最後的確認可以看安裝的是幾版的

wsl --list --verbose

有一點讓我有點意外的在 Windwos11 上這樣裝完顯示的版本居然是 WSL2 但卻沒有啟用 Hyper-V ，不知道是不是改版了 WSL2 沒有綁定 Hyper-V 還是怎樣。

雖然不知道為什麼，總之有這個疑慮的人照著這篇做完可以放心沒有被強制開啟 Hyper-V ，下面安裝 docker 也要注意要用 WSL 的環境，不然安裝檔會順便幫你啟用。

步驟2. 安裝docker

Docker: Accelerated Container Application Development
首頁就一個大大的下載，載了直接裝就是了

過程中會問你要不要用wsl的環境，全都預設給他按下去就好了
裝好打開看一兩眼沒問題就可以裝 SQLServer 了

步驟3. 安裝 SQLServer

指令只有兩個

下載容器

docker pull mcr.microsoft.com/mssql/server:2022-latest

載好之後再來要啟動它，這個啟動可以當作是實例要啟幾個都行每個獨立的
這邊密碼自己改一個包含<括號>整團是密碼，別留下括號會變成密碼的一部分
sql1就當作實例的區別吧如果要啟第二個實例這邊要改

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<Your@Passw0rd>" `
   -p 1433:1433 --name sql1 --hostname sql1 `
   -d mcr.microsoft.com/mssql/server:2022-latest

然後就完成了，再來隨意載個能連的軟體連接看看，預設管理員是SA

推薦載這個輕巧好看的軟體：Download and install Azure Data Studio

參考

1. Docker：安裝 Linux 上的 SQL Server 容器 - SQL Server | Microsoft Learn
2. 部署和連線到 SQL Server Linux 容器 - SQL Server | Microsoft Learn
3. Windows 10 安裝 WSL1、WSL2(手動安裝) - HackMD