Sqlserver安全篇之_啟用TLS即配置SQL Server 數據庫引擎以加密連接

官方文檔
https://learn.microsoft.com/zh-cn/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16
https://learn.microsoft.com/zh-cn/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver15&preserve-view=true

TLS加密連接與TDE(Transparent data encryption)透明數據加密完全不一樣，雖然TDE也要證書，但是TDE是針對數據庫層面的也就是針對數據和日志文件，比如影響整個數據庫的備份恢復、快照、刪除、脫機、分離、轉換READ ONLY 狀態等，主要是DBA這種管理人員使用的。

TDE加密數據庫步驟
1、創建證書certSSRSTEST

create certificate certSSRSTEST with subject ='SSRSTEST database certificate data encription';

2、使用證書certSSRSTEST加密數據庫SSRSTEST

use SSRSTEST;
go
create database encryption key with algorithm = AES_128 encryption by server certificate certSSRSTEST
go
alter database SSRSTEST set encryption on
go

Sqlserver啟用TLS加密連接的步驟
1、SQL Server配置為使用證書
每個版本的Sqlserver啟動的時候都會生成一份self-signed certificate自簽名證書，也可以使用powershell腳本建立一個自簽名證書，也可以導入一個證書。再配置Sqlserver使用這些證書

在SQL Server 2019及更高版本中，證書管理已集成到SQL Server配置管理器中。這句話的理解是，從SQL Server 2019開始因為Sql Server Configuration Manager有了Import選項(Sql Server Configuration Manager–>SQL Server Network Configuration–>Right-click Protocols for MSSQLSERVER–>Properties–>Certificate–>Import)，可以一次性導入證書并配置Sqlserver使用這些證書，而之前的版本沒有這個Import選項，所以從SQL Server 2019開始我們不再需要像之前的sqlserver版本一樣先通過certlm.msc或MMC把證書導入到操作系統再通過Sql Server Configuration Manager配置Sqlserver使用這些證書

SQL Server 2019之前的版本，需要2步，第一步導入證書，第二步配置Sqlserver使用這些證書

certlm.msc-->Certificates-Local Computer-->Personal-->Certificates-->All Tasks-->Import
Sql Server Configuration Manager-->SQL Server Network Configuration-->Right-click Protocols for MSSQLSERVER-->Properties-->Certificate-->Certificate：-->dropdown list-->choose Certificate-->Apply

SQL Server 2019版本開始，一次性導入證書并配置Sqlserver使用這些證書

Sql Server Configuration Manager-->SQL Server Network Configuration-->Right-click Protocols for MSSQLSERVER-->Properties-->Certificate-->Import

在這里插入圖片描述

2、SQL Server配置強制加密

Sql Server Configuration Manager-->SQL Server Network Configuration-->Right-click Protocols for MSSQLSERVER-->Properties-->Flags-->Force Encryption-->Yes

Sqlserver配置了強制加密后，客戶端或在實例本機上使用SSMS連接該實例如果不勾選Trust server certificat這個選項的話會有報錯：A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

視圖sys.dm_exec_connections的encrypt_option字段的值可以看到會話是否已經加密了

select session_id,encrypt_option,connect_time,client_net_address,local_net_address,net_transport,protocol_type,endpoint_id,auth_scheme from sys.dm_exec_connections

Sqlserver啟用TLS加密后，證書信息并沒有在sys.certificates表中找到

因為每個版本的Sqlserver啟動的時候都會生成一份self-signed certificate自簽名證書，Sqlserver服務器啟動后在錯誤日志里面會記錄一條信息A self-generated certificate was successfully loaded for encryption，這樣的話就算該Sqlserver沒有使用其他證書只要該Sqlserver配置了強制加密，客戶端比如SSMS連接到該Sqlserver時如果選擇了Encrypt connection和Trust server certificate，那么連接的會話就會被加密。

SQL Server uses a certificate from a trusted certification authority if available for encrypting login packets. If a trusted certificate isn’t installed, SQL Server generates a self-signed certificate (fallback certificate) during startup and use that self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security, but it doesn’t protect against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application is encrypted using the self-signed certificate.
SQL Server 使用來自受信任的證書頒發機構（如果可用）的證書來加密登錄數據包。如果未安裝受信任的證書，SQL Server 將在啟動期間生成自簽名證書（回退證書），并使用該自簽名證書來加密憑據。此自簽名證書有助于提高安全性，但它不會防范通過服務器進行的身份欺騙。如果使用了自簽名證書，并且“ForceEncryption”選項的值設置為“是”，將使用自簽名證書對通過網絡在 SQL Server 和客戶端應用程序之間傳輸的所有數據進行加密。
–備注：以上那句話"但它不會防范通過服務器進行的身份欺騙"，不是說在實例的本機上使用SSMS可以直接連接該實例，因為如果實例啟用強制加密連接后，就算在該實例的本機上使用SSMS連接該實例如果不勾選Trust server certificate這個選項的話一樣會有報錯：A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

創建self-signed certificate自簽名證書的powershell腳本，以下腳本執行后，在certlm.msc或MMC或Sql Server Configuration Manager里面都能直接查看到，說明證書創建好了的話就代表導入到了操作系統中

# Define parameters
$certificateParams = @{Type = "SSLServerAuthentication"Subject = "CN=$env:COMPUTERNAME"DnsName = @("$($env:COMPUTERNAME)", $([System.Net.Dns]::GetHostEntry('').HostName), 'localhost')KeyAlgorithm = "RSA"KeyLength = 2048HashAlgorithm = "SHA256"TextExtension = "2.5.29.37={text}1.3.6.1.5.5.7.3.1"NotAfter = (Get-Date).AddMonths(36)KeySpec = "KeyExchange"Provider = "Microsoft RSA SChannel Cryptographic Provider"CertStoreLocation = "cert:\LocalMachine\My"
}# Call the cmdlet
New-SelfSignedCertificate @certificateParams

查看證書
certlm.msc–>Certificates-Local Computer–>Personal–>Certificates
或
Sql Server Configuration Manager–>SQL Server Network Configuration–>Right-click Protocols for MSSQLSERVER–>Properties–>Certificate–>Certificate:–>dropdown list

導出證書
certlm.msc–>Certificates-Local Computer–>Personal–>Certificates–>Right-click Certificatename–>All Tasks–>Export

導入證書
Sql Server Configuration Manager–SQL Server Network Configuration–Right-click Protocols for MSSQLSERVER–Properties–Certificate–>Import
或
certlm.msc–>Certificates-Local Computer–>Personal–>Certificates–>All Tasks–>Import

certlm.msc等同于MMC–>File–>Add/Remove Snap-in–>Certificates–>Add–>Computer account–>Next–>Finish–>OK

Sqlserver導入self-signed certificate自簽名證書報錯
Sql Server Configuration Manager–>SQL Server Network Configuration–>Right-click Protocols for MSSQLSERVER–>Properties–>Certificate–>Import

Errors or Warnings for certificate:C:\Users\lukes\Desktop\privatekey.pfx
The selected certificate is a self signed certificate. Proceeding with this certificate isn't advised
Error:The selected certificate name does not match FQDN of this hostname. This property is required by SQL Server Certificate name: DBTEST1 Computer name: DBTEST1.lukes.com

Sqlserver導入一個外部的域名的證書
Sql Server Configuration Manager–>SQL Server Network Configuration–>Right-click Protocols for MSSQLSERVER–>Properties–>Certificate–>Import，導入一個證書G:\Share\TLS\star.pan.com.pem的時候出現問題，本機的域名是daidbrestore1.dai.com，導入的的證書的dns是*.pan.com

Errors or Warnings for certificate:G:\Share\TLS\star.pan.com.pem
-----------------------
Error:The selected certificate name does not match FQDN of this hostname. This property is required by SQL Server Certificate name: *.pan.com Computer name: daidb1.dai.com 
Error:The selected certificate  does not have the KeySpec Exchange property. This property is required by SQL Server to import a certificate. 
Import error: 0x2, Windows Native Error: 0x80092004