Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 2.4 >
edited by Rudi Marais
on 2023/05/22 23:50
To version < 8.1 >
edited by Nikhil Singh
on 2025/02/28 12:42
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,4 +1,540 @@
1 += TDE(backup from smss to azure) =
2 +
3 +
4 +1 Create master key in master database (set master key)
5 +
6 +2 Create TDE certificate (encrypted by MK)
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 +
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 +
15 +6 Create credential with SAS token
16 +
1 1  {{code language="sql"}}
18 +drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
19 +CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
20 +WITH IDENTITY='SHARED ACCESS SIGNATURE'
21 +, 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'
22 +GO
23 +
24 +{{/code}}
25 +
26 +
27 +7
28 +
29 +{{code language="sql"}}
30 +EXECUTE dba.dbo.DatabaseBackup
31 + @Databases = @DatabaseName,
32 + @URL = @BackupContainerURL,
33 + @BackupType = 'Full',
34 + @CopyOnly = 'Y',
35 + @Compress = 'Y',
36 + @Verify = 'N';
37 +{{/code}}
38 +
39 +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)
40 +
41 +We have unencrypted a database and backed it up from ssms to azure blob successfully
42 +
43 +
44 +Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
45 +
46 +
47 +
48 +
49 +
50 += **Using TDE directly from azure portal** =
51 +
52 +
53 +===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
54 +
55 +
56 +- Go to keys and click generate
57 +
58 +- Name your key (mysqlmikey)
59 +
60 +- Choose a key type (RSA)
61 +
62 +-Choose RSA key size (2048-bit)
63 +
64 +Note:
65 +
66 +* 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.
67 +* **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.).
68 +* 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.
69 +
70 +- Click create
71 +
72 +
73 +[[image:image-20250228120622-1.png||height="236" width="542"]]
74 +
75 +
76 +===== 2 Enable TDE =====
77 +
78 +
79 +- Go to your Managed instance ( sqlmi-ebs-lab)
80 +
81 +- Click on security and choose Transparent data encryption
82 +
83 +- Select the type of managed key ( Customer-managed key0
84 +
85 +- Select the key from the key vault we generated in the key vault( mysqlmikey)
86 +
87 +- Make the key the default TDE protector
88 +
89 +-Click save
90 +
91 +
92 +[[image:image-20250228122106-2.png||height="19" width="241"]]
93 +
94 +
95 +- TDE has now been enabled on the Managed instance
96 +
97 +
98 +Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
99 +
100 +We are now able to backup databases from SSMS to Azure storage.
101 +
102 +
103 +
104 +=== Backup specific databases using SQL server agent jobs ===
105 +
106 +
107 +we are creating a job to automatically backup only the DBA databases in the instance via the SQL server agent.
108 +
109 +The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.
110 +
111 +
112 +1 Create the script
113 +
114 +
115 +=== **1. Declaring Variables** ===
116 +
117 +{{code language="sql"}}
118 +DECLARE @DatabaseName NVARCHAR(128)
119 +DECLARE @BackupContainerURL NVARCHAR(512)
120 +{{/code}}
121 +
122 +DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
123 +
124 +* **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
125 +* **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
126 +
127 +----
128 +
129 +=== **2. Setting the Azure Blob Storage URL** ===
130 +
131 +{{code language="sql"}}
132 +SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
133 +{{/code}}
134 +
135 +
136 +----
137 +
138 +=== **3. Declaring the Cursor** ===
139 +
140 +
141 +{{code language="sql"}}
142 +DECLARE db_cursor CURSOR FOR
143 +SELECT name
144 +FROM sys.databases
145 +WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
146 +{{/code}}
147 +
148 +* **Cursor Declaration**:
149 +** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
150 +** The **WHERE name LIKE 'dba%'** clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
151 +** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
152 +
153 +----
154 +
155 +=== **4. Opening the Cursor** ===
156 +
157 +{{code language="sql"}}
158 +OPEN db_cursor
159 +FETCH NEXT FROM db_cursor INTO @DatabaseName
160 +{{/code}}
161 +
162 +OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
163 +
164 +* **OPEN db_cursor**: This opens the cursor for reading the results.
165 +* **FETCH NEXT**: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.
166 +
167 +----
168 +
169 +=== **5. Looping Through Databases** ===
170 +
171 +{{code language="sql"}}
172 +-- Loop through each database and execute the stored procedure
173 +WHILE @@FETCH_STATUS = 0
174 +BEGIN
175 + -- Print current database for logging/debugging
176 + PRINT 'Backing up database: ' + @DatabaseName
177 +
178 + -- Execute the DatabaseBackup stored procedure for each database
179 + EXECUTE dba.dbo.DatabaseBackup
180 + @Databases = @DatabaseName, -- Specify the current database
181 + @URL = @BackupContainerURL, -- Azure Blob Storage URL
182 + @BackupType = 'Full', -- Full backup type
183 + @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
184 + @Compress = 'Y', -- Enable compression
185 + @Verify = 'N'; -- Skip verification after backup
186 +
187 +{{/code}}
188 +
189 +
190 +* **WHILE @@FETCH_STATUS = 0**: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
191 +* **PRINT**: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.
192 +* (((
193 +**EXECUTE dba.dbo.DatabaseBackup**:
194 +
195 +* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
196 +* The parameters passed to the stored procedure:
197 +** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
198 +** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
199 +** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
200 +** **@CopyOnly = 'Y'**: Specifies that this is a **copy-only backup**. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
201 +** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
202 +** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
203 +)))
204 +
205 +----
206 +
207 +=== **6. Fetch the Next Database** ===
208 +
209 +{{code language="sql"}}
210 + -- Fetch the next database in the cursor
211 + FETCH NEXT FROM db_cursor INTO @DatabaseName
212 +
213 +{{/code}}
214 +
215 +~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
216 +
217 +* **FETCH NEXT**: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.
218 +
219 +----
220 +
221 +=== **7. Closing and Deallocating the Cursor** ===
222 +
223 +{{code language="sql"}}
224 +-- Close and deallocate the cursor to clean up resources
225 +CLOSE db_cursor
226 +DEALLOCATE db_cursor
227 +
228 +
229 +{{/code}}
230 +
231 +* **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
232 +* **DEALLOCATE db_cursor**: This deallocates the cursor, freeing up any resources used by the cursor. It’s a good practice to always deallocate cursors to avoid resource leaks.
233 +
234 +
235 +===== 8. Full Script =====
236 +
237 +
238 +{{code language="sql"}}
239 +DECLARE @DatabaseName NVARCHAR(128)
240 +DECLARE @BackupContainerURL NVARCHAR(512)
241 +
242 +SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
243 +
244 +DECLARE db_cursor CURSOR FOR
245 +SELECT name
246 +FROM sys.databases
247 +WHERE name LIKE 'dba%'
248 +
249 +OPEN db_cursor
250 +FETCH NEXT FROM db_cursor INTO @DatabaseName
251 +
252 +WHILE @@FETCH_STATUS = 0
253 +BEGIN
254 +
255 +PRINT 'Backing up database: ' + @DatabaseName
256 +
257 +EXECUTE dba.dbo.DatabaseBackup
258 + @Databases = @DatabaseName,
259 + @URL = @BackupContainerURL,
260 + @BackupType = 'Full',
261 + @CopyOnly = 'Y',
262 + @Compress = 'Y',
263 + @Verify = 'N';
264 +
265 +FETCH NEXT FROM db_cursor INTO @DatabaseName
266 +END
267 +
268 +CLOSE db_cursor
269 +DEALLOCATE db_cursor
270 +
271 +{{/code}}
272 +
273 +
274 +=== 2. Set up a new job ===
275 +
276 +
277 +- Go to SSMS and click on SQL Server Agent
278 +
279 + Drop down menu and left click jobs and select new job
280 +
281 +
282 +====== General: ======
283 +
284 +- Enter Job name (DBA databases backup)
285 +
286 +-  Owner (ebssqladmin)
287 +
288 +-  Category (Database maintenance)
289 +
290 + - Description (Description of the job)
291 +
292 +
293 +====== Steps: Create the steps for the job to follow ======
294 +
295 + - Step name (Backup only DBA database)
296 +
297 + - Type (Transact-SQL script)
298 +
299 + - Database (master)
300 +
301 + - Command (paste the script we created)
302 +
303 +
304 + Schedule: Create a schedule for the job to run
305 +
306 + - Name (DBA database backup)
307 +
308 + - Schedule Type (recurring)
309 +
310 + - Frequency (Occurs: Daily)
311 +
312 + (Recurs every: 1 day(s))
313 +
314 +
315 +
316 +
317 +
318 +
319 +
320 +
321 +
322 +1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
323 +GO
324 +
325 +CREATE CERTIFICATE TDE_Certificate
326 +WITH SUBJECT = 'TDE Certificate';
327 +GO
328 +
329 +2 USE master;
330 +GO
331 +
332 +SELECT
333 + cert.name AS Certificate_Name,
334 + cert.subject AS Certificate_Subject,
335 + cert.issuer_name AS Issuer_Name,
336 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
337 +FROM
338 + sys.certificates cert
339 +WHERE
340 + cert.name LIKE 'TDE%';
341 +
342 +
343 +3 BACKUP CERTIFICATE TDE_Certificate
344 +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'
345 +WITH PRIVATE KEY (
346 + 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',
347 + ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
348 +);
349 +GO
350 +
351 +
352 +USE Normal;
353 +GO
354 +
355 +-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
356 +CREATE DATABASE ENCRYPTION KEY
357 +WITH ALGORITHM = AES_256
358 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
359 +GO--
360 +
361 +USE [dba]
362 +GO
363 +
364 +CREATE DATABASE ENCRYPTION KEY
365 +WITH ALGORITHM = AES_256
366 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
367 +GO
368 +
369 +
370 +USE [dba1]
371 +GO
372 +
373 +CREATE DATABASE ENCRYPTION KEY
374 +WITH ALGORITHM = AES_256
375 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
376 +GO
377 +
378 +
379 +USE [dba2]
380 +GO
381 +
382 +CREATE DATABASE ENCRYPTION KEY
383 +WITH ALGORITHM = AES_256
384 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
385 +GO
386 +
387 +
388 +USE [dba3]
389 +GO
390 +
391 +CREATE DATABASE ENCRYPTION KEY
392 +WITH ALGORITHM = AES_256
393 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
394 +GO
395 +
396 +USE [xwiki]
397 +GO
398 +
399 +CREATE DATABASE ENCRYPTION KEY
400 +WITH ALGORITHM = AES_256
401 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
402 +GO
403 +
404 +
405 +-- 7. Enable Transparent Data Encryption (TDE) on the database
406 +ALTER DATABASE dba
407 +SET ENCRYPTION ON;
408 +GO--
409 +
410 +
411 +select name, database_id, state_desc
412 +from sys.databases
413 +
414 +
415 +SELECT
416 + database_id,
417 + key_algorithm,
418 + key_length,
419 + encryption_state_desc
420 + encryptor_type
421 +
422 +FROM
423 + sys.dm_database_encryption_keys;
424 +
425 +
426 + Select * from sys.dm_database_encryption_keys
427 +
428 +
429 + BACKUP DATABASE [dba2]
430 +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']]
431 +With copy_only
432 +GO
433 +
434 +
435 +BACKUP DATABASE [dba2]
436 +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']],
437 +\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
438 + COMPRESSION,  -- Optional: Compresses the backup to save storage space
439 + STATS = 10 -- Optional: Provides backup progress status
440 +GO--
441 +
442 +
443 +
444 +-- Step 1: Drop the existing credential (if needed)
445 +DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
446 +GO--
447 +
448 +-- Step 2: Create a new credential with the SAS token
449 +CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
450 +WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
451 +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';
452 +GO--
453 +
454 +-- Step 3: Perform the database backup with the provided parameters
455 +EXECUTE dba.dbo.DatabaseBackup
456 + @Databases = 'dba',                          -- Replace with your database name
457 + @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
458 + @BackupType = 'Full',                        -- Full backup
459 + @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
460 + @Compress = 'Y',                             -- Compress the backup
461 + @Verify = 'N'; -- No verification of backup
462 +GO--
463 +
464 +
465 +
466 +
467 +
468 +select name, database_id, state_desc
469 +from sys.databases
470 +
471 +
472 +SELECT
473 + database_id,
474 + key_algorithm,
475 + key_length,
476 + encryption_state_desc
477 + encryptor_type
478 +
479 +FROM
480 + select * from sys.dm_database_encryption_keys;
481 +
482 +
483 + select name, is_encrypted from sys.databases
484 +
485 +
486 + SELECT
487 + cert.name AS Certificate_Name,
488 + cert.subject AS Certificate_Subject,
489 + cert.issuer_name AS Issuer_Name,
490 + cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
491 +FROM
492 + sys.certificates cert
493 +WHERE
494 + cert.name LIKE 'TDE%';
495 +
496 + ALTER DATABASE dba1
497 +SET ENCRYPTION off;
498 +GO
499 +
500 +
501 +Use dba1;
502 +DROP DATABASE ENCRYPTION KEY;
503 +
504 +
505 +USE [dba1]
506 +GO
507 +
508 +CREATE DATABASE ENCRYPTION KEY
509 +WITH ALGORITHM = AES_256
510 +ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
511 +GO
512 +
513 +
514 +
515 +ALTER DATABASE dba1
516 +SET ENCRYPTION ON
517 +
518 +
519 +
520 +
521 +
522 +
523 +
524 +
525 +
526 +
527 +
528 +
529 +
530 +
531 +
532 +
533 +
534 +
535 +
536 +
537 +{{code language="sql"}}
2 2  USE master;
3 3  GO
4 4  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
... ... @@ -31,3 +31,53 @@
31 31  SET ENCRYPTION ON;
32 32  GO
33 33  {{/code}}
570 +
571 +
572 +
573 +
574 +
575 +
576 +
577 +
578 +
579 +
580 +
581 +
582 +
583 +{{code language="sql"}}
584 +USE master;
585 +GO
586 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
587 +GO
588 +
589 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
590 +GO
591 +
592 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
593 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
594 +ENCRYPTION BY PASSWORD='*****')
595 +
596 +USE Everest_TDE_Master;
597 +GO
598 +CREATE DATABASE ENCRYPTION KEY
599 +WITH ALGORITHM = AES_256
600 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
601 +GO
602 +ALTER DATABASE Everest_TDE_Master
603 +SET ENCRYPTION ON;
604 +GO
605 +
606 +USE Everest_TDE_Master_Documents;
607 +GO
608 +CREATE DATABASE ENCRYPTION KEY
609 +WITH ALGORITHM = AES_256
610 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
611 +GO
612 +ALTER DATABASE Everest_TDE_Master_Documents
613 +SET ENCRYPTION ON;
614 +GO
615 +{{/code}}
616 +
617 +
618 +
619 +
XWiki.XWikiComments[0]
Author
... ... @@ -1,0 +1,1 @@
1 +XWiki.rudim
Comment
... ... @@ -1,0 +1,1 @@
1 +put the sql code in code tags to start with otherwise its very hard to read
Date
... ... @@ -1,0 +1,1 @@
1 +2025-02-26 16:00:04.223

Applications

Navigation

Need help?

If you need help with XWiki you can contact: