带有 SSL 的 MSSQL:目标主体名称不正确 [英] MSSQL with SSL: The target principal name is incorrect
问题描述
我在 Microsoft SQL Server 2012 Express Edition 上成功配置了 SSL，目的是加密通过 Internet 建立的到数据库的外部网络连接.出于网络上内部客户端的性能原因，我不想强制使用 SSL，而是让客户端选择是否使用它.我通过以下步骤将强制加密设置为否:
I configured successfully SSL on Microsoft SQL Server 2012 Express Edition for the purpose of encrypting external network connections to the database that are made through Internet. For performance reasons for internal clients on the network I do not want to force the use of SSL and leave to the clients the option of use it or not. I set Force Encryption to No with the following steps:
- Sql Server 配置管理器
- Sql Server 网络配置
- (MYSQLSERVERNAME)的协议
- 右键单击:属性
- 标志标签.
当我尝试与 Microsoft Sql Server Management Studio 建立加密连接时，检查 选项 上的 加密连接 选项 > 连接属性 我得到以下错误.
When I try to establish an encrypted connection with Microsoft Sql Server Management Studio checking Encrypt connection option on Options > Connection Properties I get the following error.
已成功与服务器建立连接，但随后在登录过程中出现错误.(提供程序:SSL 提供程序，错误:0 - 目标主体名称不正确.)(Microsoft SQL Server，错误:-2146893022)
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)
令人惊讶的是，如果我在 Sql Server Configuration Manager 上选择 Force Encryption 作为 Yes 而我没有在 Microsoft Sql 上选择 Encrypt connectionServer Management Studio 我可以连接到数据库.如果我执行查询:
What is striking is that if I select Force Encryption as Yes on Sql Server Configuration Manager and I not select Encrypt connection on Microsoft Sql Server Management Studio I can connect to the database. If I execute the query:
select * from sys.dm_exec_connections
事实上，encrypt_option 列是正确的.
In fact the column encrypt_option is TRUE.
证书是用 Openssl 生成的，信息如下:
The certificate was generated with Openssl and this is the information:
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 2 (0x2)
Signature Algorithm: sha256WithRSAEncryption
Validity
Not Before: Jun 9 15:53:18 2016 GMT
Not After : Jun 9 15:53:18 2018 GMT
Subject: C=US, ST=State, L=Location, O=Testing, OU=Development, CN=JOSEPH-ASUS
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
...
Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Subject Key Identifier:
DB:7F:58:DC:F7:D9:90:2A:DF:0E:31:84:5C:49:68:E7:61:97:D8:41
X509v3 Authority Key Identifier:
keyid:C9:5C:79:34:E0:83:B2:C7:26:21:90:17:6A:86:88:84:95:19:88:EA
X509v3 Basic Constraints:
CA:FALSE
X509v3 Key Usage:
Key Encipherment, Data Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Alternative Name:
DNS:alternatename1, DNS:alternatename2, IP Address:192.168.1.100, IP Address:192.191.1.101, IP Address:192.168.1.103
Signature Algorithm: sha256WithRSAEncryption
...
当前的操作系统是 Windows 10 Home.
The current OS is Windows 10 Home.
我缺少什么?
推荐答案
使用 OpenSSL 生成的证书正常工作.在我的情况下，问题是通过证书运行 MSSQL 的帐户的权限，我通过以下步骤解决了这个问题:
The certificate generated with OpenSSL work properly. In my case the problem was rights of the account under which runs MSSQL over the certificate, I solved this issue with the follow steps:
- 打开SQL Server 配置管理器.
- 找到用于运行 MSSQL 实例的帐户(MSSQL 实例属性上的登录选项卡).
- 打开MMC 控制台并添加证书(本地计算机)管理单元.
- 搜索证书存储，右键单击证书并选择所有任务 -> 管理私钥....
- 将权限设置为运行 MSSSQL 的同一帐户.
- Open SQL Server Configuration Manager.
- Locate the account which is used to run MSSQL instance (Log On tab on MSSQL instance Properties).
- Open MMC Console and add Certificates (Local Machine) snap-in.
- Search the certificate store, right click on certificate and select All Tasks -> Manage Private Keys....
- Set the Permissions to the same account under which MSSSQL runs.
这篇关于带有 SSL 的 MSSQL:目标主体名称不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
