Wiki source code of sql-tde

Version 9.1 by Nikhil Singh on 2025/02/28 13:57

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

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?