MSSQL with SSL: the target principal name is invalid

Question

I have successfully configured SSL on Microsoft SQL Server 2012 Express Edition to encrypt external network database connections that are created over the Internet. For performance reasons, for internal clients on the network, I do not want to force SSL and leave clients the option to use it or not. I set Force Encryption to No with the following steps:

• Sql Server Configuration Manager
• Sql network configuration configuration
• Protocols for (MYSQLSERVERNAME)
• Right click: Properties
• flags .

When I try to establish an encrypted connection with Microsoft Sql Server Management Studio by checking the Encrypt connection option on Settings > Connection Properties . I get the following error.

The connection to the server was successfully established, but then an error occurred during the registration process. (provider: SSL provider, error: 0 - invalid member name). (Microsoft SQL Server, error: -2146893022)

What is striking is that if I select Force Encryption as Yes in Sql Server Configuration Manager and I do not select Encrypt Connection on Microsoft Sql Server Management Studio I can connect to the database. If I execute the request:

select * from sys.dm_exec_connections

encrypt_option .

Openssl, :

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.

?

Answer 1

, OpenSSL, . , MSSQL , :

• SQL Server.
• , MSSQL ( ) MSSQL ).
• MMC ( ).
• , → ....
• , MSSSQL.

Answer 2

, - . OpenSSL SQL Server. SQL Server Management Studio, , . , , .

, CN, :

127.0.0.1 ( ) CN , .

Answer 3

I had the same problem and decided to remove it by adding TrustServerCertificate=True;to the connection string.