{"id":1179,"date":"2025-04-22T19:18:57","date_gmt":"2025-04-22T19:18:57","guid":{"rendered":"https:\/\/humtechno.com\/master\/?p=1179"},"modified":"2025-04-22T19:42:05","modified_gmt":"2025-04-22T19:42:05","slug":"enable-encryption-on-sql-server-database-with-always-on","status":"publish","type":"post","link":"https:\/\/humtechno.com\/master\/2025\/04\/22\/enable-encryption-on-sql-server-database-with-always-on\/","title":{"rendered":"Enable Encryption on SQL Server Database with Always on"},"content":{"rendered":"\n\n\n\n\n<p><a href=\"https:\/\/humtechno.com\/master\/wp-content\/uploads\/2025\/04\/Configure SQL Server TDE with Alwayson.pdf\">Download Complete Steps with Screenshots<\/a><\/p>\n\n\n\n<p>Database file encryption occurs at the page level. In an encrypted database, pages are encrypted before being written to disk and decrypted when loaded into memory. Transparent Data Encryption (TDE) does not increase the size of the database.<\/p>\n\n\n\n<p>Transparent Data Encryption (TDE) safeguards data at rest, including both data and log files. It helps organizations comply with various industry laws, regulations, and standards. TDE enables software developers to encrypt data using AES or 3DES encryption algorithms without the need to modify existing applications.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-provider-youtube wp-block-embed-youtube\"><div class=\"wp-block-embed__wrapper\">\n<div class=\"nv-iframe-embed\"><iframe loading=\"lazy\" title=\"How to Configure TDE Encryption on SQL Server with and without Always on\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/Ri2fuQP8-yk?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/div>\n<\/div><\/figure>\n\n\n\n<p><strong><u>How to encrypt (TDE) in SQL Server<\/u><\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Create Master Key in Database Server (Primary Node)<\/li>\n\n\n\n<li>Create Certificate&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Primary Node)<\/li>\n\n\n\n<li>Backup Certificate&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Primary Node)<\/li>\n\n\n\n<li>Copy Certificate Backup File to Other Secondary Server of Always &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (if you are using always on).<\/li>\n\n\n\n<li>Create Master Key in Database Server (Secondary Node)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (if you are using always on).<\/li>\n\n\n\n<li>Restore Certificate on Secondary.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (if you are using always on).<\/li>\n\n\n\n<li>Create Database Encryption Key on Database &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Primary Node)<\/li>\n\n\n\n<li>Enable Database Encryption Database &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (Primary Node)<\/li>\n<\/ol>\n\n\n\n<p><strong><u>Types of Database Encryption<\/u><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database Level &#8211; Encrypts data at rest (data and log files).<\/li>\n\n\n\n<li>Backup Level<ul><li>Database Backup Level<\/li><\/ul>\n<ul class=\"wp-block-list\">\n<li>Transaction Log Backup Level<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p><strong><u>Scripts for Encryptions<\/u><\/strong><\/p>\n\n\n\n<p><strong>Execute on Database Primary Node<\/strong><\/p>\n\n\n\n<p>select * from sys.symmetric_keys;<\/p>\n\n\n\n<p>USE Master;<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>CREATE MASTER KEY ENCRYPTION<\/p>\n\n\n\n<p>BY PASSWORD=&#8217;fdr3#edgSar345#drDS&#8217;;<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>CREATE CERTIFICATE TDE_Cert<\/p>\n\n\n\n<p>WITH<\/p>\n\n\n\n<p>SUBJECT=&#8217;Database_Encryption&#8217;;<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date<\/p>\n\n\n\n<p>FROM sys.certificates;<\/p>\n\n\n\n<p>mkdir -p C:\\Database\\Backup\\TDE<\/p>\n\n\n\n<p>BACKUP CERTIFICATE TDE_Cert TO FILE = &#8216;C:\\Database\\Backup\\TDE\\TDE_Cert&#8217; WITH PRIVATE KEY (file=&#8217;C:\\Database\\Backup\\TDE\\TDE_Cert_Key.pvk&#8217;, ENCRYPTION BY<\/p>\n\n\n\n<p>PASSWORD=&#8217;fdr3#edgSar345#drDS&#8217;)<\/p>\n\n\n\n<p>Copy Backup of TDE File on same Directory<\/p>\n\n\n\n<p><strong>Execute on Database Secondary Nodes<\/strong><\/p>\n\n\n\n<p>select * from sys.symmetric_keys;<\/p>\n\n\n\n<p>USE Master;<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>CREATE MASTER KEY ENCRYPTION<\/p>\n\n\n\n<p>BY PASSWORD=&#8217;fdr3#edgSar345#drDS&#8217;;<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>CREATE CERTIFICATE TDE_Cert<\/p>\n\n\n\n<p>FROM FILE = &#8216;C:\\Database\\Backup\\TDE\\TDE_Cert&#8217;<\/p>\n\n\n\n<p>WITH PRIVATE KEY (FILE = &#8216;C:\\Database\\Backup\\TDE\\TDE_Cert_Key.pvk&#8217;,<\/p>\n\n\n\n<p>DECRYPTION BY PASSWORD = &#8216;fdr3#edgSar345#drDS&#8217;);<\/p>\n\n\n\n<p>select * from sys.symmetric_keys;<\/p>\n\n\n\n<p>SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date<\/p>\n\n\n\n<p>FROM sys.certificates;<\/p>\n\n\n\n<p><strong><u>Execute on Database Primary Nodes<\/u><\/strong><\/p>\n\n\n\n<p>USE DB1<\/p>\n\n\n\n<p>GO<\/p>\n\n\n\n<p>CREATE DATABASE ENCRYPTION KEY<\/p>\n\n\n\n<p>WITH ALGORITHM = AES_256<\/p>\n\n\n\n<p>ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;<\/p>\n\n\n\n<p><strong><u>Execute and Verify on All Database Nodes<\/u><\/strong><\/p>\n\n\n\n<p>select db.name<\/p>\n\n\n\n<p>&nbsp;&nbsp; , db.is_encrypted<\/p>\n\n\n\n<p>&nbsp;&nbsp; , dm.encryption_state<\/p>\n\n\n\n<p>&nbsp;&nbsp; , dm.percent_complete<\/p>\n\n\n\n<p>&nbsp;&nbsp; , dm.key_algorithm<\/p>\n\n\n\n<p>&nbsp;&nbsp; , dm.key_length<\/p>\n\n\n\n<p>from sys.databases db<\/p>\n\n\n\n<p>left outer join sys.dm_database_encryption_keys dm<\/p>\n\n\n\n<p>&nbsp;&nbsp; on db.database_id = dm.database_id;<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_siteseo_robots_primary_cat":"17","pagelayer_contact_templates":[],"_pagelayer_content":"","neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[17],"tags":[26,29,28,27],"class_list":["post-1179","post","type-post","status-publish","format-standard","hentry","category-mssql","tag-sql-server","tag-sql-server-encryption","tag-sql-server-tde","tag-tde"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/comments?post=1179"}],"version-history":[{"count":4,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"predecessor-version":[{"id":1194,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/posts\/1179\/revisions\/1194"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/master\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}