研究这个问题的起因其实因为这个错误 :

当我刚为服务器装好 SQL Server 后,远程连接时提示我SSL Provider, error:0 -证书链是由不受信任的颁发机构颁发的。

在多方查阅后,解决这个错误的方案有两个:

  1. 为你的 SQL Server 连接加密,在服务器上添加受信任的证书
  2. 链接数据库时勾选信任服务器证书,或在连接字符串上设置TrustedServerCertificates=True

但根据StackOverflow上这篇回答所示,这样是不安全的。

它会让 SQL Server 生成自签名证书为连接加密,但自签名证书并不能完全保证连接的安全,所以想一劳永逸,还是得选择解决方案1,接下来我们进入正题。

友情提醒:我一开始以为这个问题非常简单,但它整整花了我两天时间才搞定,步骤比较多比较杂,我建议有其他更好的解决方案的可以自己权衡一下,当然你有更好的解决方案请一定要告诉我。


打开证书管理单元

进入服务器 → 运行 → MMC → 添加或删除管理单元 → 添加证书管理单元

在弹框中选择计算机账户 → 下一步 → 完成

这一步完成后,先放在这里,后续生成好证书和私钥后,就在这里导入。

将服务器的 FQDN 映射到外网

为了让 Let's Encrypt 签发的证书支持 SQL Server 连接加密,我们需要将 FQDN 映射到外网。

FQDN 全程为 Fully qualified doman name:完全限定名称,其实就是系统属性中的计算机全名。

点击更改,你会看到一个计算机名,再点击其他,你会看到一个DNS后缀的设置。

于是你会发现 FQDN = 计算机名.DNS后缀,看上去是不是特别像一个子域名?

当然你先别急着改成我这样,先接着看。

这个时候我们就需要把这个 FQDN 暴露到外网。

首先你需要短暂地暴露一下你服务器的80端口,你需要一个 IIS 或者 Nginx 等服务器验证下你是否可以通过服务器IP地址进行外部访问了。

然后我们需要一个可用的外部 DNS,你可以通过 nslookup {你的FQDN},找到服务器的 DNS,或者用你自己的域名指向服务器IP作为DNS后缀使用。

找到之后将 DNS 后缀修改为你的域名,或者你刚才找到的服务器DNS,然后重启。

重启后,将 FQDN 绑定在一个网站上,像这样,并检查其能否被外部访问。

疑难排查

如果访问不了,你需要简单地排查一下权限啊,IIS 服务器本身的配置问题。

其次,你需要检查一下你找到的DNS是不是外部DNS,对于一些服务器来说,你可能只会找到一个只支持内部访问的DNS,那么你可能就需要一个域名。

或者还有可能就是这个外部DNS本身是可以访问的,但将你的计算机名加上去形成 FQDN 之后,它不一定能被正确转发到你的服务器上,比如我用的Google Cloud Platform就死活转发不过来,还是只能用自己的域名。

用 win-acme 申请 Let's Encrypt 证书

为了申请 Let's Encrypt 证书,我们需要一个叫做 win-acme 工具。

(虽然吧,你用 openssl 自签证书也是可以为 SQL Server 的连接加密的,但那其实也不够安全,跟让 SQL Server 自行生成自签证书不会有特别大的区别)

下载好后打开,用以下步骤操作:

N: Create new certificate
M: Create new certificate with advanced options
L: List scheduled renewals
R: Renew scheduled
S: Renew specific
A: Renew *all*
O: More options...
Q: Quit

Please choose from the menu: M

[INFO] Running in mode: Interactive, Advanced

选择手动输入,然后输入你的 FQDN

1: Single binding of an IIS site
2: SAN certificate for all bindings of an IIS site
3: SAN certificate for all bindings of multiple IIS sites
4: Manually input host names
<Enter>: Abort

Which kind of certificate would you like to create?: 4

Enter comma-separated list of host names, starting with the common name: sg-server.siegrain.wang

[INFO] Target generated using plugin Manual: sg-server.siegrain.wang

Suggested FriendlyName is '[Manual] sg-server.siegrain.wang', press enter to accept or type an alternative: sg-server

1: [dns-01] CNAME the record to a server that supports the acme-dns API
2: [dns-01] Manually create record
3: [dns-01] Run script to create and update records
4: [http-01] Host the validation files from memory (recommended)
5: [http-01] Save file on local or network path
6: [http-01] Upload verification file to WebDav path
7: [http-01] Upload verification files via FTP(S)
8: [http-01] Upload verification files via SSH-FTP
C: Abort

How would you like to validate this certificate?: 4

1: Elliptic Curve key
2: Standard RSA key pair

What kind of CSR would you like to create?: 2

1: IIS Central Certificate Store
2: Windows Certificate Store
3: Write .pem files to folder (Apache, ngnix, etc.)

How would you like to store this certificate?: 3

Path to folder where .pem files are stored: C:\Users\siegrain\Desktop\certificates

这里选择存储 pem 文件到文件夹,这个 pem 文件,其实就是加密后的证书跟秘钥,后续我们要通过命令导出里面的证书跟秘钥。

1: Do not run any installation steps
2: Run a custom script
<Enter>: Abort

Which installer should run for the certificate?: 1

[WARN] First chance error calling into ACME server, retrying with new nonce...
[INFO] Authorize identifier: sg-server.siegrain.wang
[INFO] Cached authorization result: valid
[INFO] Requesting certificate sg-server
[INFO] Exporting .pem files to C:\Users\siegrain\Desktop\certificates
[INFO] Installing with None...

Do you want to replace the existing task? (y/n*)  - yes

[INFO] Deleting existing task win-acme renew (acme-v02.api.letsencrypt.org) from Windows Task Scheduler.
[INFO] Adding Task Scheduler entry with the following settings
[INFO] - Name win-acme renew (acme-v02.api.letsencrypt.org)
[INFO] - Path C:\Users\siegrain\Desktop\certificates\win-acme
[INFO] - Command wacs.exe --renew --baseuri "https://acme-v02.api.letsencrypt.org/"
[INFO] - Start at 09:00:00
[INFO] - Time limit 02:00:00

Do you want to specify the user the task will run as? (y/n*)  - yes

Enter the username (Domain\username): {log on name in your sqlsvr instance}

Enter the user's password: {yourpassword}

[INFO] Adding renewal for sg-server
[INFO] Next renewal scheduled at 2019/6/7 18:45:21

最后一步也比较重要,这里的Domain\username需要是你 SQL Server 实例的 Log On 账号,也就是这个:

行了,到这里证书就签发完毕了,如果你证书信息有误,要注意 Let's Encrypt 每周还是每月签发额度是有限的,自己注意一下。

从 pem 文件导出证书和秘钥

导出证书是通过 openssl 完成的,如果没有的话你要安装一下,这里不多赘述。

导出证书:

openssl x509 -in sg-server.siegrain.wang-chain.pem -inform PEM -out server.crt

导出秘钥:

openssl pkcs12 -export -inkey sg-server.siegrain.wang-key.pem -in sg-server.siegrain.wang-chain.pem -out server.p12

这里会要求你输入密码,输入后需要在导入秘钥时填写密码。

导入证书跟秘钥

打开你第一步的 mmc → 证书 → 个人 → 证书 → 右键 → 所有任务 → 导入

找到你刚才导出的 crt 跟 p12 文件,一个一个导入就行了,其中导入秘钥时需要输入你在导出时指定的密码。

为 SQL Server 连接开启加密

打开 SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for MSSQLSERVER(取决于你的实例名称) → 右键 → 属性

  1. Flags → ForceEncryption → Yes
  2. Certificate → 选择你刚才导入的证书即可

疑难排查

这里如果看不到你的证书,原因是非常多的,我就直接从官方文档复制了。

因为您可能已安装证书无效,将出现此问题。如果证书是无效的它将不会在证书选项卡上列出。要确定您安装的证书是否有效,请执行以下步骤:

  1. 打开证书管理单元。若要执行此操作,请参阅"如何配置 mmc 管理单元"部分中的步骤 1。
  2. 在证书管理单元中,展开个人,然后展开证书
  3. 在右窗格中,找到您所安装的证书。
  4. 确定证书是否满足以下要求:
    • 在右窗格中,为该证书预期目的列中的值必须是服务器身份验证
    • 在右窗格中,颁发给列中的值必须是服务器的名称。
  5. 双击该证书,然后再确定证书是否满足以下要求:
    • 常规选项卡中,您将收到以下消息:

      您有一个与该证书对应的私钥。

    • 详细信息选项卡上的主题字段的值必须是服务器名称。

    • 增强型密钥用法字段的值必须是服务器身份验证 (< 数量 >)

    • 证书路径选项卡上的服务器名称必须出现在证书路径下。

如果不满足这些要求的任何一个,则该证书是无效的。

验证连接是否加密

打开 SQL Server Management Studio → Connect to Server → Connection Properties → 选中 Encrypt Connection,但不要选中 Trust server certificate。

回到 Login 选项卡,在 Server name 上尝试输入你的计算机名,localhost,IP地址一类的,会得到这个提示:

说明你的连接加密成功了,你只能在 Server name 上输入 FQDN 才能登录进去。

此外,证书默认是90天过期,不过 win-acme 会给你添加一个自动续期的计划任务,但这个能不能用我就没有测试过了。

想更全面的了解本篇涉及到的技术、知识跟其他方面,可以自行查看参考\致谢章节的链接。

参考\致谢

如何通过使用 Microsoft 管理控制台启用 SSL 加密的 SQL Server 实例

What is a pem file?

Converting PKCS#12 certificate into PEM using OpenSSL

Encrypting SQL Server connections with Let’s Encrypt certificates

全篇完。