Can we defer SSL bind restart on Secondary until maintenance window?

Question

Post reply

Can we defer SSL bind restart on Secondary until maintenance window?

webrunner

SSC-Dedicated

Points: 31882
More actions
December 16, 2021 at 9:04 pm
Go to Answer
#3964551
Hello experts,
Our systems team has sent us an SSL cert expiration notice for a cert that is bound to one of our SQL Servers. Specifically, the cert is shown by:
SQL Server Configuration Manager > SQL Server Network Configuration > right-click Protocols for MSSQLSERVER > Certificate tab
In this case, the SQL Server in question is an Always On Secondary set to Manual failover. The database is TDE encrypted although I don't know how to check that the encryption cert is the same as the cert that is about to expire.
Given this scenario, is it possible to wait a couple of days until the official maintenance window to bind the cert and restart SQL Server? Or will this expired cert cause the Primary to stop working?
I'm trying to get the cert updated before it expires, but I'm just curious about the effects of expiration.
Thanks for any help.
-- webrunner
- This topic was modified 3 years ago by webrunner.
- This topic was modified 3 years ago by webrunner.
- This topic was modified 3 years ago by webrunner.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Eddie Wuerch

SSChampion

Points: 12535
More actions
December 16, 2021 at 10:31 pm
Answer
#3964583
An SSL certificate is for communication between two different endpoints. The worst that could happen is the Primary won't connect to the Secondary, and things will look like they do when you reboot the Secondary.
It is highly unlikely an SSL cert was used as a TDE cert. Even if it was, TDE certificates don't expire (from the docs for CREATE CERTIFICATE - EXPIRY_DATE entry at https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15): "However, expiration is not enforced when the certificate is used for database encryption or Always Encrypted."
Once something is encrypted with a Certificate, it stays that way, and that Certificate will always be valid for decrypting it.
To see which Certificate each TDE database uses:
```
SELECT DB_NAME(dek.database_id) AS DatabaseName, COALESCE(c.name, k.name) AS CertOrKeyName, 
       dek.encryptor_type, dek.encryptor_thumbprint, dek.encryption_state
  FROM sys.dm_database_encryption_keys dek LEFT JOIN
       master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint LEFT JOIN
       master.sys.asymmetric_keys k ON dek.encryptor_thumbprint = k.thumbprint
 WHERE dek.database_id > 4;
```
Eddie Wuerch
MCM: SQL
December 20, 2021 at 5:39 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

webrunner SSC-Dedicated Points: 31882 More actions · Answer 1

Thanks so much, Eddie. This is the information I needed.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31882 More actions · Answer 2

PS Actually, I have one more question.

I am still in this situation where I can't open SQL Server Configuration Manager. The resolution appears to be to restart the Windows Management Instrumentation service, but when I try I get an Access denied error. Our Systems admin thinks that might be due to antivirus and that restarting the entire server may resolve this issue.

To that end, my question is - Is there any way besides the Configuration Manager GUI to find out what SSL certificate is bound to the MSSQLSERVER network protocols?

Thanks again for any help.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

Is there any way besides the Configuration Manager GUI to find out what SSL certificate is bound to the MSSQLSERVER network protocols?

Check the registry for what is configured. Fire up regedit.exe and drill down to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

The "MSSQL.x" shown in the path above will be specific to your install as "MSSQL<version#>.<YourInstanceName>". SQL 2016 is Version 13, so if the instance hosting the Secondary Replica is a SQL 2016 instance named "Inst01", then the registry folder will be named:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.Inst01\MSSQLServer\SuperSocketNetLib\Certificate

That will hold the thumbprint of the configured certificate.

Does this instance only host Secondary Replicas or are there Primary replicas there as well? Will there be any downtime to rebooting that replica instance?

Eddie Wuerch
MCM: SQL