Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 8.1 >
edited by Nikhil Singh
on 2025/02/28 12:42
To version < 4.1 >
edited by Nikhil Singh
on 2025/02/26 13:24
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1,324 +1,5 @@
1 -= TDE(backup from smss to azure) =
1 += TDE(1) =
2 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 -
17 -{{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 322  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
323 323  GO
324 324  
... ... @@ -329,18 +329,18 @@
329 329  2 USE master;
330 330  GO
331 331  
332 -SELECT
13 +SELECT
333 333   cert.name AS Certificate_Name,
334 334   cert.subject AS Certificate_Subject,
335 335   cert.issuer_name AS Issuer_Name,
336 336   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
337 -FROM
18 +FROM
338 338   sys.certificates cert
339 -WHERE
20 +WHERE
340 340   cert.name LIKE 'TDE%';
341 341  
342 342  
343 -3 BACKUP CERTIFICATE TDE_Certificate
24 +3 BACKUP CERTIFICATE TDE_Certificate
344 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 345  WITH PRIVATE KEY (
346 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',
... ... @@ -356,7 +356,7 @@
356 356  CREATE DATABASE ENCRYPTION KEY
357 357  WITH ALGORITHM = AES_256
358 358  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
359 -GO--
40 +GO
360 360  
361 361  USE [dba]
362 362  GO
... ... @@ -405,7 +405,7 @@
405 405  -- 7. Enable Transparent Data Encryption (TDE) on the database
406 406  ALTER DATABASE dba
407 407  SET ENCRYPTION ON;
408 -GO--
89 +GO
409 409  
410 410  
411 411  select name, database_id, state_desc
... ... @@ -412,14 +412,14 @@
412 412  from sys.databases
413 413  
414 414  
415 -SELECT
96 +SELECT
416 416   database_id,
417 417   key_algorithm,
418 418   key_length,
419 419   encryption_state_desc
420 - encryptor_type
101 + encryptor_type
421 421  
422 -FROM
103 +FROM
423 423   sys.dm_database_encryption_keys;
424 424  
425 425  
... ... @@ -426,40 +426,41 @@
426 426   Select * from sys.dm_database_encryption_keys
427 427  
428 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']]
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'
431 431  With copy_only
432 432  GO
433 433  
434 434  
435 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
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
439 439   STATS = 10 -- Optional: Provides backup progress status
440 -GO--
122 +GO
441 441  
442 442  
443 443  
444 444  -- Step 1: Drop the existing credential (if needed)
445 445  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
446 -GO--
128 +GO
447 447  
448 448  -- Step 2: Create a new credential with the SAS token
449 449  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
450 450  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
451 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--
134 +GO
453 453  
454 454  -- Step 3: Perform the database backup with the provided parameters
455 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
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
459 459   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
460 - @Compress = 'Y',                             -- Compress the backup
142 + @Compress = 'Y', -- Compress the backup
461 461   @Verify = 'N'; -- No verification of backup
462 -GO--
144 +GO
463 463  
464 464  
465 465  
... ... @@ -469,14 +469,14 @@
469 469  from sys.databases
470 470  
471 471  
472 -SELECT
154 +SELECT
473 473   database_id,
474 474   key_algorithm,
475 475   key_length,
476 476   encryption_state_desc
477 - encryptor_type
159 + encryptor_type
478 478  
479 -FROM
161 +FROM
480 480   select * from sys.dm_database_encryption_keys;
481 481  
482 482  
... ... @@ -483,17 +483,17 @@
483 483   select name, is_encrypted from sys.databases
484 484  
485 485  
486 - SELECT
168 + SELECT
487 487   cert.name AS Certificate_Name,
488 488   cert.subject AS Certificate_Subject,
489 489   cert.issuer_name AS Issuer_Name,
490 490   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
491 -FROM
173 +FROM
492 492   sys.certificates cert
493 -WHERE
175 +WHERE
494 494   cert.name LIKE 'TDE%';
495 495  
496 - ALTER DATABASE dba1
178 + ALTER DATABASE dba1
497 497  SET ENCRYPTION off;
498 498  GO
499 499  
... ... @@ -534,39 +534,10 @@
534 534  
535 535  
536 536  
537 -{{code language="sql"}}
538 -USE master;
539 -GO
540 -CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
541 -GO
542 542  
543 -CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
544 -GO
545 545  
546 -BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
547 -WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
548 -ENCRYPTION BY PASSWORD='*****')
549 549  
550 -USE Everest_TDE_Master;
551 -GO
552 -CREATE DATABASE ENCRYPTION KEY
553 -WITH ALGORITHM = AES_256
554 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
555 -GO
556 -ALTER DATABASE Everest_TDE_Master
557 -SET ENCRYPTION ON;
558 -GO
559 559  
560 -USE Everest_TDE_Master_Documents;
561 -GO
562 -CREATE DATABASE ENCRYPTION KEY
563 -WITH ALGORITHM = AES_256
564 -ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
565 -GO
566 -ALTER DATABASE Everest_TDE_Master_Documents
567 -SET ENCRYPTION ON;
568 -GO
569 -{{/code}}
570 570  
571 571  
572 572  
... ... @@ -579,7 +579,6 @@
579 579  
580 580  
581 581  
582 -
583 583  {{code language="sql"}}
584 584  USE master;
585 585  GO
... ... @@ -613,7 +613,3 @@
613 613  SET ENCRYPTION ON;
614 614  GO
615 615  {{/code}}
616 -
617 -
618 -
619 -
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: