SQL Server certificates and windows certificates

Question

Hi

I have been working for a while with TDE and Data in Transit Encryption (I call it DiTE:-) and it looks like I miss some basic understanding:
For TDE I created a SQL certificate by T-SQL commands which is stored in sys.certficates system view, but not in the windows certificate store ('Cert:\LocalMachine\My')
For DiTE I created a certificate by certlm.msc (and PowerShell) and then use it by SQL Configuration Network Protocol UI with force encryption.
So far so good.
My question is what is the relation between these two types of certificates?
Is the certificate created by windows MMC (or certlm.msc) can be used by TDE? is it done by export and CREATE CERTIFICATE command?
And vice versa - Can I create a certificate by T-SQL commands and use it for DiTE?

Thanks in advance for any clarification

Uri

Answer 1

Hi @URIKLIL ,

First of all, I want to confirm with you that this is TSL/SSL you are referring to(you called it DiTE:-)?
TSL/SSL and TDE are different things, you can't mix them up.

Here are some definitions you can check.

1. SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The TLS encryption is performed within the protocol layer and is available to all supported SQL Server clients.
2. TDE does real-time I/O encryption and decryption of data and log files. The encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key. It's secured by a certificate that the server's master database stores or by an asymmetric key that an EKM module protects.
   TDE protects data at rest, which is the data and log files. It lets you follow many laws, regulations, and guidelines established in various industries. This ability lets software developers encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

is it done by export and CREATE CERTIFICATE command?

For TDE, the key and the certificate itself to protect the encrypted data are stored in the database.
You can see the certificate using the following script：
USE master
go
SELECT * FROM sys.certificates;

You need to back it up again to a file, this is to ensure that the database is available.
See: BACKUP CERTIFICATE (Transact-SQL)

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.