Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 5.1 >
edited by Nikhil Singh
on 2025/02/26 13:57
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
... ... @@ -11,23 +11,31 @@
11 11  
12 12  5 Set encryption on for the database
13 13  
14 --- 6 Create a new credential with the SAS token
14 +
15 +6 Create credential with SAS token
16 +
17 +{{code language="sql"}}
18 +drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
15 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--
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}}
19 19  
20 ---7 Perform the database backup with the provided parameters
21 -EXECUTE dba.dbo.DatabaseBackup
22 - @Databases = 'dba',                          -- Replace with your database name
23 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
24 - @BackupType = 'Full',                        -- Full backup
25 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
26 - @Compress = 'Y',                             -- Compress the backup
27 - @Verify = 'N'; -- No verification of backup
28 -GO--
29 29  
27 +7
30 30  
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 +
31 31  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)
32 32  
33 33  We have unencrypted a database and backed it up from ssms to azure blob successfully
... ... @@ -39,28 +39,278 @@
39 39  
40 40  
41 41  
50 += **Using TDE directly from azure portal** =
42 42  
43 43  
53 +===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
44 44  
45 45  
56 +- Go to keys and click generate
46 46  
58 +- Name your key (mysqlmikey)
47 47  
60 +- Choose a key type (RSA)
48 48  
62 +-Choose RSA key size (2048-bit)
49 49  
64 +Note:
50 50  
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.
51 51  
70 +- Click create
52 52  
53 53  
73 +[[image:image-20250228120622-1.png||height="236" width="542"]]
54 54  
55 55  
76 +===== 2 Enable TDE =====
56 56  
57 57  
79 +- Go to your Managed instance ( sqlmi-ebs-lab)
58 58  
81 +- Click on security and choose Transparent data encryption
59 59  
83 +- Select the type of managed key ( Customer-managed key0
60 60  
85 +- Select the key from the key vault we generated in the key vault( mysqlmikey)
61 61  
87 +- Make the key the default TDE protector
62 62  
89 +-Click save
63 63  
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 +
64 64  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
65 65  GO
66 66  
... ... @@ -176,8 +176,7 @@
176 176  
177 177  BACKUP DATABASE [dba2]
178 178  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']],
179 -
180 - COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
437 +\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
181 181   COMPRESSION,  -- Optional: Compresses the backup to save storage space
182 182   STATS = 10 -- Optional: Provides backup progress status
183 183  GO--
... ... @@ -277,10 +277,39 @@
277 277  
278 278  
279 279  
537 +{{code language="sql"}}
538 +USE master;
539 +GO
540 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
541 +GO
280 280  
543 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
544 +GO
281 281  
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='*****')
282 282  
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
283 283  
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}}
284 284  
285 285  
286 286  
... ... @@ -290,6 +290,10 @@
290 290  
291 291  
292 292  
579 +
580 +
581 +
582 +
293 293  {{code language="sql"}}
294 294  USE master;
295 295  GO
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: