Encryption using Windows cert issued by CA vs SQL Server generated cert

Question

Encryption using Windows cert issued by CA vs SQL Server generated cert

icester

Ten Centuries

Points: 1093
More actions
January 16, 2023 at 7:55 pm

#4138310
What is the benefit in using a Windows Certificate issued by a CA (Certificate Authority), vs using a certificate generated from within SQL Server using a master key, for encrypting data at rest? Example code:
```
/* create master key */
create master key encryption by password = 'abc123'

/* generate cert based off of master key */
create certificate masterCert
   with subject = 'issued by SQL Server with master key'
   expiry_date = '20231031'

/* generate cert based off of Windows Cert issued by CA */
create certificate WindowsCert
   from file = 'C:\Temp\WindowsCert_4.cer'
   with private key (
   file = 'C:\Temp\WindowsCert_4.pvk'
   decryption by password = 'pass'
)
```
If I understand correctly, SQL Server now sees no difference from the 2 certificates generated from the above scripts (other than identifiers like Thumbprint, of course). The only potential benefit to using a Windows Cert, that I can think of, is at the client application side. The programmer could decide to do validation checks on the Windows Cert and compare that to the cert within SQL Server. Are there any other advantages to using a Windows cert that I'm missing?
Thanks!
- This topic was modified 1 year, 10 months ago by icester.
- This topic was modified 1 year, 10 months ago by icester.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

You are correct in that SQL Server cares not what the source of the certificate is - an X.509 cert is an asymmetric key pair in a specific format. It's not used for identity, so there's no need to trust the cert in any capacity.

IMHO, the major benefit to creating certificates in an external store is certificate protection - you need a reliable way to store, access, back up, and restore certificates in DR scenarios. The encryption cert is not included in the database backup, and that database backup cannot be usefully restored without the cert. You can create and host this service yourself - no need for the expense of a globally-verifiable public CA signed certificate.

This means you must also game out some disaster scenarios and ensure you can recover from them. It can also provide some Separation of Duties to have the team managing the CA be separate from the DBAs.

Note that any certificate used for encryption at rest (such as TDE) must be imported into SQL Server without a password so SQL Server can access the cert's private key without it. Any admin on the SQL instance can export that certificate. If that is an concern, based on what data is in that database and what regulations you may be required to follow, then you can lock down encryption keys and prevent export by exploring Extensible Key Management (EKM) to use an external Hardware Security Module (HSM) or Key Management Service (KMS) to control and manage those keys.

Eddie Wuerch
MCM: SQL

icester Ten Centuries Points: 1093 More actions · Answer 2

Thanks for the information, Eddie!

After creating the SQL Server certificate, from the Windows certificate, is there a security risk with keeping the complete certificate in the Windows store (cert & private key)? A concern that I have is that the cert could easily be exported by anyone (hacker) who gains server level access. Would it be better to remove the private key from the Windows store's certificate, and then keep a copy of it offsite, after the SQL Server cert has been created from it?

William McQuinn SSC Veteran Points: 202 More actions · Answer 3

I am trying to find Microsoft documentation for this lack of advantage (in terms of encryption, notwithstanding the EKM benefits). I have been wrestling with getting a trusted certificate into SQL Server 2019 and 2022. I can backup the self-signed cert and get it into another SQL Server easily, but the trusted cert won't go in. I can't install and use pvkconverter for business reasons, and I've tried every opeSSL trick I can look up. I've pulled the pvk out of the pfx, and tried both using the original Entrust cert and the one pulled out via openSSL, both in base64 and DER encoded, and all give the same error message. I've read from different sites that the private key must be and cannot be password encrypted (so of course I've tried both). I've been working on this for months hand have literally run out of things to try. If I can't import the trusted certificate, I'll settle for definitive (ie, Microsoft or government) documentation stating that there is no advantage to having a trusted certificate for encryption purposes.