Wiki source code of sql-tde

Version 5.1 by Nikhil Singh on 2025/02/26 13:57

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

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?