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 < 7.1 >
edited by Nikhil Singh
on 2025/02/28 11:30
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -1,5 +1,111 @@
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 +-- 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 --
21 +
22 +{{code language="sql"}}
23 +Perform the database backup with the provided parameters
24 +EXECUTE dba.dbo.DatabaseBackup
25 +    @Databases = 'dba',                           Replace with your database name
26 +    @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',   Azure Blob Storage URL
27 +    @BackupType = 'Full',                         Full backup
28 +    @CopyOnly = 'Y',                              Copy-only backup to avoid breaking backup chain
29 +    @Compress = 'Y',                              Compress the backup
30 +    @Verify = 'N';                                No verification of backup
31 +GO
32 +{{/code}}
33 +
34 +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)
35 +
36 +We have unencrypted a database and backed it up from ssms to azure blob successfully
37 +
38 +
39 +Conclusion: Can be done with and unencrypted DB but not with an Encrypted one>
40 +
41 +
42 +
43 +
44 +
45 += **Using TDE directly from azure portal** =
46 +
47 +
48 +===== 1 Go to azure key vault ( DemoTestRudi) to generate a key =====
49 +
50 +
51 +- Go to keys and click generate
52 +
53 +- Name your key (mysqlmikey)
54 +
55 +- Choose a key type (RSA)
56 +
57 +-Choose RSA key size (2048-bit)
58 +
59 +Note:
60 +
61 +* 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.
62 +* **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.).
63 +* 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.
64 +
65 +- Click create
66 +
67 +
68 +[[image:image-20250228120622-1.png||height="236" width="542"]]
69 +
70 +
71 +===== 2 Enable TDE =====
72 +
73 +
74 +- Go to your Managed instance ( sqlmi-ebs-lab)
75 +
76 +- Click on security and choose Transparent data encryption
77 +
78 +- Select the type of managed key ( Customer-managed key0
79 +
80 +- Select the key from the key vault we generated in the key vault( mysqlmikey)
81 +
82 +- Make the key the default TDE protector
83 +
84 +-Click save
85 +
86 +
87 +[[image:image-20250228122106-2.png||height="251" width="511"]]
88 +
89 +
90 +- TDE has now been enabled on the Managed instance
91 +
92 +
93 +Conclusion: All the databases have been encrypted by an asymmetric key. The key is the same for each database ( same encryption thumbprint).
94 +
95 +We are now able to backup databases from SSMS to Azure storage.
96 +
97 +
98 +
99 +
100 +
101 +
102 +
103 +
104 +
105 +
106 +
107 +
108 +
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
119 +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
124 +FROM
19 19   sys.certificates cert
20 -WHERE
126 +WHERE
21 21   cert.name LIKE 'TDE%';
22 22  
23 23  
24 -3 BACKUP CERTIFICATE TDE_Certificate
130 +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
146 +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
195 +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
202 +SELECT
97 97   database_id,
98 98   key_algorithm,
99 99   key_length,
100 100   encryption_state_desc
101 - encryptor_type
207 + encryptor_type
102 102  
103 -FROM
209 +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'
216 + BACKUP DATABASE [dba2]
217 +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
223 +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']],
224 +\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
225 + COMPRESSION,  -- Optional: Compresses the backup to save storage space
121 121   STATS = 10 -- Optional: Provides backup progress status
122 -GO
227 +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
233 +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
239 +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
243 + @Databases = 'dba',                          -- Replace with your database name
244 + @URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
245 + @BackupType = 'Full',                        -- Full backup
141 141   @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 - @Compress = 'Y', -- Compress the backup
247 + @Compress = 'Y',                             -- Compress the backup
143 143   @Verify = 'N'; -- No verification of backup
144 -GO
249 +GO--
145 145  
146 146  
147 147  
... ... @@ -151,14 +151,14 @@
151 151  from sys.databases
152 152  
153 153  
154 -SELECT
259 +SELECT
155 155   database_id,
156 156   key_algorithm,
157 157   key_length,
158 158   encryption_state_desc
159 - encryptor_type
264 + encryptor_type
160 160  
161 -FROM
266 +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
273 + 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
278 +FROM
174 174   sys.certificates cert
175 -WHERE
280 +WHERE
176 176   cert.name LIKE 'TDE%';
177 177  
178 - ALTER DATABASE dba1
283 + ALTER DATABASE dba1
179 179  SET ENCRYPTION off;
180 180  GO
181 181  
... ... @@ -216,10 +216,39 @@
216 216  
217 217  
218 218  
324 +{{code language="sql"}}
325 +USE master;
326 +GO
327 +CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
328 +GO
219 219  
330 +CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
331 +GO
220 220  
333 +BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
334 +WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
335 +ENCRYPTION BY PASSWORD='*****')
221 221  
337 +USE Everest_TDE_Master;
338 +GO
339 +CREATE DATABASE ENCRYPTION KEY
340 +WITH ALGORITHM = AES_256
341 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
342 +GO
343 +ALTER DATABASE Everest_TDE_Master
344 +SET ENCRYPTION ON;
345 +GO
222 222  
347 +USE Everest_TDE_Master_Documents;
348 +GO
349 +CREATE DATABASE ENCRYPTION KEY
350 +WITH ALGORITHM = AES_256
351 +ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
352 +GO
353 +ALTER DATABASE Everest_TDE_Master_Documents
354 +SET ENCRYPTION ON;
355 +GO
356 +{{/code}}
223 223  
224 224  
225 225  
... ... @@ -232,6 +232,7 @@
232 232  
233 233  
234 234  
369 +
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}}
403 +
404 +
405 +
406 +
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: