Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 2.3 >
edited by Rudi Marais
on 2023/05/22 23:50
To version < 5.1 >
edited by Nikhil Singh
on 2025/02/26 13:57
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Parent
... ... @@ -1,1 +1,1 @@
1 -xwiki:technical-documentation.analysis.sql.WebHome
1 +technical-documentation.analysis.sql.WebHome
Author
... ... @@ -1,1 +1,1 @@
1 -XWiki.rudim
1 +XWiki.nikhils
Content
... ... @@ -1,3 +1,295 @@
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 +-- 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 +
1 1  {{code language="sql"}}
2 2  USE master;
3 3  GO
... ... @@ -31,3 +31,7 @@
31 31  SET ENCRYPTION ON;
32 32  GO
33 33  {{/code}}
326 +
327 +
328 +
329 +

Applications

Navigation

Need help?

If you need help with XWiki you can contact: