Join Log in
Score:0
avatar Server

How to determine the common name (CN) for a microsoft sql certificate?

kz flag
pbuchheit

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.

54
0 + 0
Score:2
avatar Server
cn flag
Crypt32

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.

0 + 0
kz flag
pbuchheit
So if I have a connection string like "jdbc:sqlserver://tura-dev:1433;databaseName=JetNavDwh_Live;" the expected CN would be "tura-dev" correct?
0
Reply
cn flag
Crypt32
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.
0
Reply
kz flag
pbuchheit
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?
0
Reply
cn flag
Crypt32
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
0
Reply
kz flag
pbuchheit
So, if my domain name was "FOO" the CN should be "FOO.tura-dev" ?
0
Reply
kz flag
pbuchheit
@ 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.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.