MSSQL with SSL: The target principal name is incorrect

the target principal name is incorrect sql server
ssl provider: the target principal name is incorrect
azure sql the target principal name is incorrect
hit the target principal name is incorrect
ssms target principal name incorrect
smartconnect the target principal name is incorrect
[microsoft][odbc driver 17 for sql server]ssl provider: the target principal name is incorrect.
system componentmodel win32exception 0x80004005 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:

  • 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_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?

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.

MSSQL with SSL: The target principal name is incorrect, The SQL Server 2016 instance has (provider: SSL Provider, error: 0 - The target principal name is incorrect.) 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. "The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)" This appears to have begun shortly after upgrading SQL 2016 to SP1. And I appear to be the only one affected.

How to resolve an SQL Server "Target principal name is incorrect , I am experiencing a "Target principal name is incorrect" error after upgrading SQL Server 2016 to 2017. The SQL Server 2016 instance has  SQL Server / The target principal name is incorrect. Cannot generate SSPI context. One of our old SQL servers was running under the local system context. Then we decided to change the account that the SQL service runs under, and we created domain service account with basic domain user permissions.

I had the same issue and got resolved by adding TrustServerCertificate=True; to the connection string.

https://social.msdn.microsoft.com/Forums/vstudio/e, AWS RDS (SQL Server): SSL Connection - The target principal name is incorrect · ssl amazon-rds sql-server-2012. I have a Amazon Web Services (AWS)  Solving the Target Principal Name is Incorrect. A bit of minor negligence can cause havoc at times. This article presents a practical situation that I faced in my organization. In our working environment, we use a VDI (Virtual Desk Interface) to connect to the client environment.

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 including Trusted_Connection=True in the connection string.

AWS RDS (SQL Server): SSL Connection, (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​  “The target principal name is incorrect. Cannot generate SSPI context.” The explanation, as given by Microsoft in this KB article. If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos authentication interacts successfully with the computer that is running SQL Server.

MSSQL with SSL: The target principal name is incorrect, The AD token retained by the user session is different from the result queried by the SQL Server. I have had users come to me with this on  "The target principal name is incorrect. Cannot generate SSPI context" Environment Details. User: DomainA\userA. Server: xxxxx.DomainB.com (this is a standalone SQL Server) SQL Server Instance: xxxxx\SQLx (version 2014) SQL Server Domain: DomainB. Current Windows Server for Domain Controllers (Windows Server 2012). This was an upgrade.

Solving the Target Principal Name is Incorrect – SQLServerCentral, "The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)". This appears to have begun shortly  (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.

SQL Server: The target principal name is incorrect., They are receiving the error message: Target Principal Name Is Incorrect. This SQL server has two instances on it. This issue is only being seen  Outlook 2016 says “The target principal name is incorrect” on my site's security certificate. I currently run a Plesk VPS, with 2 Domains hosted within. Some time ago, I had installed the 'Let's Encrypt' SSL Certificate onto my Plesk VPS.

Comments
  • I have this problem as well. Similar situation. Certificate created in OpenSSL (perhaps incorrectly). Differences: SQL Server 2014 Developer edition, Windows 7.
  • Incidentally the differences between 'force encryption' on server and 'force encryption' on client and 'encrypt connection' in SSMS are described here: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
  • grateful for your time, do you suggest me the use of IP address instead of Common Name?, because I set the alternate IP on certificate.
  • What you want is for the the CN on the certificate to match the address you use to connect to the server. You can do that by changing the certificate, by changing the connection address, or both.
  • Interestingly for me, the common name (CN) wasn't the server name I needed to use. But instead the fully qualified domain name (FQDN) which was in the Subject Alternative Names attribute.
  • This was my case installed SQL 2016 Enterprise with the default "MSSQLSERVER" account, which is a "virtual account". In my case, I had to add the account "NT SERVICE\MSSQLSERVER" by typing it in (Windows won't find it if you search). Here is a long and detailed discussion on account permissions for SQL. docs.microsoft.com/en-us/sql/database-engine/configure-windows/…
  • That is generally not considered safe to do in production.