TDE is at rest encryption. If we are using SSL which encrypts in transit traffic, then can I use SSL cert somewhere in the below TSQL steps? (Basically, I want to avoid TDE self-sign cert) I’m looking for some help regarding in below areas
1 – How can I not use self-sign cert in the TDE encryption. If I am following the below steps/ commands, then I will be using self-sign cert with my password…which I’m trying to avoid.
2 – Is it possible to use SSL cert info in the below commands? So that way, I can avoid self-sign cert.
3 – If I’m wrong in my approach please advise some helpful tips here. Thanks!
Current approach: -
Step 1: Install SSL cert on DB server
Step 2: Encrypt DB (using TDE)
1. Create a master key.
2. Create or obtain a certificate protected by the master key.
3. Create a database encryption key and protect it by using the certificate.
4. Set the database to use encryption.
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere';
GO
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO
USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
ALTER DATABASE <DB>
SET ENCRYPTION ON;
GO
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\temp\TDE_Cert'
WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='InsertStrongPasswordHere')
February 23, 2022 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
1 – How can I not use self-sign cert in the TDE encryption. If I am following the below steps/ commands, then I will be using self-sign cert with my password…which I’m trying to avoid.
Any Certificate you use for TDE must be created without a password or else the engine cannot access the certificate to perform crypto ops. You can create the certificate anywhere that will generate and export X.509 certificates, but when you import that Certificate to SQL Server, you cannot place a password on it. Any DBA or other person with sufficient privileges (ALTER ANY CERTIFICATE) can export a copy of the certificate that can be used to decrypt the database elsewhere.
2 – Is it possible to use SSL cert info in the below commands? So that way, I can avoid self-sign cert.
'Self-signed' vs. 'not self-signed' isn't terribly important with TDE certificates. To SQL Server they are just asymmetric key pairs that follow a specific set of rules. Using an SSL cert for TDE is possible, noting that said certificate must be loaded into SQL Server as an exportable password-free certificate. 'Possible' is a long way from 'a good idea' here.
The value gained from using an external system to generate the certificate and importing the file into SQL Server is having an external system to track your certificates and (most importantly) back them up in a secure manner. TDE certificates are NOT included in database backups, and must be separately backed up and maintained in order to recover from a disaster.
3 – If I’m wrong in my approach please advise some helpful tips here. Thanks!
If your goal with this question is to just avoid self-signed certs, then any X.509 certificate will work. Export/convert the certificate into separate public and private key files and import them with CREATE CERTIFICATE [...] FILE = '<path_to_cert_file>' WITH PRIVATE KEY (FILE = '<path_to_private_key_file>', DECRYPTION BY PASSWORD = '<private_key_password>'); See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2016
However, if your goal is to get away from the concerns raised by the simple security surrounding these critical crypto objects, the answer may be to not use certificate-based TDE, but instead to move up to EKM-based TDE (requires Enterprise Edition on Windows Server for versions prior to SQL2019, Standard or Enterprise Edition on Windows for SQL2019 and later).
Instead of using locally-generated and locally-managed certificates, EKM (Extensible Key Management)-based TDE uses keys hosted on one or more external HSM and/or KMS systems. An HSM (Hardware Security Module) is a networked physical or virtual device that performs key operations, and a KMS (Key Management System) is a software service that may sit 'in front' of the HSM as middleware between a client and the HSM. From SQL Server's point of view, HSMs and KMSs are the same thing. An EKM can also supply the keys for encrypted database backups.
Examples of HSMs include devices from Gemalto and Thompson that sit on your network; a KMS example is Azure Key Vault, where clients interact with web services that make commands to the backend HSMs on the client's behalf. A 'Cloud HSM' is just an HSM on somebody else's computer, usually with a KMS in front of it to handle the network calls and provide a little value-add. The 'Extensible' part of 'EKM' comes from SQL Server having no built-in support for any specific EKM solution. Each vendor's solution will include a collection of files, called the Provider Library, that SQL Server will call to ask the EKM backend to perform work. Each solution will have its own way of being configured (edit a text file vs. use an app vs. ...) to set up endpoints and the method for SQL Server to authenticate on the EKM solution.
Note: vendors and products listed are just examples. There are many vendors and many products; this is not an endorsement of any of them. Full disclosure: I work for a company that sells data encryption services. Although none of the solutions are applicable here, I am respectfully avoiding any fitness claims, recommendations, or endorsements of different vendors' EKM offerings.
With EKM, crypto ops that require the HSM key's private key are performed on the HSM - the private key never leaves the device. Keys can (should) be created as 'unexportable', meaning the device will prevent anyone from making a copy of the key.
Traditionally, EKM admins are a different team than database admins, and spreading the crypto responsibilities around provides clear separation of duties. EKM ratchets up the cost of a solution, so it's generally used only when you need the enhanced security and/or auditable separation of duties.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply