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 Configuration Manager
- Sql Server Network Configuration
- Protocols for (MYSQLSERVERNAME)
- Right click: Properties
- Flags tab.
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.
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)
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_connectionsIn fact the column encrypt_option is TRUE.
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
...The current OS is Windows 10 Home.
What I'm missing?
5条答案
按热度按时间9fkzdhlc1#
I had the same issue and got resolved by adding
TrustServerCertificate=True;to the connection string.kr98yfug2#
I received this error when I was doing something similar. I also created a certificate from OpenSSL and imported it into SQL Server. I also used SQL Server Management Studio to attempt to verify that the client side copy of the certificate was required. When I did this I got the error described above.
The solution was simply that in the window to connect I was not using the CN that is on the certificate:
Instead of 127.0.0.1 (or whatever you have there) put the CN on the certificate and this connection should work.
axr492tv3#
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:
piwo6bdm4#
I got this error when trying to connect via sqlcmd to a server which required windows integrated authentication (option
-E) but accidentally used Azure Active Directory Authentication (option-G). Selecting the correct flags fixed it for me. Note that this is the equivalent of includingTrusted_Connection=Truein the connection string.k5ifujac5#
It's possible that your Server Certificate is using a *.domain name.
Make sure your SQL server has a certificate with a fully qualified name (sqlserver.yourcompany.com, not just *.yourcompany.com)
I used to get this same error when I had a *.mycompany.com certificate installed, but when I tried with a self-signed certificate specifically made for that SQL server, Then everything worked.
The steps are as follows: Assuming you have already generated a certificate and it's in your machine