Wiki source code of sql-tde

Version 6.2 by Rudi Marais on 2025/02/26 16:00

version	line-number	content
5.1	1	= TDE(backup from smss to azure) =
3.1	2
5.1	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
6.1	42	= Using TDE directly from azure portal =
5.1	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
3.2	65	1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
	66	GO
3.1	67
3.2	68	CREATE CERTIFICATE TDE_Certificate
	69	WITH SUBJECT = 'TDE Certificate';
	70	GO
3.1	71
3.2	72	2 USE master;
	73	GO
3.1	74
5.1	75	SELECT
3.2	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
5.1	80	FROM
3.2	81	sys.certificates cert
5.1	82	WHERE
3.2	83	cert.name LIKE 'TDE%';
3.1	84
3.2	85
5.1	86	3 BACKUP CERTIFICATE TDE_Certificate
3.2	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;
5.1	102	GO--
3.2	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;
5.1	151	GO--
3.2	152
	153
	154	select name, database_id, state_desc
	155	from sys.databases
	156
	157
5.1	158	SELECT
3.2	159	database_id,
	160	key_algorithm,
	161	key_length,
	162	encryption_state_desc
5.1	163	encryptor_type
3.2	164
5.1	165	FROM
3.2	166	sys.dm_database_encryption_keys;
	167
	168
	169	Select * from sys.dm_database_encryption_keys
	170
	171
5.1	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']]
3.2	174	With copy_only
	175	GO
	176
	177
	178	BACKUP DATABASE [dba2]
5.1	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']],
6.1	180
5.1	181	COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
	182	COMPRESSION, -- Optional: Compresses the backup to save storage space
3.2	183	STATS = 10 -- Optional: Provides backup progress status
5.1	184	GO--
3.2	185
	186
	187
	188	-- Step 1: Drop the existing credential (if needed)
	189	DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
5.1	190	GO--
3.2	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';
5.1	196	GO--
3.2	197
	198	-- Step 3: Perform the database backup with the provided parameters
	199	EXECUTE dba.dbo.DatabaseBackup
5.1	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
3.2	203	@CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
5.1	204	@Compress = 'Y', -- Compress the backup
3.2	205	@Verify = 'N'; -- No verification of backup
5.1	206	GO--
3.2	207
	208
	209
	210
	211
	212	select name, database_id, state_desc
	213	from sys.databases
	214
	215
5.1	216	SELECT
3.2	217	database_id,
	218	key_algorithm,
	219	key_length,
	220	encryption_state_desc
5.1	221	encryptor_type
3.2	222
5.1	223	FROM
3.2	224	select * from sys.dm_database_encryption_keys;
	225
	226
	227	select name, is_encrypted from sys.databases
	228
	229
5.1	230	SELECT
3.2	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
5.1	235	FROM
3.2	236	sys.certificates cert
5.1	237	WHERE
3.2	238	cert.name LIKE 'TDE%';
	239
5.1	240	ALTER DATABASE dba1
3.2	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
2.1	294	{{code language="sql"}}
	295	USE master;
	296	GO
	297	CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
	298	GO
	299
	300	CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
	301	GO
	302
	303	BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
	304	WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
	305	ENCRYPTION BY PASSWORD='*****')
	306
	307	USE Everest_TDE_Master;
	308	GO
	309	CREATE DATABASE ENCRYPTION KEY
	310	WITH ALGORITHM = AES_256
	311	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	312	GO
	313	ALTER DATABASE Everest_TDE_Master
	314	SET ENCRYPTION ON;
	315	GO
	316
	317	USE Everest_TDE_Master_Documents;
	318	GO
	319	CREATE DATABASE ENCRYPTION KEY
	320	WITH ALGORITHM = AES_256
	321	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	322	GO
	323	ALTER DATABASE Everest_TDE_Master_Documents
	324	SET ENCRYPTION ON;
	325	GO
	326	{{/code}}
5.1	327
	328
	329
	330

Applications

More applications

Need help?

If you need help with XWiki you can contact:

XWiki 14.10.21

Wiki source code of sql-tde

Applications

Navigation

Need help?