0

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.

pbuchheit
  • 139
  • 10

1 Answers1

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.

Crypt32
  • 6,414
  • 1
  • 13
  • 32
  • 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 '22 at 12:47
  • 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 '22 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 '22 at 13:06
  • 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: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15#certificate-requirements – Crypt32 Mar 31 '22 at 13:11
  • So, if my domain name was "FOO" the CN should be "FOO.tura-dev" ? – pbuchheit Mar 31 '22 at 13:40
  • @ Crypt32 See my updates to the original post. I followed the steps in that link, and the only thing I can't figure out is how to correctly set up the permissions. – pbuchheit Mar 31 '22 at 15:28