Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
From version < 3.2 >
edited by Nikhil Singh
on 2025/02/26 13:24
To version 2.1 >
edited by Rudi Marais
on 2023/01/25 12:52
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -technical-documentation.analysis.sql.WebHome
1 +Technical Documentation.analysis.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nikhils
1 +XWiki.rudim
Content
... ... @@ -1,237 +235,3 @@
1 -= TDE(1) =
2 -
3 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
4 -GO
5 -
6 -CREATE CERTIFICATE TDE_Certificate
7 -WITH SUBJECT = 'TDE Certificate';
8 -GO
9 -
10 -2 USE master;
11 -GO
12 -
13 -SELECT
14 - cert.name AS Certificate_Name,
15 - cert.subject AS Certificate_Subject,
16 - cert.issuer_name AS Issuer_Name,
17 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
18 -FROM
19 - sys.certificates cert
20 -WHERE
21 - cert.name LIKE 'TDE%';
22 -
23 -
24 -3 BACKUP CERTIFICATE TDE_Certificate
25 -TO FILE = https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D\TDE_Certificate.cer'
26 -WITH PRIVATE KEY (
27 - FILE = https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D\TDE_PrivateKey.pvk',
28 - ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
29 -);
30 -GO
31 -
32 -
33 -USE Normal;
34 -GO
35 -
36 --- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
37 -CREATE DATABASE ENCRYPTION KEY
38 -WITH ALGORITHM = AES_256
39 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
40 -GO
41 -
42 -USE [dba]
43 -GO
44 -
45 -CREATE DATABASE ENCRYPTION KEY
46 -WITH ALGORITHM = AES_256
47 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
48 -GO
49 -
50 -
51 -USE [dba1]
52 -GO
53 -
54 -CREATE DATABASE ENCRYPTION KEY
55 -WITH ALGORITHM = AES_256
56 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
57 -GO
58 -
59 -
60 -USE [dba2]
61 -GO
62 -
63 -CREATE DATABASE ENCRYPTION KEY
64 -WITH ALGORITHM = AES_256
65 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
66 -GO
67 -
68 -
69 -USE [dba3]
70 -GO
71 -
72 -CREATE DATABASE ENCRYPTION KEY
73 -WITH ALGORITHM = AES_256
74 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
75 -GO
76 -
77 -USE [xwiki]
78 -GO
79 -
80 -CREATE DATABASE ENCRYPTION KEY
81 -WITH ALGORITHM = AES_256
82 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
83 -GO
84 -
85 -
86 --- 7. Enable Transparent Data Encryption (TDE) on the database
87 -ALTER DATABASE dba
88 -SET ENCRYPTION ON;
89 -GO
90 -
91 -
92 -select name, database_id, state_desc
93 -from sys.databases
94 -
95 -
96 -SELECT
97 - database_id,
98 - key_algorithm,
99 - key_length,
100 - encryption_state_desc
101 - encryptor_type
102 -
103 -FROM
104 - sys.dm_database_encryption_keys;
105 -
106 -
107 - Select * from sys.dm_database_encryption_keys
108 -
109 -
110 - BACKUP DATABASE [dba2]
111 -TO URL = 'https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D'
112 -With copy_only
113 -GO
114 -
115 -
116 -BACKUP DATABASE [dba2]
117 -TO URL = 'https://zagpebslab.blob.core.windows.net/sql-backups?sp=racwl&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=eQKxB%2F0bg5cX71PmhUTlGHLCnIwSbe5CLOWVVkaDR1g%3D',
118 -
119 - COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
120 - COMPRESSION, -- Optional: Compresses the backup to save storage space
121 - STATS = 10 -- Optional: Provides backup progress status
122 -GO
123 -
124 -
125 -
126 --- Step 1: Drop the existing credential (if needed)
127 -DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
128 -GO
129 -
130 --- Step 2: Create a new credential with the SAS token
131 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
132 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
133 -SECRET = 'sp=racwdli&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=kBFwlj5S5eqBEE32LM1EFebBY0W94uEFiwOXo3R0yt4%3D';
134 -GO
135 -
136 --- Step 3: Perform the database backup with the provided parameters
137 -EXECUTE dba.dbo.DatabaseBackup
138 - @Databases = 'dba', -- Replace with your database name
139 - @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups', -- Azure Blob Storage URL
140 - @BackupType = 'Full', -- Full backup
141 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 - @Compress = 'Y', -- Compress the backup
143 - @Verify = 'N'; -- No verification of backup
144 -GO
145 -
146 -
147 -
148 -
149 -
150 -select name, database_id, state_desc
151 -from sys.databases
152 -
153 -
154 -SELECT
155 - database_id,
156 - key_algorithm,
157 - key_length,
158 - encryption_state_desc
159 - encryptor_type
160 -
161 -FROM
162 - select * from sys.dm_database_encryption_keys;
163 -
164 -
165 - select name, is_encrypted from sys.databases
166 -
167 -
168 - SELECT
169 - cert.name AS Certificate_Name,
170 - cert.subject AS Certificate_Subject,
171 - cert.issuer_name AS Issuer_Name,
172 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
173 -FROM
174 - sys.certificates cert
175 -WHERE
176 - cert.name LIKE 'TDE%';
177 -
178 - ALTER DATABASE dba1
179 -SET ENCRYPTION off;
180 -GO
181 -
182 -
183 -Use dba1;
184 -DROP DATABASE ENCRYPTION KEY;
185 -
186 -
187 -USE [dba1]
188 -GO
189 -
190 -CREATE DATABASE ENCRYPTION KEY
191 -WITH ALGORITHM = AES_256
192 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
193 -GO
194 -
195 -
196 -
197 -ALTER DATABASE dba1
198 -SET ENCRYPTION ON
199 -
200 -
201 -
202 -
203 -
204 -
205 -
206 -
207 -
208 -
209 -
210 -
211 -
212 -
213 -
214 -
215 -
216 -
217 -
218 -
219 -
220 -
221 -
222 -
223 -
224 -
225 -
226 -
227 -
228 -
229 -
230 -
231 -
232 -
233 -
234 -
235 235  {{code language="sql"}}
236 236  USE master;
237 237  GO

Applications

Navigation

Need help?

If you need help with XWiki you can contact: