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

Applications

Navigation

Need help?

If you need help with XWiki you can contact: