sql-serverssl

MSSQL with SSL: The target principal name is incorrect

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:

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_connections

In 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?

Solution

  • 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: