MSSQL 2022 快速建構 自簽署SSL證書

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

上一篇已經完整的解析如何建構自簽署SSL證書，這一篇是續集補上如何快速使用 PowerShell 建構的方式。不同於ＯpenSSL PowerShell 可以直接跳過中間步驟直接就簽署完了，步驟相對縮短很多。

伺服器端

創建證書

# 定義證書詳細資訊
$countryCode = "TW"
$state = "Taipei"
$city = "Taipei"
$companyName = "CHG"
$organizationalUnit = "CHG"
$dnsName = "192.168.3.68"

# 創建自簽名證書
$subject = "C=$countryCode, S=$state, L=$city, O=$companyName, OU=$organizationalUnit, CN=$dnsName"
$cert = New-SelfSignedCertificate -DnsName $dnsName -CertStoreLocation "cert:\LocalMachine\My" -Subject $subject -KeyExportPolicy Exportable -KeySpec Signature

給與SQLSERVER權限 (如果實例名不是預設的MSSQLSERVER記得修改)

# 將權限授權給 NT Service\MSSQLSERVER
$sqlLoginUser = "NT Service\MSSQLSERVER"
$keyPath = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
$fullKeyPath = Join-Path "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys" $keyPath
icacls $fullKeyPath /grant "$($sqlLoginUser):F"

信任該證書 (自己連接自己用的)

# 導出公鑰到 .cer 文件
$publicKeyPath = "$env:USERPROFILE\certificate.cer"
Export-Certificate -Cert $cert -FilePath $publicKeyPath
# 將公鑰安裝到本機信任位置
Import-Certificate -FilePath $publicKeyPath -CertStoreLocation cert:\LocalMachine\Root

到這證書設置就完成了，接下來需要回到 SQLSERVER 啟用該證書。詳細可以參考文章開頭連結那篇，裡面定位到【4. 在SQLSERVER中啟用SSL證書】補一下這章節內容

設置完畢再來，自己連接自己驗證一下證書可用性

# 使用 Windows認證 驗證加密情況
sqlcmd -S . -E -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

命令解析：（"-S ." = 連接自己）, （"-E" = 使用當前登入的Windwos用戶）

執行結果有看到第二個欄位 True 就是加密連接的意思了

客戶端

剛剛伺服器端預設是產在使用者目錄底下，自行把 cer 文件複製過來
("$env:USERPROFILE\certificate.cer")

# 定義公鑰的路徑
$publicKeyPath = ".\certificate.cer"

# 導入公鑰到受信任的根證書發行機構
Import-Certificate -FilePath $publicKeyPath -CertStoreLocation cert:\LocalMachine\Root

再來驗證一下，這邊用 sqlserver認證 比較方便

# 使用 SqlServer認證 驗證加密情況
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

執行這個命令需要 "檢視伺服器狀態(SERVER STATE)" 的權限，參考以下幾個方法

• 賦予 SERVER STATE 權限
• 從伺服器角色中給予 processadmin 或 setupadmin 權限
• 用 sa 帳戶登入

賦予該權限的圖形介面操作可以參考這張圖