I've applied certificates and enforced encryption on two SQL 2016 SP2 servers. Using the same login credentials I can create an encrypted connection to one of the servers by selecting 'encrypt connection' in the connections options. With the second server unless I also select 'trust server certificate' I get a connection error:
A connection was successfully established with the server, but then an error occurred during the login process.
provider: SSL Provider, The target principal name is incorrect
Error Code -2146893022
The target principal name is incorrect
If I select 'trust server certificate' as well as 'encrypt connection' in the connection options it connects fine and I can see encrypted connections in dm_exec_connections so the 'incorrect principal name' error seems to be false clue.
Why would you have to select 'trust server certificate' as well as 'encrypt connection' in the connection options on one server and only 'encrypt connection' on another?
Thanks
The likely reason - your certificate isn't issued to the same name as you are using to connect.
For example, SERVERA is not the same name as SERVERA.corp.com but if you are on the same domain as that machine, it would appear they are the same thing computer. If your connection doesn't match the name in the certificate exactly, you will get the error "Target principal name is incorrect" (or similar).
I would check the name on the certificate that you used for the servers. Generally, the certificate for server A is going to be different than the certificate for server B. There are exceptions with wildcard certificates mind you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 12, 2021 at 3:32 pm
thanks, that does explain it. when I use the fully qualified domain name as the server name it works without the 'trust server certificate' option. So when the cert was created maybe they didn't add all the DNS options in.
Thanks very much for replying, Hugh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply