0

I am trying to install an SSL cert into SQL Server 2017 on a Windows Server 2016 box. I've done a lot of research as to why the certs don't appear in the Certificate drop down (referred to as drop down) in SQL Server 2017 Configuration Studio. I realise the CN of the cert has to be FQDN which has to match the computer name exactly. It cannot be a wildcard cert either.

I'm testing on an internal company computer as we need to install a cert into sql server on a production box in the cloud.

My local server name is part of the domain company.local and has computer name = comp101. Full computer Name is therefore comp101.company.local and has a internal DNS comp101.company.local and an global DNS comp101.company.com so it can be accessed by public addresses.

Using MMC I created 4 certs with various FQDN and CN attributes but none appeared in the drop down. I created one self signed cert with IIS and set up the bindings and that does appear in the drop down. I set Force Encryption to Yes, and using a packet sniffer I can see communication to the sql server port is using TLS.

a) How do I install a new cert or my *.company.com wildcard cert into the local server?

b) Create and install a new cert for db.diff_company_on_the_cloud.com server when PC Name: WIN-XXXXXXXXX Organization : WORKGROUP

Thanks

David

David Janes
  • 69
  • 1
  • 6
  • Some things don't add up here: `.local` domains were deprecated years ago. The Server box is *not* part of the domain because it's in `WORKGROUP` so I'm not sure what's to be gained by making a self-signed cert (unless you want to turn on "Trust Server Certificate"). You *can* use a wildcard cert, but it must be `*.diff_company_on_the_cloud.com`, or you can use `db.diff_company_on_the_cloud.com`. You can use Let's Encrypt or another provider to buy a cert, or you can create a PFX certificate on your local PC against your internal CA and export it to the server – Charlieface May 27 '21 at 14:59
  • The self signed cert was just to check if that would be shown in the drop down on the local box, due to the 4 certs created by MMC not showing in the drop down. I haven't tried creating a cert for the server yet as the one i need to add the cert to hasn't been created yet. I dont understand how i can use a wildcard cert for *.diff_company_on_the_cloud.com or db.....com when the computer is not called that and is in a workgroup. Everything i've read says the computer name plus domain needs to match the cert exact;ly. Can you point to some document hat explains how to do this please? – David Janes May 27 '21 at 18:15
  • This is the post that led me to believe the server had to have a FQDN. My server is on a hosted server and not part of a domain. https://stackoverflow.com/questions/36817627/ssl-certificate-missing-from-dropdown-in-sql-server-configuration-manager – David Janes May 27 '21 at 18:22
  • The cert needs to match the name used in the connection string, the PC's actual machine name is not directly relevant (unless it's the same). This needs to be in "DNS name" or in "Subject Alternative Name". If it wasn't showing in MMC then either you don't have the private key on the machine, or the "Key Usage" didn't have "Server Authentication". Or you can also set the cert thumbprint at `HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Certificate` see also https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/ – Charlieface May 27 '21 at 19:35
  • AFAIK the restriction on CN name is only in the Management Console, not in the registry. And that is backed by the post you linked – Charlieface May 27 '21 at 19:38
  • For the internal self signed i was following https://techcommunity.microsoft.com/t5/iis-support-blog/sha-256-self-signed-certificate-for-windows-server-2012-r2/ba-p/376391. this fails on step 22/23 trying to import. ""The file type is not recognizable. Select another file"" I haven't tried to do anything on the cloud server as that doesn't exist yet. I would like to get the internal server working with LetsEncrypt if i could to prove it works. – David Janes May 27 '21 at 21:17
  • I've seen the mssqltips post and what stumped me was "The Common Name (CN) in the Subject property of the certificate must be the same as the fully qualified domain name (FQDN) of the server computer." 'The FQDN consists of two parts: the hostname and the domain name.' The cloud server isnt in a domain.... I've created and installed dozens of certs, into wls, ohs and tomcat. This one is confusing me though ! Thanks. – David Janes May 27 '21 at 21:19
  • FQDN of `someMachine` which is not in a domain is just `someMachine`. But as I said, if the MMC snap-in cannot find it, just do it via registry. I have had the exact situation you describe, and done exactly that – Charlieface May 27 '21 at 21:26
  • So I now have a server and a DNS ! hostname: SRV-DB-COP (no domain) DNS: aaaa.bbbb.cop.com (cop.com is the domain) Going to try and create a csr for the CA now... – David Janes Jun 08 '21 at 13:06
  • I created a self signed cert using Powershell. DNS = SRV-DB-COP CN = SRV-DB-COP This showed in MMC and was usable in SQL Configuration drop down. . But creating a cert with win-acme for a letsEncrypt cert, didn't show in the sql server drop down, and when the registry thumbnail was added, sql server wouldn't start. Win-acme will only create a cert if the DNS is a global DNS like aaaa.bbbb.cop.com. We needed to create and delete a TXT DNS record for approval. – David Janes Jun 09 '21 at 08:41

1 Answers1

0

You'll have to ask your cloud provider whether they issue certificates for diff_company_on_the_cloud.com is it's their domain. In Azure IIRC you must register a custom domain for the VM IP and use a certificate for that.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67