SQL Server Self Signed Cert - no private key?

Question

I recently turn on SSL encryption using SQL server self-signed cert then I start to ponder more about

1) encryption algorithm, authentication method

It seems that the encryption/hashing algorithm used depends on the SChannel negotiation between the client/server's OS. - fine.

2) key length of the self-signed cert.

I tried query this

select  name, pvt_key_encryption_type_desc from sys.certificates

##MS_SQLResourceSigningCertificate##    NO_PRIVATE_KEY
##MS_SQLReplicationSigningCertificate## NO_PRIVATE_KEY
##MS_SQLAuthenticatorCertificate##  NO_PRIVATE_KEY
##MS_AgentSigningCertificate##  NO_PRIVATE_KEY
##MS_PolicySigningCertificate## NO_PRIVATE_KEY
##MS_SmoExtendedSigningCertificate##    NO_PRIVATE_KEY
##MS_SchemaSigningCertificate990F36EF1B3577FE5687C7465F0A5135DE9E6834## NO_PRIVATE_KEY

q1) can I check if the cert use for SSL encryption is the MS_SQLAuthenticatorCertifcate ?

q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY" ? In all my understanding, a cert/asymmetric encryption will involved a PUBLIC key (provided in the cert) for encryption, and the owner of the cert will have its PRIVATE key for decryption ?

Why is this not the case here ?

Answer 1

This is too big for a comment on Sean's answer so let me add to it.

q1) can I check if the cert use for SSL encryption is the MS_SQLAuthenticatorCertifcate ?

According to the Principals (Database Engine) doc the ##MS_SQLAuthenticatorCertificate## cert is used for an internal certificate-based login.

The self-signed certificate generated during instance start-up is not exposed by a DMV, at least as far as I know. EDIT: Also, Sean mentioned in his answer this certificate is resident only in the SQL Server process memory space and is removed upon SQL Server shutdown for security reasons.

q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY" ? In all my understanding, a cert/asymmetric encryption will involved a PUBLIC key (provided in the cert) for encryption, and the owner of the cert will have its PRIVATE key for decryption ?

The actual SQL Server self-signed certificate is an X509 certificate with a public/private key pair. However, note that TLS uses a symmetric key (same key used for both encryption and decryption) to encrypt/decrypt messages over the wire for performance reasons. This session-specific key is generated during the TLS handshake and the self-signed cert (or provisioned one) is used to generate and protect the secrets exchanged, not to encrypt the subsequent messages. This Microsoft doc (https://technet.microsoft.com/en-us/library/dn786441(v=ws.11).aspx) describes how the Transport Layer Security (TLS) protocol works and provides links to the IETF RFCs for TLS 1.0, TLS 1.1, and TLS 1.2.

You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:

CREATE EVENT SESSION [tls] ON SERVER 
ADD EVENT sqlsni.trace(
    WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%')))
ADD TARGET package0.event_file(SET filename=N'tls_trace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Below is an example of the event text field using a self-signed certificate from the trace against SQL Server 2017

SNISecurity Handshake Handshake succeeded. Protocol: TLS1.2 (1024), Cipher: AES 256 (26128), Cipher Strength: 256, Hash: SHA 384 (32781), Hash Strength: 0, PeerAddr: ::1 

Answer 2

I am not so deep into SQL server that I can precisely answer all your questions. So let's begin with what I know for sure.

Question q2)

You are absolutely right. Every software which communicates with other parties over TLS / SSL connections definitely needs a private key which matches its public key, because SSL / TLS exchanges the actual encryption keys by means of asymmetric RSA (or nowadays, EC) encryption. Period.

Please note that the fact that a private key is needed is not due to the fact that there are certificates involved. Rather, it is the principle of asymmetric RSA and EC encryption that there must be a private key matching the public key, regardless of the actual implementation.

The public key and other data (e.g. the a computer or organization name, the signage of trusted root or intermediate authorities, and so on) are usually stored in a certificate which one party presents to another, thereby claiming a certain identity. It is up to the other party to check if the claim is true, i.e. if the party presenting the certificate really is what it pretends to be.

The private key matching a certificate's public key may be stored in unexpected places.

For example, I have generated the SSL certificate for my SQL Server 2012 using certreq (for details, see here and here). When I issued the final command of the process, that is,

certreq -new -f req.inf test.cer

a certificate was created and automatically imported into the Windows certificate store (I later used SSCM to activate that certificate for the instance in question), and that certificate's private key was put into a file with a cryptic name in the following location:

C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\

I am absolutely sure that I do not know all methods to create a certificate for SQL Server's SSL / TLS authentication, but once again, I am also sure that all methods involve creating a private key, and that SQL server needs this key to authenticate against other parties (clients, or (itself acting as a client) servers).

After all, the key point of certificate-based server authentication is that the client checks whether the server can prove that it has the private key which matches the server certificate's public key. If the server does not have that key, the certificate does not belong to that server, and the client rejects to continue the connection.

In my case, for example, SQL Server won't even start if it can't read the private key matching the TLS certificate from the machine key store mentioned above.

Question q1)

To compare the certificate SSL / TLS to other certificates, I would do the following:

a) Identify the SSL / TLS certificate:

Start SSCM (SQL Server Configuration Manager; if you don't have it installed, add it to your installation via SQL Server setup - it is part of the configuration / management tools). In the left pane, expand "SQL Server Network Configuration" and (still in the left pane) right-click "Protocols for xxx" (where xxx is the instance in question). From the context menu, select "Properties".

This should open a dialog box with multiple tabs, among them a tab titled "Certificate". Activate this tab and press the button "View ...". This should open the usual Windows dialog where a certificate's details are presented. In your case, the tab "Details" in this dialog is the interesting one. Among others, it contains the certificate's thumbprint which is unique. Note down that thumbprint.

In my case, the thumbprint is ce8623c589bc1a4a7733cb6a5ac43824838a4415.

b) Identify the other certificates

You can query the other certificates like that:

select  name, thumbprint from sys.certificates

which (in my case) gives the following result:

##MS_SQLResourceSigningCertificate##    0x6D5F2F7CA5696AC5333BABB0E4AD1E96DD99DBC7
##MS_SQLReplicationSigningCertificate## 0x5B630A36C16BB9D79982A64BC0B3B584E7485B31
##MS_SQLAuthenticatorCertificate##  0x40FCDF6DBE46A1201A7CBFB7E6421172F67EE9F7
##MS_AgentSigningCertificate##  0x62D95F0D5A702BB990BB688BAC1DAB83E6B6487F
##MS_PolicySigningCertificate## 0x8EFAD562CDCF43FFC3618BE6C8CBE11FB62C19B6
##MS_SmoExtendedSigningCertificate##    0xB36E8511BA3A33BED8A6F848E100B7DF691AAD8B
##MS_SchemaSigningCertificateFBBED91EB3ECA494D27028E6F5921426EBFA28A8## 0xC60791200612C0F13D047F2250BCA93865646097

Now compare these thumbprints to the SSL / TLS certificate's thumbprint obtained in step a). This clearly shows that the SSL / TLS certificate is not identical to the MS_SQLAuthenticatorCertificate.

By the way, I don't know what pvt_key_encryption_type_desc from your example actually contains or means, and what those ##MS_... certificates are used for. But once again, SQL Server ultimately needs to know the private keys of the certificates which it uses to authenticate itself against clients (or other servers which insist on the client presenting a certificate).

So I suspect you misunderstood what NO_PRIVATE_KEY actually means (I had no chance to misunderstand it because I have never seen it until now :-)).

I should probably not write anything more, because the following really is just a guess, so don't take it too serious. However, there are certificate formats which directly include the private key as part of the certificate itself. Perhaps NO_PRIVATE_KEY means that the respective certificates don't include the private key directly; in that case, it is stored elsewhere, at least for those certificates which SQL Server actually uses to authenticate itself.