Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 7.1 >
edited by Nikhil Singh
on 2025/02/28 11:30
To version < 3.1 >
edited by Rudi Marais
on 2025/02/26 13:07
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.nikhils
1 +XWiki.rudim
Content
... ... @@ -1,327 +1,12 @@
1 -= TDE(backup from smss to azure) =
1 += Fancy =
2 2  
3 3  
4 -1 Create master key in master database (set master key)
4 +sdfswedfsdf
5 5  
6 -2 Create TDE certificate (encrypted by MK)
6 +{{mention reference="XWiki.nikhils" style="FULL_NAME" anchor="XWiki-nikhils-rfwngb"/}}
7 7  
8 -3 Backup the Certificate and private key, By encryption with a password ( did not do it in this case due to storage blog problems)
9 9  
10 -4 Choose DB to create the DEK ,Create database encryption key (DEK) with algorithm ( AES= 256) and encryption by certificate
11 -
12 -5 Set encryption on for the database
13 -
14 --- 6 Create a new credential with the SAS token
15 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
16 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
17 -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';
18 -Go--
19 -
20 ---7 --
21 -
22 22  {{code language="sql"}}
23 -Perform the database backup with the provided parameters
24 -EXECUTE dba.dbo.DatabaseBackup
25 -    @Databases = 'dba',                           Replace with your database name
26 -    @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',   Azure Blob Storage URL
27 -    @BackupType = 'Full',                         Full backup
28 -    @CopyOnly = 'Y',                              Copy-only backup to avoid breaking backup chain
29 -    @Compress = 'Y',                              Compress the backup
30 -    @Verify = 'N';                                No verification of backup
31 -GO
32 -{{/code}}
33 -
34 -The backup was unable to be done from smss to azure ( the MI does not support encrypted DBs to be backed up from smss to azure)
35 -
36 -We have unencrypted a database and backed it up from ssms to azure blob successfully
37 -
38 -
39 -Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
40 -
41 -
42 -
43 -
44 -
45 -= **Using TDE directly from azure portal** =
46 -
47 -
48 -===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
49 -
50 -
51 -- Go to keys and click generate
52 -
53 -- Name your key (mysqlmikey)
54 -
55 -- Choose a key type (RSA)
56 -
57 --Choose RSA key size (2048-bit)
58 -
59 -Note:
60 -
61 -* switching from 2048-bit RSA to 4096-bit RSA for TDE will affect performance, but the actual impact might be minor, especially on modern hardware and typical workloads.
62 -* **It will likely affect CPU usage** more than disk I/O, and the impact might be more noticeable during key management operations (key generation, encryption, etc.).
63 -* If your database is not under heavy load and your hardware can handle the extra processing, the trade-off for better security may be worth it.
64 -
65 -- Click create
66 -
67 -
68 -[[image:image-20250228120622-1.png||height="236" width="542"]]
69 -
70 -
71 -===== 2 Enable TDE =====
72 -
73 -
74 -- Go to your Managed instance ( sqlmi-ebs-lab)
75 -
76 -- Click on security and choose Transparent data encryption
77 -
78 -- Select the type of managed key ( Customer-managed key0
79 -
80 -- Select the key from the key vault we generated in the key vault( mysqlmikey)
81 -
82 -- Make the key the default TDE protector
83 -
84 --Click save
85 -
86 -
87 -[[image:image-20250228122106-2.png||height="251" width="511"]]
88 -
89 -
90 -- TDE has now been enabled on the Managed instance
91 -
92 -
93 -Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
94 -
95 -We are now able to backup databases from SSMS to Azure storage.
96 -
97 -
98 -
99 -
100 -
101 -
102 -
103 -
104 -
105 -
106 -
107 -
108 -
109 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
110 -GO
111 -
112 -CREATE CERTIFICATE TDE_Certificate
113 -WITH SUBJECT = 'TDE Certificate';
114 -GO
115 -
116 -2 USE master;
117 -GO
118 -
119 -SELECT
120 - cert.name AS Certificate_Name,
121 - cert.subject AS Certificate_Subject,
122 - cert.issuer_name AS Issuer_Name,
123 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
124 -FROM
125 - sys.certificates cert
126 -WHERE
127 - cert.name LIKE 'TDE%';
128 -
129 -
130 -3 BACKUP CERTIFICATE TDE_Certificate
131 -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'
132 -WITH PRIVATE KEY (
133 - 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',
134 - ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
135 -);
136 -GO
137 -
138 -
139 -USE Normal;
140 -GO
141 -
142 --- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
143 -CREATE DATABASE ENCRYPTION KEY
144 -WITH ALGORITHM = AES_256
145 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
146 -GO--
147 -
148 -USE [dba]
149 -GO
150 -
151 -CREATE DATABASE ENCRYPTION KEY
152 -WITH ALGORITHM = AES_256
153 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
154 -GO
155 -
156 -
157 -USE [dba1]
158 -GO
159 -
160 -CREATE DATABASE ENCRYPTION KEY
161 -WITH ALGORITHM = AES_256
162 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
163 -GO
164 -
165 -
166 -USE [dba2]
167 -GO
168 -
169 -CREATE DATABASE ENCRYPTION KEY
170 -WITH ALGORITHM = AES_256
171 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
172 -GO
173 -
174 -
175 -USE [dba3]
176 -GO
177 -
178 -CREATE DATABASE ENCRYPTION KEY
179 -WITH ALGORITHM = AES_256
180 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
181 -GO
182 -
183 -USE [xwiki]
184 -GO
185 -
186 -CREATE DATABASE ENCRYPTION KEY
187 -WITH ALGORITHM = AES_256
188 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
189 -GO
190 -
191 -
192 --- 7. Enable Transparent Data Encryption (TDE) on the database
193 -ALTER DATABASE dba
194 -SET ENCRYPTION ON;
195 -GO--
196 -
197 -
198 -select name, database_id, state_desc
199 -from sys.databases
200 -
201 -
202 -SELECT
203 - database_id,
204 - key_algorithm,
205 - key_length,
206 - encryption_state_desc
207 - encryptor_type
208 -
209 -FROM
210 - sys.dm_database_encryption_keys;
211 -
212 -
213 - Select * from sys.dm_database_encryption_keys
214 -
215 -
216 - BACKUP DATABASE [dba2]
217 -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'>>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']]
218 -With copy_only
219 -GO
220 -
221 -
222 -BACKUP DATABASE [dba2]
223 -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'>>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']],
224 -\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
225 - COMPRESSION,  -- Optional: Compresses the backup to save storage space
226 - STATS = 10 -- Optional: Provides backup progress status
227 -GO--
228 -
229 -
230 -
231 --- Step 1: Drop the existing credential (if needed)
232 -DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
233 -GO--
234 -
235 --- Step 2: Create a new credential with the SAS token
236 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
237 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
238 -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';
239 -GO--
240 -
241 --- Step 3: Perform the database backup with the provided parameters
242 -EXECUTE dba.dbo.DatabaseBackup
243 - @Databases = 'dba',                          -- Replace with your database name
244 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
245 - @BackupType = 'Full',                        -- Full backup
246 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
247 - @Compress = 'Y',                             -- Compress the backup
248 - @Verify = 'N'; -- No verification of backup
249 -GO--
250 -
251 -
252 -
253 -
254 -
255 -select name, database_id, state_desc
256 -from sys.databases
257 -
258 -
259 -SELECT
260 - database_id,
261 - key_algorithm,
262 - key_length,
263 - encryption_state_desc
264 - encryptor_type
265 -
266 -FROM
267 - select * from sys.dm_database_encryption_keys;
268 -
269 -
270 - select name, is_encrypted from sys.databases
271 -
272 -
273 - SELECT
274 - cert.name AS Certificate_Name,
275 - cert.subject AS Certificate_Subject,
276 - cert.issuer_name AS Issuer_Name,
277 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
278 -FROM
279 - sys.certificates cert
280 -WHERE
281 - cert.name LIKE 'TDE%';
282 -
283 - ALTER DATABASE dba1
284 -SET ENCRYPTION off;
285 -GO
286 -
287 -
288 -Use dba1;
289 -DROP DATABASE ENCRYPTION KEY;
290 -
291 -
292 -USE [dba1]
293 -GO
294 -
295 -CREATE DATABASE ENCRYPTION KEY
296 -WITH ALGORITHM = AES_256
297 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
298 -GO
299 -
300 -
301 -
302 -ALTER DATABASE dba1
303 -SET ENCRYPTION ON
304 -
305 -
306 -
307 -
308 -
309 -
310 -
311 -
312 -
313 -
314 -
315 -
316 -
317 -
318 -
319 -
320 -
321 -
322 -
323 -
324 -{{code language="sql"}}
325 325  USE master;
326 326  GO
327 327  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
... ... @@ -354,53 +354,3 @@
354 354  SET ENCRYPTION ON;
355 355  GO
356 356  {{/code}}
357 -
358 -
359 -
360 -
361 -
362 -
363 -
364 -
365 -
366 -
367 -
368 -
369 -
370 -{{code language="sql"}}
371 -USE master;
372 -GO
373 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
374 -GO
375 -
376 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
377 -GO
378 -
379 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
380 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
381 -ENCRYPTION BY PASSWORD='*****')
382 -
383 -USE Everest_TDE_Master;
384 -GO
385 -CREATE DATABASE ENCRYPTION KEY
386 -WITH ALGORITHM = AES_256
387 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
388 -GO
389 -ALTER DATABASE Everest_TDE_Master
390 -SET ENCRYPTION ON;
391 -GO
392 -
393 -USE Everest_TDE_Master_Documents;
394 -GO
395 -CREATE DATABASE ENCRYPTION KEY
396 -WITH ALGORITHM = AES_256
397 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
398 -GO
399 -ALTER DATABASE Everest_TDE_Master_Documents
400 -SET ENCRYPTION ON;
401 -GO
402 -{{/code}}
403 -
404 -
405 -
406 -
XWiki.XWikiComments[0]
Author
... ... @@ -1,1 +1,0 @@
1 -XWiki.rudim
Comment
... ... @@ -1,1 +1,0 @@
1 -put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,1 +1,0 @@
1 -2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: