Connection Encryption - Protocols not showing the certficiate

  • Following https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine to setup SSL connections.

    Have got through to the part when you need to enable the certificate in the protocols section, but the certificate isn't showing up in the drop down box.

    My guess is that the cert is a PFX but could be mistaken

    This is going onto a AOAG cluster, have requested the cert to have the ClusterFQDN as the CN and the Cluster, AOAG Names and the nodes as the Subject Alternates Names.  The cert is installed on all 3 nodes of the cluster.

    Anyone any ideas as to why the cert isn't showing in SQL Config Manager?

    Thanks
    Ant

  • So tried to set this up again on a brand new clean test cluster with 2 AOAG's, again the cluster doesn't like the certificate.

    Tried doing it stand alone and is all good, so got to be an issue with SSL on Clusters.

    Anyone any ideas how to get this to work?

  • Hi Steve

    Yes the cert is for the FQDN, example CN = Cluster.mydomain.com and the SAN = Cluster.mydomain.com, AG1.mydomain.com, AG2.mydomain.com

    Will take a look at the links and let you know.

  • I know I've had issues at times with a PFX, but can't duplicate it now. Good luck. This is a PIA when I've had to do it. I prefer IPSec if I Can get away with it.

  • Just had a look at Derek's blog, he doesn't create the certificate for the cluster, but for the individual machine with the AG as a SAN, will get the clients crypto guys to create me 3 certificates with the machine NetBIOS names and the AG names and give that a try.

    CN = Node1.mydomain.com
    SAN = Node1, AG1, AG1.mydomain.com, AG2, AG2.mydomain.com

    CN = Node2.mydomain.com
    SAN = Node2, AG1, AG1.mydomain.com, AG2, AG2.mydomain.com

    CN = Node3.mydomain.com
    SAN = Node3, AG1, AG1.mydomain.com, AG2, AG2.mydomain.com

  • OK, so the individual certificates didn't work either.

    Certificate imported fine into MMC, permissions granted, but SQL Config Manager still doesn't want to acknowledge the fact a certificate is present.

    This is becoming a real PIA.

  • Thanks Steve.

    Tried adding the thumbprint but then the service wont start with the error "TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.

    My guess is that there is something up with the traversing back to the issuing CA, as this is a highly secure site we have to get the certs from one machine which I am guessing is not on the same domain so its probably getting its knickers in a twist trying to validate the certificate.

  • That might be it. Certainly there's a bit of mystery for me in how this actually happens.
    I think the SSL implementation is a little hokey in SQL Server and a pain. I don't envy you here. Can you open a ticket with MS?

  • Steve Jones - SSC Editor - Tuesday, October 10, 2017 9:48 AM

    That might be it. Certainly there's a bit of mystery for me in how this actually happens.
    I think the SSL implementation is a little hokey in SQL Server and a pain. I don't envy you here. Can you open a ticket with MS?

    Yes think that is going to be the next option as something is definitely off

  • So with the help of MS Support and their SSL checking tool, there where a couple of typos in the CN, fixed them and the cert shows in config manager

  • Can you share the typos? Wondering if this is something I've done right and wrong at different times.

  • Steve Jones - SSC Editor - Thursday, October 19, 2017 11:38 AM

    Can you share the typos? Wondering if this is something I've done right and wrong at different times.

    It same down to having the have the CN in upper case to match the NETBIOS name and also a O (Oscar) where it should of been a 0 (zero) from the CN which I completely missed as the machine names here can vary between who set them up from the infrastructure team.

  • Thanks, that upper case part is good to know. Glad NETBIOS is still working as smooth as ever.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply