Wiki source code of sql-tde

Version 4.1 by Nikhil Singh on 2025/02/26 13:24
Show last authors
1 = TDE(1) =
2
3 1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
4 GO
5
6 CREATE CERTIFICATE TDE_Certificate
7 WITH SUBJECT = 'TDE Certificate';
8 GO
9
10 2 USE master;
11 GO
12
13 SELECT
14 cert.name AS Certificate_Name,
15 cert.subject AS Certificate_Subject,
16 cert.issuer_name AS Issuer_Name,
17 cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
18 FROM
19 sys.certificates cert
20 WHERE
21 cert.name LIKE 'TDE%';
22
23
24 3 BACKUP CERTIFICATE TDE_Certificate
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 WITH PRIVATE KEY (
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',
28 ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
29 );
30 GO
31
32
33 USE Normal;
34 GO
35
36 -- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
37 CREATE DATABASE ENCRYPTION KEY
38 WITH ALGORITHM = AES_256
39 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
40 GO
41
42 USE [dba]
43 GO
44
45 CREATE DATABASE ENCRYPTION KEY
46 WITH ALGORITHM = AES_256
47 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
48 GO
49
50
51 USE [dba1]
52 GO
53
54 CREATE DATABASE ENCRYPTION KEY
55 WITH ALGORITHM = AES_256
56 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
57 GO
58
59
60 USE [dba2]
61 GO
62
63 CREATE DATABASE ENCRYPTION KEY
64 WITH ALGORITHM = AES_256
65 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
66 GO
67
68
69 USE [dba3]
70 GO
71
72 CREATE DATABASE ENCRYPTION KEY
73 WITH ALGORITHM = AES_256
74 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
75 GO
76
77 USE [xwiki]
78 GO
79
80 CREATE DATABASE ENCRYPTION KEY
81 WITH ALGORITHM = AES_256
82 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
83 GO
84
85
86 -- 7. Enable Transparent Data Encryption (TDE) on the database
87 ALTER DATABASE dba
88 SET ENCRYPTION ON;
89 GO
90
91
92 select name, database_id, state_desc
93 from sys.databases
94
95
96 SELECT
97 database_id,
98 key_algorithm,
99 key_length,
100 encryption_state_desc
101 encryptor_type
102
103 FROM
104 sys.dm_database_encryption_keys;
105
106
107 Select * from sys.dm_database_encryption_keys
108
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'
112 With copy_only
113 GO
114
115
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
121 STATS = 10 -- Optional: Provides backup progress status
122 GO
123
124
125
126 -- Step 1: Drop the existing credential (if needed)
127 DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
128 GO
129
130 -- Step 2: Create a new credential with the SAS token
131 CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
132 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
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
135
136 -- Step 3: Perform the database backup with the provided parameters
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
141 @CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
142 @Compress = 'Y', -- Compress the backup
143 @Verify = 'N'; -- No verification of backup
144 GO
145
146
147
148
149
150 select name, database_id, state_desc
151 from sys.databases
152
153
154 SELECT
155 database_id,
156 key_algorithm,
157 key_length,
158 encryption_state_desc
159 encryptor_type
160
161 FROM
162 select * from sys.dm_database_encryption_keys;
163
164
165 select name, is_encrypted from sys.databases
166
167
168 SELECT
169 cert.name AS Certificate_Name,
170 cert.subject AS Certificate_Subject,
171 cert.issuer_name AS Issuer_Name,
172 cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
173 FROM
174 sys.certificates cert
175 WHERE
176 cert.name LIKE 'TDE%';
177
178 ALTER DATABASE dba1
179 SET ENCRYPTION off;
180 GO
181
182
183 Use dba1;
184 DROP DATABASE ENCRYPTION KEY;
185
186
187 USE [dba1]
188 GO
189
190 CREATE DATABASE ENCRYPTION KEY
191 WITH ALGORITHM = AES_256
192 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
193 GO
194
195
196
197 ALTER DATABASE dba1
198 SET ENCRYPTION ON
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235 {{code language="sql"}}
236 USE master;
237 GO
238 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
239 GO
240
241 CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
242 GO
243
244 BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
245 WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
246 ENCRYPTION BY PASSWORD='*****')
247
248 USE Everest_TDE_Master;
249 GO
250 CREATE DATABASE ENCRYPTION KEY
251 WITH ALGORITHM = AES_256
252 ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
253 GO
254 ALTER DATABASE Everest_TDE_Master
255 SET ENCRYPTION ON;
256 GO
257
258 USE Everest_TDE_Master_Documents;
259 GO
260 CREATE DATABASE ENCRYPTION KEY
261 WITH ALGORITHM = AES_256
262 ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
263 GO
264 ALTER DATABASE Everest_TDE_Master_Documents
265 SET ENCRYPTION ON;
266 GO
267 {{/code}}