Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 4.1 >
edited by Nikhil Singh
on 2025/02/26 13:24
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
Content
... ... @@ -1,5 +1,324 @@
1 -= TDE(1) =
1 += TDE(backup from smss to azure) =
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 +
3 3  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
4 4  GO
5 5  
... ... @@ -10,18 +10,18 @@
10 10  2 USE master;
11 11  GO
12 12  
13 -SELECT
332 +SELECT
14 14   cert.name AS Certificate_Name,
15 15   cert.subject AS Certificate_Subject,
16 16   cert.issuer_name AS Issuer_Name,
17 17   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
18 -FROM
337 +FROM
19 19   sys.certificates cert
20 -WHERE
339 +WHERE
21 21   cert.name LIKE 'TDE%';
22 22  
23 23  
24 -3 BACKUP CERTIFICATE TDE_Certificate
343 +3 BACKUP CERTIFICATE TDE_Certificate
25 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 26  WITH PRIVATE KEY (
27 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',
... ... @@ -37,7 +37,7 @@
37 37  CREATE DATABASE ENCRYPTION KEY
38 38  WITH ALGORITHM = AES_256
39 39  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
40 -GO
359 +GO--
41 41  
42 42  USE [dba]
43 43  GO
... ... @@ -86,7 +86,7 @@
86 86  -- 7. Enable Transparent Data Encryption (TDE) on the database
87 87  ALTER DATABASE dba
88 88  SET ENCRYPTION ON;
89 -GO
408 +GO--
90 90  
91 91  
92 92  select name, database_id, state_desc
... ... @@ -93,14 +93,14 @@
93 93  from sys.databases
94 94  
95 95  
96 -SELECT
415 +SELECT
97 97   database_id,
98 98   key_algorithm,
99 99   key_length,
100 100   encryption_state_desc
101 - encryptor_type
420 + encryptor_type
102 102  
103 -FROM
422 +FROM
104 104   sys.dm_database_encryption_keys;
105 105  
106 106  
... ... @@ -107,41 +107,40 @@
107 107   Select * from sys.dm_database_encryption_keys
108 108  
109 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'
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']]
112 112  With copy_only
113 113  GO
114 114  
115 115  
116 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
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
121 121   STATS = 10 -- Optional: Provides backup progress status
122 -GO
440 +GO--
123 123  
124 124  
125 125  
126 126  -- Step 1: Drop the existing credential (if needed)
127 127  DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
128 -GO
446 +GO--
129 129  
130 130  -- Step 2: Create a new credential with the SAS token
131 131  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
132 132  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
133 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
452 +GO--
135 135  
136 136  -- Step 3: Perform the database backup with the provided parameters
137 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
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
141 141   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 - @Compress = 'Y', -- Compress the backup
460 + @Compress = 'Y',                             -- Compress the backup
143 143   @Verify = 'N'; -- No verification of backup
144 -GO
462 +GO--
145 145  
146 146  
147 147  
... ... @@ -151,14 +151,14 @@
151 151  from sys.databases
152 152  
153 153  
154 -SELECT
472 +SELECT
155 155   database_id,
156 156   key_algorithm,
157 157   key_length,
158 158   encryption_state_desc
159 - encryptor_type
477 + encryptor_type
160 160  
161 -FROM
479 +FROM
162 162   select * from sys.dm_database_encryption_keys;
163 163  
164 164  
... ... @@ -165,17 +165,17 @@
165 165   select name, is_encrypted from sys.databases
166 166  
167 167  
168 - SELECT
486 + SELECT
169 169   cert.name AS Certificate_Name,
170 170   cert.subject AS Certificate_Subject,
171 171   cert.issuer_name AS Issuer_Name,
172 172   cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
173 -FROM
491 +FROM
174 174   sys.certificates cert
175 -WHERE
493 +WHERE
176 176   cert.name LIKE 'TDE%';
177 177  
178 - ALTER DATABASE dba1
496 + ALTER DATABASE dba1
179 179  SET ENCRYPTION off;
180 180  GO
181 181  
... ... @@ -216,10 +216,39 @@
216 216  
217 217  
218 218  
537 +{{code language="sql"}}
538 +USE master;
539 +GO
540 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
541 +GO
219 219  
543 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
544 +GO
220 220  
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='*****')
221 221  
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
222 222  
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}}
223 223  
224 224  
225 225  
... ... @@ -232,6 +232,7 @@
232 232  
233 233  
234 234  
582 +
235 235  {{code language="sql"}}
236 236  USE master;
237 237  GO
... ... @@ -265,3 +265,7 @@
265 265  SET ENCRYPTION ON;
266 266  GO
267 267  {{/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: