SQL Server SSL/TLS 加密

文档

官方文档
https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15
建议看看下面这个，可以测试加密是否成功：
http://mysql.taobao.org/monthly/2019/04/02/
抓包工具：
https://download.microsoft.com/download/7/1/0/7105C7FF-768E-4472-AFD5-F29108D1E383/NM34_x64.exe

补充说明

以下情况也会加密连接：

• 开启"Force Encryption"。
• 对于服务器安装了证书，并启用了注册表启用TLS加密的，抓包工具是抓不到数据的，即若在服务器层使用了加密，SQL Server和 其他客户端的通信也是加密的。

官方原文说明：

TLS can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server.

但是对于没有使用CA证书，如果只指定Encrypt=True,那么就会报以下错误
https://docs.microsoft.com/en-us/troubleshoot/sql/connect/error-message-when-you-connect

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - 证书链是由不受信任的颁发机构颁发的。) (.Net SqlClient Data Provider)

创建自签名证书

也可以使用自签名证书

<#
.DESCRIPTION
Create Certificate
.EXAMPLE
.\New-selfSignedCertificate.ps1 -FQDN FQDN
.NOTES
CertMGR
#>
param(
    [string]$FQDN,
	[string]$CertPath="C:\temp" #Exported certificate path
)
Write-Host "Target server FQDN: $FQDN" -ForegroundColor Green -BackgroundColor Black
if ($FQDN -eq $null -or $FQDN -eq ""){
    Write-Error 'You need to specify the FQDN parameter. Run this command to get FQDN on target Seraver : [System.Net.Dns]::GetHostByName($env:computerName)' -ErrorAction Stop
}
    

$FriendlyName="$FQDN Self Signed Cert By KiGiBoy"
$Subject="Self Signed Cert By KiGiBoy"
New-SelfSignedCertificate -DnsName $FQDN -CertStoreLocation cert:\LocalMachine\My -FriendlyName $FriendlyName -KeySpec KeyExchange -Subject $Subject -NotAfter (get-date).AddYears(199)

$pwd=ConvertTo-SecureString -String "Str0ngePassword1!" -Force -AsPlainText

$THUMBPRINT=Get-ChildItem -path cert:\LocalMachine\My | Where-Object -Property FriendlyName -EQ $FriendlyName | select Thumbprint -First 1
$loc="cert:\LocalMachine\My\"+$THUMBPRINT.Thumbprint
$path=join-path -Path $CertPath -ChildPath "$FriendlyName.pfx"
if (Test-Path -Path $CertPath){
    Export-PfxCertificate -Cert $loc -FilePath $path -Password $pwd
}else{
	New-Item -ItemType Directory -path $CertPath -InformationAction Ignore 
    Export-PfxCertificate -Cert $loc -FilePath $path -Password $pwd
}


Write-Host "Exported Certificate Location: $path" -ForegroundColor Green -BackgroundColor Black