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 < 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,295 +1,11 @@
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 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 -
30 -
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 -
33 -We have unencrypted a database and backed it up from ssms to azure blob successfully
34 -
35 -
36 -Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
37 -
38 -
39 -
40 -
41 -
42 -
43 -
44 -
45 -
46 -
47 -
48 -
49 -
50 -
51 -
52 -
53 -
54 -
55 -
56 -
57 -
58 -
59 -
60 -
61 -
62 -
63 -
64 -1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
65 -GO
66 -
67 -CREATE CERTIFICATE TDE_Certificate
68 -WITH SUBJECT = 'TDE Certificate';
69 -GO
70 -
71 -2 USE master;
72 -GO
73 -
74 -SELECT
75 - cert.name AS Certificate_Name,
76 - cert.subject AS Certificate_Subject,
77 - cert.issuer_name AS Issuer_Name,
78 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
79 -FROM
80 - sys.certificates cert
81 -WHERE
82 - cert.name LIKE 'TDE%';
83 -
84 -
85 -3 BACKUP CERTIFICATE TDE_Certificate
86 -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'
87 -WITH PRIVATE KEY (
88 - 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',
89 - ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
90 -);
91 -GO
92 -
93 -
94 -USE Normal;
95 -GO
96 -
97 --- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
98 -CREATE DATABASE ENCRYPTION KEY
99 -WITH ALGORITHM = AES_256
100 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
101 -GO--
102 -
103 -USE [dba]
104 -GO
105 -
106 -CREATE DATABASE ENCRYPTION KEY
107 -WITH ALGORITHM = AES_256
108 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
109 -GO
110 -
111 -
112 -USE [dba1]
113 -GO
114 -
115 -CREATE DATABASE ENCRYPTION KEY
116 -WITH ALGORITHM = AES_256
117 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
118 -GO
119 -
120 -
121 -USE [dba2]
122 -GO
123 -
124 -CREATE DATABASE ENCRYPTION KEY
125 -WITH ALGORITHM = AES_256
126 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
127 -GO
128 -
129 -
130 -USE [dba3]
131 -GO
132 -
133 -CREATE DATABASE ENCRYPTION KEY
134 -WITH ALGORITHM = AES_256
135 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
136 -GO
137 -
138 -USE [xwiki]
139 -GO
140 -
141 -CREATE DATABASE ENCRYPTION KEY
142 -WITH ALGORITHM = AES_256
143 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
144 -GO
145 -
146 -
147 --- 7. Enable Transparent Data Encryption (TDE) on the database
148 -ALTER DATABASE dba
149 -SET ENCRYPTION ON;
150 -GO--
151 -
152 -
153 -select name, database_id, state_desc
154 -from sys.databases
155 -
156 -
157 -SELECT
158 - database_id,
159 - key_algorithm,
160 - key_length,
161 - encryption_state_desc
162 - encryptor_type
163 -
164 -FROM
165 - sys.dm_database_encryption_keys;
166 -
167 -
168 - Select * from sys.dm_database_encryption_keys
169 -
170 -
171 - BACKUP DATABASE [dba2]
172 -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']]
173 -With copy_only
174 -GO
175 -
176 -
177 -BACKUP DATABASE [dba2]
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
181 - COMPRESSION,  -- Optional: Compresses the backup to save storage space
182 - STATS = 10 -- Optional: Provides backup progress status
183 -GO--
184 -
185 -
186 -
187 --- Step 1: Drop the existing credential (if needed)
188 -DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
189 -GO--
190 -
191 --- Step 2: Create a new credential with the SAS token
192 -CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
193 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
194 -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';
195 -GO--
196 -
197 --- Step 3: Perform the database backup with the provided parameters
198 -EXECUTE dba.dbo.DatabaseBackup
199 - @Databases = 'dba',                          -- Replace with your database name
200 - @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
201 - @BackupType = 'Full',                        -- Full backup
202 - @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
203 - @Compress = 'Y',                             -- Compress the backup
204 - @Verify = 'N'; -- No verification of backup
205 -GO--
206 -
207 -
208 -
209 -
210 -
211 -select name, database_id, state_desc
212 -from sys.databases
213 -
214 -
215 -SELECT
216 - database_id,
217 - key_algorithm,
218 - key_length,
219 - encryption_state_desc
220 - encryptor_type
221 -
222 -FROM
223 - select * from sys.dm_database_encryption_keys;
224 -
225 -
226 - select name, is_encrypted from sys.databases
227 -
228 -
229 - SELECT
230 - cert.name AS Certificate_Name,
231 - cert.subject AS Certificate_Subject,
232 - cert.issuer_name AS Issuer_Name,
233 - cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
234 -FROM
235 - sys.certificates cert
236 -WHERE
237 - cert.name LIKE 'TDE%';
238 -
239 - ALTER DATABASE dba1
240 -SET ENCRYPTION off;
241 -GO
242 -
243 -
244 -Use dba1;
245 -DROP DATABASE ENCRYPTION KEY;
246 -
247 -
248 -USE [dba1]
249 -GO
250 -
251 -CREATE DATABASE ENCRYPTION KEY
252 -WITH ALGORITHM = AES_256
253 -ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
254 -GO
255 -
256 -
257 -
258 -ALTER DATABASE dba1
259 -SET ENCRYPTION ON
260 -
261 -
262 -
263 -
264 -
265 -
266 -
267 -
268 -
269 -
270 -
271 -
272 -
273 -
274 -
275 -
276 -
277 -
278 -
279 -
280 -
281 -
282 -
283 -
284 -
285 -
286 -
287 -
288 -
289 -
290 -
291 -
292 -
293 293  {{code language="sql"}}
294 294  USE master;
295 295  GO
... ... @@ -323,7 +323,3 @@
323 323  SET ENCRYPTION ON;
324 324  GO
325 325  {{/code}}
326 -
327 -
328 -
329 -

Applications

Navigation

Need help?

If you need help with XWiki you can contact: