1

I'm in the process of trying to set up a self signed certificate for connecting a jetty server to a development sql database. I'm following the instructions here: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=create-install-ssl-tls-certificate-sql-server but I've run into a bit of a roadblock. When I try to install the certificate in sql server manager, no certificates are showing up in the dropdown. I thought it might be that I needed to add the cert to trusted, so I did that and tried again with the same result.

My suspicion now is that the common name (CN) I chose when creating the cert does not match what sqlserver is expecting. According to the instructions

This must be the computer name (the local Windows computer name, not DNS name) of the SQL Server computer.

The sql server is on a remote machine. How do I determine what the correct CN is for the cert? Is it even possible to configure the certificates for that sql server using SQLServerManager running on my local machine? If not how, do I go about this?

Update:

I'm going through the process of verifying that my certificate is valid for use with sqlserver. I used the query:

DECLARE @Domain NVARCHAR(100)
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN

to obtain the FQDN and used that as the CN for the cert. I verified that the KeyUsage property of the cert is Server Authentication (1.3.6.1.5.5.7.3.1) . I'm using a pfx so the KeySpec option should be good. I added the pfx into Trusted Root Certification Authorities.

I'm still not seeing the cert as an option in the sql server manager so there must be something else I'm missing. The only thing I can think of is the requirement

The SQL Server Service Account must have the necessary permission to access the TLS certificate

but I'm not sure how to verify that or to fix it if it's wrong.

2
  • Do you successful start the sql server? I have the CN as my computer, but it kept refuse to start the service as the error 0x8009030d. Check certificates to make sure they are valid. Any Idea? Thanks Oct 4, 2022 at 2:50
  • AH I FOUND IT. I need to allow the service to access the PFX in Cert manager, learn.microsoft.com/en-us/troubleshoot/sql/admin/…. It's so sad.. Oct 4, 2022 at 2:55

1 Answer 1

2

Certificate name (in subject alternative name extension) must match the FQDN (or DNS name) of the host machine, not simply computer name. The name in certificate must match the Server or Data Source property in SQL connection string.

6
  • 1
    So if I have a connection string like "jdbc:sqlserver://tura-dev:1433;databaseName=JetNavDwh_Live;" the expected CN would be "tura-dev" correct?
    – pbuchheit
    Mar 31, 2022 at 12:47
  • 1
    Yes, that's correct. However if your server is part of AD DS domain it is recommended to use FQDN (e.g. tura-dev.example.com) instead of NetBIOS names to avoid ambiguity and use DNS for name resolution and use FQDN in connection string. But anyway, short name is valid too for internal use.
    – Crypt32
    Mar 31, 2022 at 12:50
  • Strange, I was using tura-dev and it wasn't showing up. Is there anything else that could be preventing the cert from showing up in the dropdown?
    – pbuchheit
    Mar 31, 2022 at 13:06
  • 1
    Maybe certificate is not trusted on SQL server or SQL server is part of domain (which requires FQDN). The full certificate requirement list at Microsoft Docs: docs.microsoft.com/en-us/sql/database-engine/configure-windows/…
    – Crypt32
    Mar 31, 2022 at 13:11
  • So, if my domain name was "FOO" the CN should be "FOO.tura-dev" ?
    – pbuchheit
    Mar 31, 2022 at 13:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .