Wiki source code of sql-tde

Version 10.1 by Rudi Marais on 2025/03/05 13:24

version	line-number	content
9.1	1	= 1. Steps for Implementing Transparent Data Encryption (TDE) FROM SQL Server =
3.1	2
5.1	3
9.1	4	This document outlines the process of encrypting SQL Server databases using Transparent Data Encryption (TDE) and backing them up to Azure Storage. TDE ensures data at rest is encrypted, leveraging a Master Key (MK), TDE Certificate, and Database Encryption Key (DEK) for encryption.
5.1	5
9.1	6	The process also includes creating a credential for secure backup to Azure Blob Storage, providing a scalable and secure solution for storing encrypted databases in the cloud.
5.1	7
	8
10.1	9	[[image:image-20250305152543-1.png]]
	10
	11
9.1	12	~1. Create a Master Key in the master Database
	13	The first step is to create a Master Key in the master database. This key will be used to encrypt other cryptographic objects, such as certificates and symmetric keys, within SQL Server.
5.1	14
	15
9.1	16	2. Create a TDE Certificate (Encrypted by the Master Key)
	17	Next, generate a TDE certificate that will be used to encrypt the Database Encryption Key (DEK). This certificate is encrypted by the Master Key created in step 1, providing an additional layer of security.
8.1	18
	19
9.1	20	3. Choose the Database to Create the Database Encryption Key (DEK)
	21	For the selected database, create the Database Encryption Key (DEK). The DEK will be encrypted by the TDE certificate and will use the AES-256 encryption algorithm to ensure data is securely encrypted at rest.
	22
	23
	24	4. Enable Encryption for the Database
	25	Once the DEK has been created, enable TDE for the database. This ensures that all data written to the database is automatically encrypted at rest, providing full protection for sensitive information.
	26
	27
	28	5. Create a Credential with a SAS Token
	29	Finally, create a credential that allows SQL Server to access Azure Blob Storage. This credential is created using a Shared Access Signature (SAS) token, which ensures secure and authenticated access to the storage account for backup purposes.
	30
8.1	31	{{code language="sql"}}
	32	drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
5.1	33	CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
8.1	34	WITH IDENTITY='SHARED ACCESS SIGNATURE'
	35	, 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'
	36	GO
	37
	38	{{/code}}
5.1	39
7.1	40
9.1	41	~ 6. Use a Stored Procedure to Back Up to Azure Storage Account
8.1	42
7.1	43	{{code language="sql"}}
5.1	44	EXECUTE dba.dbo.DatabaseBackup
9.1	45	@Databases = 'dba',
	46	@URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',
	47	@BackupType = 'Full',
	48	@CopyOnly = 'Y',
	49	@Compress = 'Y',
	50	@Verify = 'N'
7.1	51	{{/code}}
5.1	52
10.1	53	=== ===
5.1	54
9.1	55	=== Conclusion: Backup to Azure Storage Account with TDE Encrypted Databases ===
5.1	56
9.1	57	The attempt to back up an encrypted database (with Transparent Data Encryption - TDE) from SQL Server Management Studio (SSMS) to an Azure Storage Account was unsuccessful. This issue arises because Azure does not permit TDE-encrypted databases to be backed up directly to Azure Storage using SSMS.
5.1	58
9.1	59	However, after decrypting the database, we were able to successfully back it up to the Azure Storage Account via SSMS. This confirms that backups can be performed on an unencrypted database, but not on an encrypted database.
5.1	60
	61
	62
	63
	64
9.1	65	= 2. Enabling Transparent Data Encryption (TDE) Using Azure Key Vault =
5.1	66
	67
9.1	68	Introduction: This document outlines the process of enabling Transparent Data Encryption (TDE) on an Azure SQL Managed Instance (SQL MI) using a Customer-Managed Key (CMK) stored in Azure Key Vault. The encryption is managed using an asymmetric key from Azure Key Vault, ensuring that all data within the SQL Managed Instance is encrypted using a strong encryption algorithm.
5.1	69
	70
9.1	71	===== 1. Generate a Key in Azure Key Vault =====
5.1	72
	73
9.1	74	- Navigate to Azure Key Vault:
5.1	75
9.1	76	* Go to the Azure Portal and select Key Vault (DemoRudiTest).
5.1	77
9.1	78	- Generate a New Key:
	79
	80	* Go to the Keys section and click Generate to create a new key.
	81
	82	- Configure Key Settings:
	83
	84	* Name the Key: Choose a name for your key, e.g., mysqlmikey.
	85	* Key Type: Select RSA as the key type.
	86	* RSA Key Size: Choose an RSA key size of 2048-bit. (optional)
	87
	88	Note:
	89
7.1	90	* 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.
	91	* 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.).
	92	* 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.
5.1	93
9.1	94	- Create the Key:
5.1	95
9.1	96	* Click Create to generate the key.
5.1	97
	98
	99
9.1	100	===== 2. Enable TDE on the SQL Managed Instance =====
5.1	101
10.1	102	===== =====
5.1	103
9.1	104	- Navigate to Your Managed Instance:
5.1	105
9.1	106	* Go to your SQL Managed Instance (sqlmi-ebs-lab).
5.1	107
9.1	108	- Enable Transparent Data Encryption (TDE):
5.1	109
9.1	110	* Under Security, select Transparent Data Encryption.
7.1	111
9.1	112	- Configure TDE with a Customer-Managed Key (CMK):
7.1	113
9.1	114	* Select Customer-managed key as the encryption type.
	115	* Choose the key you created earlier from Azure Key Vault (mysqlmikey).
7.1	116
	117
9.1	118	- Set the Key as Default TDE Protector:
7.1	119
9.1	120	* Make the key the default TDE protector for your instance.
7.1	121
9.1	122	- Save Configuration:
7.1	123
9.1	124	* Click Save to apply the changes.
7.1	125
	126
9.1	127	=== Conclusion ===
7.1	128
9.1	129	After following these steps, TDE has been successfully enabled on your SQL Managed Instance using an asymmetric key stored in Azure Key Vault. All databases within the instance are now encrypted using the same encryption key (identified by the same encryption thumbprint). You can now securely back up these encrypted databases from SSMS to Azure Storage.
7.1	130
9.1	131	This process ensures that your data is protected both at rest and during backup, offering enhanced security for your managed databases in the cloud.
7.1	132
	133
9.1	134
	135	=== 1. Backup Specific Databases Using SQL Server Agent Jobs ===
	136
	137	This document describes the process of creating an automated SQL Server Agent Job to back up only the DBA databases in a SQL Server instance. The backups will occur daily at 3:00 AM and will be stored in an Azure Storage Account for secure and reliable cloud storage.
	138
	139	----
	140
	141	==== 1. Create the Backup Script ====
	142
	143
	144	====== - Declaring Variables ======
	145
8.1	146	{{code language="sql"}}
	147	DECLARE @DatabaseName NVARCHAR(128)
	148	DECLARE @BackupContainerURL NVARCHAR(512)
	149	{{/code}}
7.1	150
8.1	151	DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
7.1	152
8.1	153	* @DatabaseName: A variable to hold the name of the database during each iteration of the loop.
	154	* @BackupContainerURL: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
	155
	156	----
	157
9.1	158	====== 2. Setting the Azure Blob Storage URL ======
8.1	159
	160	{{code language="sql"}}
	161	SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
	162	{{/code}}
	163
	164
	165	----
	166
9.1	167	====== 3. Declaring the Cursor ======
8.1	168
	169
	170	{{code language="sql"}}
	171	DECLARE db_cursor CURSOR FOR
	172	SELECT name
	173	FROM sys.databases
	174	WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
	175	{{/code}}
	176
	177	* Cursor Declaration:
	178	** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
	179	The WHERE name LIKE 'dba%'** clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
	180	sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
	181
	182	----
	183
9.1	184	====== 4. Opening the Cursor ======
8.1	185
	186	{{code language="sql"}}
	187	OPEN db_cursor
	188	FETCH NEXT FROM db_cursor INTO @DatabaseName
	189	{{/code}}
	190
	191	OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
	192
	193	* OPEN db_cursor: This opens the cursor for reading the results.
	194	* FETCH NEXT: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.
	195
	196	----
	197
9.1	198	====== 5. Looping Through Databases ======
8.1	199
	200	{{code language="sql"}}
	201	-- Loop through each database and execute the stored procedure
	202	WHILE @@FETCH_STATUS = 0
	203	BEGIN
	204	-- Print current database for logging/debugging
	205	PRINT 'Backing up database: ' + @DatabaseName
	206
	207	-- Execute the DatabaseBackup stored procedure for each database
	208	EXECUTE dba.dbo.DatabaseBackup
	209	@Databases = @DatabaseName, -- Specify the current database
	210	@URL = @BackupContainerURL, -- Azure Blob Storage URL
	211	@BackupType = 'Full', -- Full backup type
	212	@CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
	213	@Compress = 'Y', -- Enable compression
	214	@Verify = 'N'; -- Skip verification after backup
	215
	216	{{/code}}
	217
	218
	219	* WHILE @@FETCH_STATUS = 0: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
	220	* PRINT: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.
	221	* (((
	222	EXECUTE dba.dbo.DatabaseBackup:
	223
	224	* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
	225	* The parameters passed to the stored procedure:
	226	@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
	227	@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
	228	@BackupType = 'Full': Specifies the backup type, which is a Full** backup (this means all data in the database is backed up).
	229	@CopyOnly = 'Y': Specifies that this is a copy-only backup**. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
	230	@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
	231	@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
	232	)))
	233
	234	----
	235
9.1	236	====== 6. Fetch the Next Database ======
8.1	237
	238	{{code language="sql"}}
	239	-- Fetch the next database in the cursor
	240	FETCH NEXT FROM db_cursor INTO @DatabaseName
	241
	242	{{/code}}
	243
	244	~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
	245
	246	* FETCH NEXT: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.
	247
	248	----
	249
9.1	250	====== 7. Closing and Deallocating the Cursor ======
8.1	251
	252	{{code language="sql"}}
	253	-- Close and deallocate the cursor to clean up resources
	254	CLOSE db_cursor
	255	DEALLOCATE db_cursor
	256
	257
	258	{{/code}}
	259
	260	* CLOSE db_cursor: This closes the cursor once the loop finishes processing all databases.
	261	* DEALLOCATE db_cursor: This deallocates the cursor, freeing up any resources used by the cursor. It’s a good practice to always deallocate cursors to avoid resource leaks.
	262
9.1	263	====== ======
8.1	264
9.1	265	====== 8. Full Script ======
8.1	266
	267
	268	{{code language="sql"}}
	269	DECLARE @DatabaseName NVARCHAR(128)
	270	DECLARE @BackupContainerURL NVARCHAR(512)
	271
	272	SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
	273
	274	DECLARE db_cursor CURSOR FOR
	275	SELECT name
	276	FROM sys.databases
	277	WHERE name LIKE 'dba%'
	278
	279	OPEN db_cursor
	280	FETCH NEXT FROM db_cursor INTO @DatabaseName
	281
	282	WHILE @@FETCH_STATUS = 0
	283	BEGIN
	284
	285	PRINT 'Backing up database: ' + @DatabaseName
	286
	287	EXECUTE dba.dbo.DatabaseBackup
	288	@Databases = @DatabaseName,
	289	@URL = @BackupContainerURL,
	290	@BackupType = 'Full',
	291	@CopyOnly = 'Y',
	292	@Compress = 'Y',
	293	@Verify = 'N';
	294
	295	FETCH NEXT FROM db_cursor INTO @DatabaseName
	296	END
	297
	298	CLOSE db_cursor
	299	DEALLOCATE db_cursor
	300
	301	{{/code}}
	302
	303
	304
	305
9.1	306	=== 2. Set Up a New Job in SQL Server Agent ===
8.1	307
9.1	308	To automate the backup process of the DBA databases, follow the steps below to create a new job in SQL Server Agent.
8.1	309
9.1	310	----
8.1	311
9.1	312	====== Step 1: Create a New Job ======
8.1	313
9.1	314	1. Open SQL Server Management Studio (SSMS):
	315	1. In the Object Explorer, expand the SQL Server Agent* node.
	316	1. Right-click on Jobs* and select New Job.
8.1	317
9.1	318	----
8.1	319
9.1	320	====== Step 2: Define Job Properties ======
8.1	321
9.1	322	In the New Job window, configure the job properties:
8.1	323
9.1	324	* General Tab:
	325	Job Name**: Enter a descriptive name for the job, such as DBA Databases Backup.
	326	Owner**: Specify the job owner (ebssqladmin).
	327	Category**: Select Database Maintenance as the category for the job.
	328	Description**: Provide a brief description of the job (Automated backup of DBA databases to Azure Storage").
8.1	329
9.1	330	----
8.1	331
9.1	332	====== Step 3: Create the Job Steps ======
8.1	333
9.1	334	The job will perform the backup through Transact-SQL (T-SQL) commands. Set up the following steps:
8.1	335
9.1	336	1. Step Name: Enter a descriptive step name, such as Backup Only DBA Databases.
	337	1. Type: Select Transact-SQL Script (T-SQL).
	338	1. Database: Choose the master database, as the script will be run in the context of the master database.
	339	1. Command: Paste the backup script you created earlier, which automates the backup of the DBA databases.
8.1	340
9.1	341	----
8.1	342
9.1	343	====== Step 4: Set the Job Schedule ======
8.1	344
9.1	345	Now, configure the schedule for the job to run daily at 3:00 AM:
8.1	346
9.1	347	1. Schedule Name: Name the schedule (DBA Database Backup).
	348	1. Schedule Type: Choose Recurring for a regular schedule.
	349	1. (((
	350	Frequency:
8.1	351
9.1	352	* Occurs: Select Daily.
	353	* Recurs Every: Set it to 1 day to run the job every day at the same time.
	354	)))
	355	1. (((
	356	Daily Frequency:
8.1	357
9.1	358	* Set the time for the backup to start, such as 3:00 AM.
	359	)))
8.1	360
9.1	361	----
8.1	362
9.1	363	=== Conclusion ===
8.1	364
9.1	365	The SQL Server Agent job is now configured to automatically back up the DBA databases daily to an Azure Storage Account. The job will only back up databases that start with 'dba', and it handles encrypted databases with Transparent Data Encryption (TDE). This ensures secure, encrypted backups are stored in the cloud without requiring manual intervention.
8.1	366
9.1	367	=== ===
8.1	368
9.1	369	===== Note: We could use the job activity monitor to see if the job executed successfully. =====
8.1	370
	371
	372
	373
	374
9.1	375
	376
	377
	378
	379
	380
	381
3.2	382	1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
	383	GO
3.1	384
3.2	385	CREATE CERTIFICATE TDE_Certificate
	386	WITH SUBJECT = 'TDE Certificate';
	387	GO
3.1	388
3.2	389	2 USE master;
	390	GO
3.1	391
5.1	392	SELECT
3.2	393	cert.name AS Certificate_Name,
	394	cert.subject AS Certificate_Subject,
	395	cert.issuer_name AS Issuer_Name,
	396	cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
5.1	397	FROM
3.2	398	sys.certificates cert
5.1	399	WHERE
3.2	400	cert.name LIKE 'TDE%';
3.1	401
3.2	402
5.1	403	3 BACKUP CERTIFICATE TDE_Certificate
3.2	404	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'
	405	WITH PRIVATE KEY (
	406	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',
	407	ENCRYPTION BY PASSWORD = 'AnotherStrongPasswordHere!'
	408	);
	409	GO
	410
	411
	412	USE Normal;
	413	GO
	414
	415	-- 6. Create a Database Encryption Key (DEK) and encrypt it with the TDE certificate
	416	CREATE DATABASE ENCRYPTION KEY
	417	WITH ALGORITHM = AES_256
	418	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
5.1	419	GO--
3.2	420
	421	USE [dba]
	422	GO
	423
	424	CREATE DATABASE ENCRYPTION KEY
	425	WITH ALGORITHM = AES_256
	426	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	427	GO
	428
	429
	430	USE [dba1]
	431	GO
	432
	433	CREATE DATABASE ENCRYPTION KEY
	434	WITH ALGORITHM = AES_256
	435	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	436	GO
	437
	438
	439	USE [dba2]
	440	GO
	441
	442	CREATE DATABASE ENCRYPTION KEY
	443	WITH ALGORITHM = AES_256
	444	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	445	GO
	446
	447
	448	USE [dba3]
	449	GO
	450
	451	CREATE DATABASE ENCRYPTION KEY
	452	WITH ALGORITHM = AES_256
	453	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	454	GO
	455
	456	USE [xwiki]
	457	GO
	458
	459	CREATE DATABASE ENCRYPTION KEY
	460	WITH ALGORITHM = AES_256
	461	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	462	GO
	463
	464
	465	-- 7. Enable Transparent Data Encryption (TDE) on the database
	466	ALTER DATABASE dba
	467	SET ENCRYPTION ON;
5.1	468	GO--
3.2	469
	470
	471	select name, database_id, state_desc
	472	from sys.databases
	473
	474
5.1	475	SELECT
3.2	476	database_id,
	477	key_algorithm,
	478	key_length,
	479	encryption_state_desc
5.1	480	encryptor_type
3.2	481
5.1	482	FROM
3.2	483	sys.dm_database_encryption_keys;
	484
	485
	486	Select * from sys.dm_database_encryption_keys
	487
	488
5.1	489	BACKUP DATABASE [dba2]
	490	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	491	With copy_only
	492	GO
	493
	494
	495	BACKUP DATABASE [dba2]
5.1	496	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']],
7.1	497	\\ COPY_ONLY, -- Ensures the backup does not affect the regular backup chain
5.1	498	COMPRESSION, -- Optional: Compresses the backup to save storage space
3.2	499	STATS = 10 -- Optional: Provides backup progress status
5.1	500	GO--
3.2	501
	502
	503
	504	-- Step 1: Drop the existing credential (if needed)
	505	DROP CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups];
5.1	506	GO--
3.2	507
	508	-- Step 2: Create a new credential with the SAS token
	509	CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
	510	WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
	511	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	512	GO--
3.2	513
	514	-- Step 3: Perform the database backup with the provided parameters
	515	EXECUTE dba.dbo.DatabaseBackup
5.1	516	@Databases = 'dba', -- Replace with your database name
	517	@URL = '[[https:~~/~~/zagpebslab.blob.core.windows.net/sql-backups'>>https://zagpebslab.blob.core.windows.net/sql-backups']], -- Azure Blob Storage URL
	518	@BackupType = 'Full', -- Full backup
3.2	519	@CopyOnly = 'Y', -- Copy-only backup to avoid breaking backup chain
5.1	520	@Compress = 'Y', -- Compress the backup
3.2	521	@Verify = 'N'; -- No verification of backup
5.1	522	GO--
3.2	523
	524
	525
	526
	527
	528	select name, database_id, state_desc
	529	from sys.databases
	530
	531
5.1	532	SELECT
3.2	533	database_id,
	534	key_algorithm,
	535	key_length,
	536	encryption_state_desc
5.1	537	encryptor_type
3.2	538
5.1	539	FROM
3.2	540	select * from sys.dm_database_encryption_keys;
	541
	542
	543	select name, is_encrypted from sys.databases
	544
	545
5.1	546	SELECT
3.2	547	cert.name AS Certificate_Name,
	548	cert.subject AS Certificate_Subject,
	549	cert.issuer_name AS Issuer_Name,
	550	cert.pvt_key_encryption_type AS Private_Key_Encryption_Type
5.1	551	FROM
3.2	552	sys.certificates cert
5.1	553	WHERE
3.2	554	cert.name LIKE 'TDE%';
	555
5.1	556	ALTER DATABASE dba1
3.2	557	SET ENCRYPTION off;
	558	GO
	559
	560
	561	Use dba1;
	562	DROP DATABASE ENCRYPTION KEY;
	563
	564
	565	USE [dba1]
	566	GO
	567
	568	CREATE DATABASE ENCRYPTION KEY
	569	WITH ALGORITHM = AES_256
	570	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
	571	GO
	572
	573
	574
	575	ALTER DATABASE dba1
	576	SET ENCRYPTION ON
	577
	578
	579
	580
	581
	582
	583
	584
	585
	586
	587
	588
	589
	590
	591
	592
	593
	594
	595
	596
7.1	597	{{code language="sql"}}
	598	USE master;
	599	GO
	600	CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
	601	GO
3.2	602
7.1	603	CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
	604	GO
3.2	605
7.1	606	BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
	607	WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
	608	ENCRYPTION BY PASSWORD='*****')
3.2	609
7.1	610	USE Everest_TDE_Master;
	611	GO
	612	CREATE DATABASE ENCRYPTION KEY
	613	WITH ALGORITHM = AES_256
	614	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	615	GO
	616	ALTER DATABASE Everest_TDE_Master
	617	SET ENCRYPTION ON;
	618	GO
3.2	619
7.1	620	USE Everest_TDE_Master_Documents;
	621	GO
	622	CREATE DATABASE ENCRYPTION KEY
	623	WITH ALGORITHM = AES_256
	624	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	625	GO
	626	ALTER DATABASE Everest_TDE_Master_Documents
	627	SET ENCRYPTION ON;
	628	GO
	629	{{/code}}
3.2	630
	631
	632
	633
	634
	635
	636
	637
	638
7.1	639
	640
	641
	642
2.1	643	{{code language="sql"}}
	644	USE master;
	645	GO
	646	CREATE MASTER KEY ENCRYPTION BY PASSWORD = '******';
	647	GO
	648
	649	CREATE CERTIFICATE EBSphere_TDE_SQL2019_Cert WITH SUBJECT = 'Database_Encryption';
	650	GO
	651
	652	BACKUP CERTIFICATE EBSphere_TDE_SQL2019_Cert TO FILE = 'D:\temp\EBSphere_TDE_SQL2019_Cert'
	653	WITH PRIVATE KEY (file = 'D:\temp\EBSphere_TDE_SQL2019_Cert_Key.pvk',
	654	ENCRYPTION BY PASSWORD='*****')
	655
	656	USE Everest_TDE_Master;
	657	GO
	658	CREATE DATABASE ENCRYPTION KEY
	659	WITH ALGORITHM = AES_256
	660	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	661	GO
	662	ALTER DATABASE Everest_TDE_Master
	663	SET ENCRYPTION ON;
	664	GO
	665
	666	USE Everest_TDE_Master_Documents;
	667	GO
	668	CREATE DATABASE ENCRYPTION KEY
	669	WITH ALGORITHM = AES_256
	670	ENCRYPTION BY SERVER CERTIFICATE EBSphere_TDE_SQL2019_Cert;
	671	GO
	672	ALTER DATABASE Everest_TDE_Master_Documents
	673	SET ENCRYPTION ON;
	674	GO
	675	{{/code}}
5.1	676
	677
	678

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?