1

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 ?

3

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 
  • Dan, add in that the cert isn't persisted anywhere ,etc., and I'll remove my answer. You added much more than I wanted to write given the question :) – Sean Gallardy Oct 29 '17 at 17:23
  • 1
    @SeanGallardy-Microsoft, done. There's a lot of magic that happens under the covers with TLS security that I wish were easier for we mere mortals to understand. – Dan Guzman Oct 29 '17 at 17:38
  • @SeanGallardy-Microsoft - saw your original post, feels good to feel assured that my understanding is right, thanks a million – Noob Nov 1 '17 at 16:32
  • @DanGuzman thanks for coming back to read this thread. appreciate it. I got this error when trying to create event session using the script that you had given earlier -> Msg 25623, Level 16, State 1, Line 1 The event name, "sqlsni.trace", is invalid, or the object could not be found -- I am on sqlserver 2014 – Noob Nov 1 '17 at 16:44
  • @Noob, sorry about that but the XE event with the handshake info was added in SQL 2016 SP1 and not available in SQL 2014. – Dan Guzman Nov 2 '17 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.