{"id":12169,"date":"2024-04-24T15:45:08","date_gmt":"2024-04-24T14:45:08","guid":{"rendered":"https:\/\/sdna.lu\/?p=12169"},"modified":"2025-09-01T10:45:19","modified_gmt":"2025-09-01T09:45:19","slug":"enable-transparent-data-encryption-tde-on-sql-server-databases","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=12169","title":{"rendered":"Enable Transparent Data Encryption (TDE) on SQL Server databases"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Transparent Data Encryption (TDE) is a method used to encrypt data-at-rest within a database, ensuring that stored data remains <strong>encrypted on disk<\/strong> and is only decrypted when accessed by <strong>authorized users or applications<\/strong>. So you can safely enable this option on your DeMaSy &amp; Licence databases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In order to avoid any right or authorization issues we recommend to use the <strong>SA user<\/strong> to execute this tutorial.<\/p>\n\n\n\n<p class=\"has-medium-font-size wp-block-paragraph\">Enabling TDE <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To enable TDE for DeMaSy &amp; Licence databases on your SQL Server follow these instructions: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check the encrypted databases\n<ul class=\"wp-block-list\">\n<li>Select name, is_encrypted from sys.databases<\/li>\n\n\n\n<li>DeMaSy &amp; Licence are supposed to be not encrypted at this point<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Make the full database backup of DeMaSy &amp; Licence\n<ul class=\"wp-block-list\">\n<li>&#8212; DeMaSy<\/li>\n\n\n\n<li>BACKUP DATABASE [DeMaSy] TO DISK = N&#8217;D:\\SQLBackups\\DeMaSy.bak&#8217; WITH NOFORMAT, NOINIT,<\/li>\n\n\n\n<li>NAME = N&#8217;DeMaSy-Full Database Backup&#8217;, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/li>\n\n\n\n<li>GO<\/li>\n\n\n\n<li>&#8212; Licence<\/li>\n\n\n\n<li>BACKUP DATABASE [Licence] TO DISK = N&#8217;D:\\SQLBackups\\Licence.bak&#8217; WITH NOFORMAT, NOINIT,<\/li>\n\n\n\n<li>NAME = N&#8217;Licence-Full Database Backup&#8217;, SKIP, NOREWIND, NOUNLOAD, STATS = 10<\/li>\n\n\n\n<li>GO<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Create a database master key\n<ul class=\"wp-block-list\">\n<li>USE master;<\/li>\n\n\n\n<li>Go<\/li>\n\n\n\n<li>CREATE MASTER KEY<\/li>\n\n\n\n<li>ENCRYPTION BY PASSWORD = &#8216;YourEncryptionPassword&#8217;;<\/li>\n\n\n\n<li>GO<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Create the certificate\n<ul class=\"wp-block-list\">\n<li>USE master;<\/li>\n\n\n\n<li>GO<\/li>\n\n\n\n<li>CREATE CERTIFICATE TDE_Certificate<\/li>\n\n\n\n<li>WITH SUBJECT=&#8217;Certificate for TDE&#8217;;<\/li>\n\n\n\n<li>GO<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Create the database encryption key for DeMaSy &amp; Licence\n<ul class=\"wp-block-list\">\n<li>&#8212; DeMaSy<\/li>\n\n\n\n<li>USE DeMaSy<\/li>\n\n\n\n<li>GO<\/li>\n\n\n\n<li>CREATE DATABASE ENCRYPTION KEY<\/li>\n\n\n\n<li>WITH ALGORITHM = AES_256<\/li>\n\n\n\n<li>ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;<\/li>\n\n\n\n<li>&#8212; Licence<\/li>\n\n\n\n<li>USE Licence<\/li>\n\n\n\n<li>GO<\/li>\n\n\n\n<li>CREATE DATABASE ENCRYPTION KEY<\/li>\n\n\n\n<li>WITH ALGORITHM = AES_256<\/li>\n\n\n\n<li>ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Back up the certificate and the private key associated with the certificate\n<ul class=\"wp-block-list\">\n<li>USE master;<\/li>\n\n\n\n<li>GO<\/li>\n\n\n\n<li>BACKUP CERTIFICATE [TDE_Certificate]<\/li>\n\n\n\n<li>TO FILE = &#8216;D:\\CertificatesBackup\\TDE_Certificate_For_DeMaSyAndLicenceDatabases.cer&#8217;<\/li>\n\n\n\n<li>WITH PRIVATE KEY (file=&#8217;D:\\CertificatesBackup\\Keys\\TDE_DeMaSyAndLicence_private_CertKey.pvk&#8217;,<\/li>\n\n\n\n<li>ENCRYPTION BY PASSWORD=&#8217;YourEncryptionPassword&#8217;);<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Turn on encryption on databases\n<ul class=\"wp-block-list\">\n<li>&#8212; DeMaSy<\/li>\n\n\n\n<li>ALTER DATABASE DeMaSy<\/li>\n\n\n\n<li>SET ENCRYPTION ON<\/li>\n\n\n\n<li>&#8212; Licence<\/li>\n\n\n\n<li>ALTER DATABASE Licence<\/li>\n\n\n\n<li>SET ENCRYPTION ON<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Check if encryption is enabled\n<ul class=\"wp-block-list\">\n<li>Select name, is_encrypted from sys.databases\n<ul class=\"wp-block-list\">\n<li>Now you can see 1 instead 0 the DeMaSy &amp; Licence records<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Select * from sys.certificates<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size wp-block-paragraph\">Disable TDE<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To disable TDE on DeMaSy &amp; Licence databases follow these instructions: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Turn of encryption on databases\n<ul class=\"wp-block-list\">\n<li>&#8212; DeMaSy<\/li>\n\n\n\n<li>Use DeMaSy<\/li>\n\n\n\n<li>Go<\/li>\n\n\n\n<li>Alter Database DeMaSy Set Encryption off<\/li>\n\n\n\n<li>&#8212; Licence<\/li>\n\n\n\n<li>Use Licence<\/li>\n\n\n\n<li>Go<\/li>\n\n\n\n<li>Alter Database Licence Set Encryption off<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Remove the encryption key on databases\n<ul class=\"wp-block-list\">\n<li>&#8212; DeMaSy<\/li>\n\n\n\n<li>Use DeMaSy<\/li>\n\n\n\n<li>go<\/li>\n\n\n\n<li>Drop database encryption key<\/li>\n\n\n\n<li>&#8212; Licence<\/li>\n\n\n\n<li>Use Licence<\/li>\n\n\n\n<li>go<\/li>\n\n\n\n<li>Drop database encryption key<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Remove the certificate\n<ul class=\"wp-block-list\">\n<li>Use Master<\/li>\n\n\n\n<li>Go<\/li>\n\n\n\n<li>Drop certificate TDE_Certificate<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Optional, remove the master key\n<ul class=\"wp-block-list\">\n<li>Use master<\/li>\n\n\n\n<li>Go<\/li>\n\n\n\n<li>Drop master key<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Transparent Data Encryption (TDE) is a method used to encrypt data-at-rest within a database, ensuring that stored data remains encrypted on disk and is only decrypted when accessed by authorized users or applications. So you can safely enable this option on your DeMaSy &amp; Licence databases. In order to avoid any right or authorization issues [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-12169","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/12169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12169"}],"version-history":[{"count":2,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/12169\/revisions"}],"predecessor-version":[{"id":12709,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/12169\/revisions\/12709"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}