Great video. clearly explained. Just need a bigger fonts with lower screen resolution that will make it perfect. Could please you also do a video for adding encrypted database into Alwayson availability group? I found your logic and explaination in better than others.
Thank you very much Sir for this great step by step video! I was a little bit confused on the step to backup the Certificate. I thought it needs the private key in order to backup the certificate. I realized later that the private key was also being backup at the same time.
Yes that is correct. I used the with private key switch when I backed up the certificate. You will get 2 files - one for the certificate and one for the private key. As you indicated the certificate is not much use without the private key. If you do not backup the private key when you backup the certificate you are only backing up the public key.
0:41 Explaining TDE Process (Create master key, creating certificate via the master key, creating database encryption key via certificate, then encrypting the database via the DEK) 6:17 Creating the Certificate via T-SQL 10:34 Explaining what will be encrypted. 13:28 Backing up the certificate
Very smooth and broad description! I liked the subtle sense of humor as well. :) Thank you. A quick hassle: What happens if we want to apply TDE on a database in Azure SQL Server?! since there's no Master in the database in Azure! I stumbled in the first step.
With Azure it depends. If you implement an Azure SQL Database it automatically has TDE enabled by default so you do not have to do anything. Remember that an Azure SQL Database is an isolated database (similar to a contained database on an on-premise SQL Server Instance). Since Azure SQL databases do not use instances the encryption is done at the server level. If you use an Azure SQL Server Managed Instance then TDE is at the instance level and all databases using the instance are automatically enabled with TDE. If you use an Azure VM then just do it like I showed. If you have older Azure SQL Databases already deployed then you would need to manually enable the TDE for the databases. I have not used Azure Synapse yet but those must be enabled manually. You can read more about it here. Just go to the azure.portal and enable TDE if you need it. docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?tabs=azure-portal
That was a great video, thanks OdinTree. One question that was already asked below but I would like clarification on. Is backing up the masterkey not necessary? Since you created a new master key on SERVER2 with a new password is backing up that not required?
Hello Taylor, Thanks for the question. Backing up any key is not technically required but should be done. Even the database master key should be backed up. Here is the reason directly from MS docs: "The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location."
Good video.. Nicly explained.. just to ask one thing, will we get same certificate error if we copy the mdf and ldf to another server and attach the database? Assume the new server doesnt have any keys and certs
If you do not have the original certificate or keys then you cannot open the database on a new server. That is one of the main functions of TDE. So that if somebody obtains a physical copy of the database they cannot restore it on another server to look at the data.
A quick question if I may :-). In the end, you deleted the Master Key. Does this need to be deleted? I presume this key resides at the Instance level and if so, do you only need this to be created once and then it is used here on when creating the certificate? If you have multiple databases in the instance, I presume every database will have it's own certificate and it's own database encryption key? Excellent video and I was able to replicate the whole exercise without issues. Thank you
OK I will attempt to answer your questions: Does the master key need to be deleted: No Do you need a new certificate for each database: No - serves no purpose to create multiple certificates on the same server. Each database has its own database key (use different passwords)
Hello Sen, I do not think you could restore a TDE encrypted database to SQL Express since SQL Express does not support Encryption in anyway. I have never attempted it but you could and verify it for all.
Good evening. I was able to follow all your steps and everything worked but I have a few questions since I have several different databases. For the first step of creating the master key, is this done once at the server level or master database level only and can be used for several databases? Or, is this master key encryption step done for each database? Next, creating the database encryption key with the algorithm by server certificate, is that again done once and used for any database or is this this a database specific step and should be done for each database? Lastly, alternating a specific database and turning on encryption is obviously specific to each database so no response needed for this really. I hope you can provide me an answer as I did not know how to contact you directly.
Hello RenewalTracker, Sorry for the late response as I did not see this message for some reason. For each server instance there is a single Service Master Key (this will automatically be created when SQL Server is setup), you will create the database master key and certificate once (you can use the same certificate for many user databases), and then create a database encryption key for each database.
@@odintree5704 Thank you very much. So, in your video as you refer to the Service Master Key, that is specific to the server and is a single value. When you refer to the database master key and certificate, that can be a single value used for all databases within SQL Server on that server. Finally, when you refer to a database encryption key, that is specific to a single database and for each database within SQL Server on the server you would have a unique value. Do I have it correct?
@@renewaltracker Sounds like you have it. See this link from Microsoft and scroll about 1/4 of the way down and you will see an image with the different keys and certificates. A picture is worth a 1000 words. docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
@@renewaltracker @OdinTree, Thank you both for clarify this. It was explained clearly. However, one thing escaped me. @OdinTree did explained in his video how to backup the cert. and database encryption key and this works but I am not sure how to do it if you have more encrypted databases. If you use one certificate for more databases and each database has its own private key. Because backup query shows that we are doing this from master key. Backup query explain how to backup certificate with private key (single), but how backup query should look like if you have more databases encrypt with same certification?
Hello Kornelije, Here is an explanation of the keys and certificates that I gave to another question. It will hopefully help explain what is happening even with multiple user databases and why we just need to backup the certificate and private key to restore any user database that has been encrypted. Hope this helps, if not please clarify your question. The DEK (database encryption key) resides in the user database that you encrypted. This key is needed to decrypt and encrypt your database. So if someone tried restoring your database they would also restore the DEK. You would now think they could decrypt your database. But they cannot because the key is protected (encrypted) by the certificate which resides in the Master database. You therefore need to restore the certificate also. The certificate is what is needed to decrypt the DEK. But you need the private key that is associated with the certificate. The private key is encrypted by the DMK (this is also in the Master database). In turn the DMK is protected by the Service Master Key (SMK) and just to confuse you more the SMK is protected by the operating system. So it sounds like we need all the keys back to the operating system, but luckily for us that is not the case. We can restore just by going as far back as the certificate and private key. When you backed up the certificate you included the private key and a password. This password was used to encrypt the private key. But you may recall I said that the private key is already encrypted with the DMK. So this looks like we just encrypt a key that is already encrypted. But the BACKUP CERTIFICATE command actually backups the unencrypted private key and therefore we supply a password to encrypt the unencrypted version of the key. Now I can retrieve the unencrypted key with the supplied password. Now when I recreate the certificate to another server or instance and supply the password I restore an unencrypted private key. But immediately the DMK of the new instance will protect this key by encrypting it and storing it in the new Master database.
This was a great overview, really appreciate it. What is the purpose of the password on the master key (in your case 'Access999')? It was never used after the master key was created, nor was it backed up, used for the restore, or even used to remove encryption from the database.
This is a great question and I hope my explanation below does it justice. It may be long winded but I am trying to give a full explanation rather than not enough information. The DEK resides in the user database that you encrypted. This key is needed to decrypt and encrypt your database. So if someone tried restoring your database they would also restore the DEK and therefore you would think they could decrypt your database. But they cannot because the key is protected (encrypted) by the certificate which resides in the Master database. You therefore need to restore the certificate also. The certificate is what is needed to decrypt the DEK. But you need the private key that is associated with the certificate. The private key is encrypted by the DMK (this is also in the Master database). In turn the DMK is protected by the Service Master Key (SMK) and just to confuse you more the SMK is protected by the operating system. So it sounds like we need all the keys back to the operating system, but luckily for us that is not the case. We can restore just by going as far back as the certificate and private key. When you backed up the certificate you included the private key and a password. This password was used to encrypt the private key. But you may recall I said that the private key is already encrypted with the DMK. So this looks like we just encrypt a key that is already encrypted. But the BACKUP CERTIFICATE command actually backups the unencrypted private key and therefore we supply a password to encrypt the unencrypted version of the key. Now I can retrieve the unencrypted key with the supplied password. Now when I recreate the certificate to another server or instance and supply the password I restore an unencrypted private key. But immediately the DMK of the new instance will protect this key by encrypting it and storing it in the new Master database.
@@odintree5704 Excellent explanation. It is quite tricky to understand TDE given that there are so many components that are overwhelming but your explanation was great. Thank you so much for sharing your knowledge.
Will this encription need an access key to read / update data from an application? Or this encryption is only for just in case someone gets a copy so they can't restore it???
TDE is transparent to the front end application. You do NOT need to make any changes what-so-ever to your front end application to interact with a TDE enabled database.
Sorry qqq - for some reason CZcams did not display your comment to me. You are correct that TDE does not affect the app. Keep in mind there is a small performance hit since data needs to be constantly encrypted and decrypted. You could get up to a 3% performance hit, but depending on what is in the cache this could be reduced.
Thank you for this tutorial!!
This is by far the BEST tutorial on TDE I have seen anywhere online. YOu are such a great instructor and you just WON OVER a NEW FAN!!!!!!
Thanks for the kind words and I am glad the video was of help to you.
This was an amazing video, thank you!
For anybody wondering about time to encrypt like I was, I have a 43.6GB DB that took exactly an hour to encrypt.
Thanks for sharing
Thank you so much! you did things simply and step by step, it was easy to follow, thanks!
Thank you very much. It was a nice initial section for TDE.
This was a great overview, really appreciate it. Thank you very much.
Thanks, OdinTree.
Just what I needed to set up a TDE on SQL 2019.
Glad it was helpful.
Great video. clearly explained. Just need a bigger fonts with lower screen resolution that will make it perfect. Could please you also do a video for adding encrypted database into Alwayson availability group? I found your logic and explaination in better than others.
the best tutorial on data encryption. Thank you
Thanks. You save me a lot of time.
Thank you man . you are explaining very clear .
Glad it helped
Thank you very much Sir for this great step by step video! I was a little bit confused on the step to backup the Certificate. I thought it needs the private key in order to backup the certificate. I realized later that the private key was also being backup at the same time.
Yes that is correct. I used the with private key switch when I backed up the certificate. You will get 2 files - one for the certificate and one for the private key. As you indicated the certificate is not much use without the private key. If you do not backup the private key when you backup the certificate you are only backing up the public key.
Thank you very much. It was a nice section for TDE
You're welcome
0:41 Explaining TDE Process (Create master key, creating certificate via the master key, creating database encryption key via certificate, then encrypting the database via the DEK)
6:17 Creating the Certificate via T-SQL
10:34 Explaining what will be encrypted.
13:28 Backing up the certificate
you diserve more views
Very smooth and broad description! I liked the subtle sense of humor as well. :) Thank you.
A quick hassle: What happens if we want to apply TDE on a database in Azure SQL Server?! since there's no Master in the database in Azure! I stumbled in the first step.
With Azure it depends. If you implement an Azure SQL Database it automatically has TDE enabled by default so you do not have to do anything. Remember that an Azure SQL Database is an isolated database (similar to a contained database on an on-premise SQL Server Instance). Since Azure SQL databases do not use instances the encryption is done at the server level.
If you use an Azure SQL Server Managed Instance then TDE is at the instance level and all databases using the instance are automatically enabled with TDE.
If you use an Azure VM then just do it like I showed.
If you have older Azure SQL Databases already deployed then you would need to manually enable the TDE for the databases.
I have not used Azure Synapse yet but those must be enabled manually.
You can read more about it here. Just go to the azure.portal and enable TDE if you need it.
docs.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-tde-overview?tabs=azure-portal
That was a great video, thanks OdinTree. One question that was already asked below but I would like clarification on. Is backing up the masterkey not necessary? Since you created a new master key on SERVER2 with a new password is backing up that not required?
Hello Taylor,
Thanks for the question. Backing up any key is not technically required but should be done. Even the database master key should be backed up. Here is the reason directly from MS docs:
"The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location."
Good video.. Nicly explained.. just to ask one thing, will we get same certificate error if we copy the mdf and ldf to another server and attach the database? Assume the new server doesnt have any keys and certs
If you do not have the original certificate or keys then you cannot open the database on a new server. That is one of the main functions of TDE. So that if somebody obtains a physical copy of the database they cannot restore it on another server to look at the data.
A quick question if I may :-). In the end, you deleted the Master Key. Does this need to be deleted? I presume this key resides at the Instance level and if so, do you only need this to be created once and then it is used here on when creating the certificate? If you have multiple databases in the instance, I presume every database will have it's own certificate and it's own database encryption key? Excellent video and I was able to replicate the whole exercise without issues. Thank you
OK I will attempt to answer your questions:
Does the master key need to be deleted: No
Do you need a new certificate for each database: No - serves no purpose to create multiple certificates on the same server.
Each database has its own database key (use different passwords)
Hi, thanks for the demonstration. Can I know if the encrypted database can be restored to sql server express edition?
Hello Sen,
I do not think you could restore a TDE encrypted database to SQL Express since SQL Express does not support Encryption in anyway. I have never attempted it but you could and verify it for all.
Good evening. I was able to follow all your steps and everything worked but I have a few questions since I have several different databases. For the first step of creating the master key, is this done once at the server level or master database level only and can be used for several databases? Or, is this master key encryption step done for each database? Next, creating the database encryption key with the algorithm by server certificate, is that again done once and used for any database or is this this a database specific step and should be done for each database? Lastly, alternating a specific database and turning on encryption is obviously specific to each database so no response needed for this really. I hope you can provide me an answer as I did not know how to contact you directly.
Hello RenewalTracker,
Sorry for the late response as I did not see this message for some reason. For each server instance there is a single Service Master Key (this will automatically be created when SQL Server is setup), you will create the database master key and certificate once (you can use the same certificate for many user databases), and then create a database encryption key for each database.
@@odintree5704 Thank you very much. So, in your video as you refer to the Service Master Key, that is specific to the server and is a single value. When you refer to the database master key and certificate, that can be a single value used for all databases within SQL Server on that server. Finally, when you refer to a database encryption key, that is specific to a single database and for each database within SQL Server on the server you would have a unique value. Do I have it correct?
@@renewaltracker Sounds like you have it. See this link from Microsoft and scroll about 1/4 of the way down and you will see an image with the different keys and certificates. A picture is worth a 1000 words.
docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
@@renewaltracker @OdinTree, Thank you both for clarify this. It was explained clearly. However, one thing escaped me. @OdinTree did explained in his video how to backup the cert. and database encryption key and this works but I am not sure how to do it if you have more encrypted databases. If you use one certificate for more databases and each database has its own private key. Because backup query shows that we are doing this from master key. Backup query explain how to backup certificate with private key (single), but how backup query should look like if you have more databases encrypt with same certification?
Hello Kornelije,
Here is an explanation of the keys and certificates that I gave to another question. It will hopefully help explain what is happening even with multiple user databases and why we just need to backup the certificate and private key to restore any user database that has been encrypted. Hope this helps, if not please clarify your question.
The DEK (database encryption key) resides in the user database that you encrypted. This key is needed to decrypt and encrypt your database.
So if someone tried restoring your database they would also restore the DEK. You would now think they could decrypt your database.
But they cannot because the key is protected (encrypted) by the certificate which resides in the Master database. You therefore need to restore the certificate also.
The certificate is what is needed to decrypt the DEK. But you need the private key that is associated with the certificate. The private key is encrypted by the DMK (this is also in the Master database). In turn the DMK is protected by the Service Master Key (SMK) and just to confuse you more the SMK is protected by the operating system.
So it sounds like we need all the keys back to the operating system, but luckily for us that is not the case.
We can restore just by going as far back as the certificate and private key.
When you backed up the certificate you included the private key and a password. This password was used to encrypt the private key.
But you may recall I said that the private key is already encrypted with the DMK. So this looks like we just encrypt a key that is already encrypted. But the BACKUP CERTIFICATE command actually backups the unencrypted private key and therefore we supply a password to encrypt the unencrypted version of the key. Now I can retrieve the unencrypted key with the supplied password.
Now when I recreate the certificate to another server or instance and supply the password I restore an unencrypted private key. But immediately the DMK of the new instance will protect this key by encrypting it and storing it in the new Master database.
This was a great overview, really appreciate it. What is the purpose of the password on the master key (in your case 'Access999')? It was never used after the master key was created, nor was it backed up, used for the restore, or even used to remove encryption from the database.
This is a great question and I hope my explanation below does it justice. It may be long winded but I am trying to give a full explanation rather than not enough information.
The DEK resides in the user database that you encrypted. This key is needed to decrypt and encrypt your database.
So if someone tried restoring your database they would also restore the DEK and therefore you would think they could decrypt your database.
But they cannot because the key is protected (encrypted) by the certificate which resides in the Master database. You therefore need to restore
the certificate also.
The certificate is what is needed to decrypt the DEK. But you need the private key that is associated with the certificate.
The private key is encrypted by the DMK (this is also in the Master database). In turn the DMK is protected by the Service Master Key (SMK) and just to confuse you more the SMK is protected by the operating system.
So it sounds like we need all the keys back to the operating system, but luckily for us that is not the case.
We can restore just by going as far back as the certificate and private key.
When you backed up the certificate you included the private key and a password. This password was used to encrypt the private key.
But you may recall I said that the private key is already encrypted with the DMK. So this looks like we just encrypt a key that is already encrypted. But the BACKUP CERTIFICATE command actually backups the unencrypted private key and therefore we supply a password to encrypt the unencrypted version of the key. Now I can retrieve the unencrypted key with the supplied password.
Now when I recreate the certificate to another server or instance and supply the password I restore an unencrypted private key. But immediately the DMK of the new instance will protect this key by encrypting it and storing it in the new Master database.
@@odintree5704 Excellent explanation. It is quite tricky to understand TDE given that there are so many components that are overwhelming but your explanation was great. Thank you so much for sharing your knowledge.
Will this encription need an access key to read / update data from an application? Or this encryption is only for just in case someone gets a copy so they can't restore it???
TDE is transparent to the front end application. You do NOT need to make any changes what-so-ever to your front end application to interact with a TDE enabled database.
Hallo can you help me?? For the error
I will try. What is your error?
Implementing TDE wont affect the application ?
Sorry qqq - for some reason CZcams did not display your comment to me. You are correct that TDE does not affect the app. Keep in mind there is a small performance hit since data needs to be constantly encrypted and decrypted. You could get up to a 3% performance hit, but depending on what is in the cache this could be reduced.