I have a quick question relating to Oracle TDE. Could somebody please explain why both of the following pairs of commands appear to work when opening/closing an ecryption wallet? Is the wallet password actually needed for this or not? If not, when exactly do we need to use the password? Many thanks. administer key management set keystore close identified by ""; administer key management set keystore open identified by ""; administer key management set keystore close identified by "null"; administer key management set keystore open identified by "null";

Databases on Azure Managed Instance are encrypted by default. And these dbs have service-managed Transparent Data Encryption (TDE). One of our databases is a restored database from a .bak file, and hence its *Encryption Enabled* property was set to False. We want this database to be encrypted, as well. Can we enable this database just by setting *Encryption Enabled* to True?. Will it cause any issues? Are there any other steps that need to be performed to encrypt this database? We heard you have to create a Master Key etc. but for all our other databases that have service-managed TDE, we don't see any Master Key.

What are the possible ways to Clone a PDB from one CDB into another CDB (in a different host) with TDE enabled at both source and target. At present i am able to think of the following two a) Data pump export and import b) Unplugging in source and plugging into target Option a) requires creation of tablespaces manually before import at target pdb and is quite slow since the data we have is huge. Option b) is easy without TDE but with TDE their is a concept of exporting and importing encryption keys , and what ever i have used it till now it becomes quite complex during plugging and importing keys . Can someone suggest and alternative way to achieve the mentioned requirement or may be a way to do a) or b) with more ease and accuracy .

We are about to move away from SQL 2008R2 to a much newer version. We upgraded to the Enterprise Edition of 2008R2 just for TDE, but I was just browsing here: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver15 and it appears to say that SS2019 includes TDE in the Standard Edition (the 2017 and older pages confirm it’s Enterprise Edition only). Is this a typo or did I miss the memo and my life just got a LOT cheaper?

I recently migrated a SQL Server 2017 TDE database to SQL Server 2019 CU4. I was browsing the Error Log and noticed intermittent messages about An error occurred while processing log encryption. The process was recovered automatically. No user action is required. These messages are always preceded by Error: 1222, Severity: 16, State: 55. And Lock request time out period exceeded. I found [FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server](https://support.microsoft.com/en-us/help/3197631) , where it discusses a fix that has been back-ported to previous versions of SQL Server. From that post > Assume that you have a Transparent Data Encryption (TDE) encrypted > database on an instance of Microsoft SQL Server. When you restart the > instance of SQL Server, you may receive a lock time-out error that > resembles the following: Starting up database . Error: > 1222, Severity: 16, State: 55. Lock request time out period exceeded. > Error: 9016, Severity: 21, State: 7. An error occurred while > processing the log for database . The log block could > not be decrypted. Then, the database will go in a suspect state.You > can get the database back online by using Emergency Mode. You won't > encounter corruption. I'm not in the situation where my instance is being restarted, but it seems this _fix_ is similar to what I am experiencing. While I appreciate the process being automatically recovered, I'm concerned why the error is even showing up in the error log. I also have an almost identical instance in the same migration scenario from SQL Server 2017 to 2019 that is NOT experiencing these errors.

When a virtual machine with a SQL Server database that uses TDE is copied or cloned, will the database be readable on the new instance?

I am trying to restore a certificate but it gives me above error message. The database is not being stored it was part of TDE encryption, but that was removed earlier. Now how to handle this ?. while restoring database gives this error message.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'PrismBT' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint '0x3A4813BD6A61D443D620E43014F46F78F593EB54'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17285.0+((SSMS_Rel_17_4).180821-0238)&LinkId=20476 

when I try to restore the certificate using old certificate and private key , I get the above error message. What to do now.? I used below commands in new server

create master key encryption by password = 'password'


Use master
go

create certificate GCRDB1_TDE_Cert 
from File = 'E:\script\GCRDB1_certificate.cer'
with private key (File = 'e:\script\GCRDB1_PKey.pvk',encryption by password = 'password')

We have a handful of MSSQL databases encrypted using Transparent Data Encryption (TDE) without an HSM. So the current chain is:

Service Master Key (SMK) ->
Master Key (DMK) ->
Certificate encrypted by DMK ->
User Database - Data Encryption Key (DEK)

The databases are encrypted using the same certificate. Is there a possible migration path to utilizing an HSM without having to re-encrypt all of the databases?
(there is around 100TB of data, so I am trying to avoid re-encryption if at all possible)

I am trying to work with MariaDB and the file key management encryption plugin. From the documentation at it seems like the key file and the encryption key(for the key file) are stored locally on the PC. This is not working in my case since this is seen as a security risk. The system is quite limited (local PC with Win10 not connected to the internet). Having the keys on an USB stick is not an option. The keys have to be hidden to any user(including admins) so restricting the rights also is not an option. From what I've seen the only solution that I came up with is to create the key files, start the MariaDB server, and delete them. Is there a way to get the key information from in memory files? In case that this is not possible is the hashicorp plugin a good alternative to secure the encryption keys (kind of hard to find good documentation for it) Thank you

I have a full chain of full backups and log backups. I am looking to investigate exactly what transaction was done by a certain user in a given period of time. The database (thus all backups) are encrypted by TDE. Tools such as ApexSQL Log does not work on TDE-encrypted data source. Is there any way I can analyze the log file to find out the information I want? I can run fn_dump_dblog which gives me some information, however I cannot retrieve the exact information on what was changed, such as what column was changed to what value.

We are trying to crossrestore a TDE encrypted Oracle Database 19.12 on a new ODA X8-2M. TDE wallet has been successfully backedup (odacli create-backup -in CDBET015 -c TDEWallet) and transferred to the new ODA to the filesystem (not ASM). [oracle@oda-host tdewallet]$ ls -ltr total 72 -rwxrwxrwx 1 oracle oinstall 341 Feb 25 13:23 logfile.log -rwxrwxrwx 1 oracle oinstall 5835 Feb 25 13:23 ewallet_202202241425400934_CDB.p12 Crossrestore with SBT-Tape was successful and RMAN completed the restore and the recovery. However the odacli register-database was not yet started. We tried to restore the wallet first, because it is not part of the rman crossrestore. odacli restore-tdewallet -in CDBET015 -tl /u01/NFS_TDE/backup/CDB/tdewallet/ewallet.p12 The restore of the TDE wallet fails because the new ODA does not know the new database. The ODA need to register the newly restored Database first. This fails because it does not have the wallet. odacli register-database -c OLTP -s odb1 -sn CDB -t SI –tp Enter SYS, SYSTEM and PDB Admin user password: Retype SYS, SYSTEM and PDB Admin user password: Enter TDE wallet password: Retype TDE wallet password: Job details: odacli describe-job -i "7e60dc6f-0c69-4bb8-8205-bee6f6276b19" Job details ---------------------------------------------------------------- ID: 7e60dc6f-0c69-4bb8-8205-bee6f6276b19 Description: Database service registration with db service name: CDBET015 Status: Failure Created: February 25, 2022 4:04:21 PM CET Message: DCS-10001:Internal error encountered: failed to open the tde password based wallet for database : CDB.ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN identified by ******** container=all * ERROR at line 1: ORA-28367: wallet does not exist Task Name Start Time End Time Status ---------------------------------------- ----------------------------------- ----------------------------------- ---------- database Service registration February 25, 2022 4:04:22 PM CET February 25, 2022 4:05:54 PM CET Failure database Service registration February 25, 2022 4:04:22 PM CET February 25, 2022 4:05:54 PM CET Failure TDE parameter validate at destination February 25, 2022 4:04:22 PM CET February 25, 2022 4:04:22 PM CET Success Enable OMF parameters February 25, 2022 4:04:23 PM CET February 25, 2022 4:04:23 PM CET Success Setting db character set February 25, 2022 4:04:23 PM CET February 25, 2022 4:04:24 PM CET Success Move Spfile to right location February 25, 2022 4:04:24 PM CET February 25, 2022 4:04:33 PM CET Success Enable DbSizing Template February 25, 2022 4:04:33 PM CET February 25, 2022 4:05:32 PM CET Success Copy Pwfile to Shared Storage February 25, 2022 4:05:32 PM CET February 25, 2022 4:05:39 PM CET Success Add Startup Trigger to Open all PDBS February 25, 2022 4:05:39 PM CET February 25, 2022 4:05:40 PM CET Success Running DataPatch February 25, 2022 4:05:40 PM CET February 25, 2022 4:05:53 PM CET Success configuring TDE February 25, 2022 4:05:53 PM CET February 25, 2022 4:05:54 PM CET Failure Opening wallet February 25, 2022 4:05:53 PM CET February 25, 2022 4:05:54 PM CET Failure How to make the wallet accessible to the database/ODA?

Is there any effect at the application end, if you configure TDE for Production databases, which are in both replication and Always On Availability Groups in SQL Server 2014 Enterprise Edition? This is a three node cluster with both replication and AGs, with another set of two nodes used for reporting purposes.

We are migrating a database with TDE enabled from a SQL 2016 SP3 machine to SQL 2022 CU17 machine. New Server Setup: to prepare the new server, I have restored the service master key and master database master key (with private key), as well as the TDE certificate stored in master. I ran into an issue very early on with the service master key, but was able to resolve it by having the new SQL service running as the same Windows AD account as the previous server. I can provide the scripts I used to do any/all of this setup, but didn't want to clutter the post too much When I restore the database to the new server, I am running:

USE [master]
GO

Open Master Key Decryption by password = '8675309'

RESTORE DATABASE mysecuredb FROM
DISK = '\\myshare\mysecuredbbackup.bak'
WITH REPLACE

USE [mysecuredb]

OPEN MASTER KEY DECRYPTION BY PASSWORD = '12345'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '54321'

Both commands succeed, so it appears that the key is accessible as expected after the restore. DBCC CHECKDB also comes up clean. When I restore the database, it runs the upgrade steps to 2022 and comes online, but when I run ALTER DATABASE mysecuredb SET ENCRYPTION ON I get the following set of errors >Info: Beginning database encryption scan for database 'mysecuredb'. >Error 15581: Please create a master key in the database or open the master key in the session before performing this operation. >Error 9001: The log for database 'mysecuredb' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database. The database does not become suspect as I would expect with corruption, but sys.dm_database_encryption_keys shows that the database is stuck in state 2 (Encryption in progress), with seemingly no way of continuing or reverting the encrypt action. It may be worth noting that the database also has cell encryption enabled for some columns, however I am able to decrypt that data using the SYM/ASYM keys after the restore (but while TDE is disabled). This is my first time interacting with TDE, as the previous owner of this infrastructure has sailed to greener pastures, but I am not sure what I am missing from the available documentation from MS. Any help is appreciated!

I am just asking here so that someone from Percona can check if possible. I have followed below documentation to configure pg_tde in RHEL 8 with PG version 16. https://percona.github.io/pg_tde/main/ After enabling and configuring pg_tde key provider and master key . pg_basebackup is getting failed with below error

[postgres@hostname postgres]$ pg_basebackup -D bkp_test -R -X stream -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/14000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1391250"
WARNING:  aborting backup due to backend exiting before pg_backup_stop was called
pg_basebackup: error: COPY stream ended before last file was finished
pg_basebackup: removing contents of data directory "bkp_test"

In the logs I can see below error.

2024-10-18 07:55:21.132 EDT  LOG:  checkpoint starting: force wait
2024-10-18 07:55:21.136 EDT  LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.001 s, total=0.004 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16384 kB, estimate=27000 kB; lsn=0/14000060, redo lsn=0/14000028
2024-10-18 07:55:21.205 EDT  WARNING:  aborting backup due to backend exiting before pg_backup_stop was called
2024-10-18 07:55:21.205 EDT  ERROR:  invalid segment number 0 in file "pg_tde.map"
2024-10-18 07:55:21.205 EDT  STATEMENT:  BASE_BACKUP ( LABEL 'pg_basebackup base backup',  PROGRESS,  WAIT 0,  MANIFEST 'yes',  TARGET 'client')
2024-10-18 07:55:21.221 EDT  LOG:  unexpected EOF on standby connection
2024-10-18 07:55:21.221 EDT  STATEMENT:  START_REPLICATION SLOT "pg_basebackup_1391250" 0/14000000 TIMELINE 1

After loading shared library pg_tde on server level and configuring master key for specific DB level, I can see below files in base directory that is creating issue. but unable to understand .

./16505/pg_tde.map
./16505/pg_tde.dat

below are the databases with OID.

test=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
     5 | postgres
     1 | template1
     4 | template0
 16505 | test
(4 rows)

Thanks for your help and suggestion if any.

I was wondering why sp_blitz is recommending to back up TDE certificates that has not been backed up the last 30 days? If the certificate hasn't been changed, and it has been backed up after creation, is there a need to do more backups? Should I do regular backups of the TDE certificates even if they have not changed since last backup?

I don't have a great means to test this due to limited access, but how is this action done? I only see one other stack article where someone did what I'd assume to be proper and just update the credential's secret using the same appid but a new secret.

I have already (by accident) regenerated the master key in the master database of a (development)server that hosts multiple databases encrypted with Transparent Data Encryption (TDE). Now, I am curious about the effects of this action on the existing certificates and the databases encrypted with those certificates. Specifically, I would like to know: 1. What impact does regenerating the master key have on the encryption status of the existing databases that were previously encrypted with TDE? 2. After regenerating the master key, what happens to the certificates used for TDE encryption? Do they need to be reconfigured or regenerated? Do I need to take a new backup of the certificates/private key, or will the existing files still function when restoring to other servers? 3. Are there any necessary precautions or steps to take after regenerating the master key to ensure the integrity and accessibility of the encrypted databases? I want to ensure that regenerating the master key has not caused any unintended consequences, such as rendering my encrypted databases inaccessible or compromising their security (as of now I can query them normally). Any insights or advice on this matter would be greatly appreciated. Thank you!

based on the picture below you can see a result of

* from sys.dm_exec_connections

nice dmv to show the current connections. now if you look at the detail,the databasae mirroring connections are not encrypted. question is: should it be cause of concern?

I have enabled TDE on a big DB of mine (mdf file:9.6GB, log file:12.6GB). I have followed the steps from this video (amongst other sources): https://www.youtube.com/watch?v=6EhCeLbAS_o It has been more than 1.5 hour, and when running the query to check on the status , the progress appears to be zero! SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = CASE encryption_state WHEN '0' THEN 'No database encryption key present, no encryption' WHEN '1' THEN 'Unencrypted' WHEN '2' THEN 'Encryption in progress' WHEN '3' THEN 'Encrypted' WHEN '4' THEN 'Key change in progress' WHEN '5' THEN 'Decryption in progress' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete, encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys I have checked the value of the 5004 trace flag (which is said to be able to pause the encryption and produce similar results ), and it is not set: DBCC TRACESTATUS (5004); This is the second time I try to enable encryption on the same restored backup, and on both times I got the same delay. So, I am beginning to wonder if I am missing something here. Does anyone have any ideas if this delay to see some progress is to be expected? If not, any ideas on what might be wrong? **UPDATE:** I still can't make this work. SQL Server aborts the encryption process without reporting the reason. I took a look here and didn't get much wiser. These are the steps I've used the last time, while trying to be extra careful :

-- I regenerate all keys, just in case:
		ALTER SERVICE MASTER KEY FORCE REGENERATE;
		GO
		OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass';
		GO
		ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'mypass';
		-- If it doesn't exist already: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypass';
		GO
		-- Restart SQL Server Service
		-- Restore Backup
		-- Change compatibility level for the newly created DB to 2017
		-- Restart SQL Server Service
		-- Create certificate:
		use master;
		GO
		OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypass';
		GO
		CREATE CERTIFICATE SECURED_DB_EncryptionCertificate WITH SUBJECT = 'SECURED_DB_EncryptionCertificate';
		-- 
		USE SECURED_DB;
		GO
		CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
		ENCRYPTION BY SERVER CERTIFICATE SECURED_DB_EncryptionCertificate;
		GO
		-- Backup keys and certificates.
		use master;
		go
		backup certificate SECURED_DB_EncryptionCertificate to file= 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SECURED_DB_EncryptionCertificate'
		with private key (file = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\SECURED_DB_EncryptionCertificateKey.pvk', encryption by password = 'mypass');
		go
		-- Avoid having active connections to DB
		USE master;
		-- Encrypt DB
		ALTER DATABASE SECURED_DB SET ENCRYPTION ON;

At this point, the SECURED_DB encryption gets aborted, and restarting the SQL Server service makes master lose its encryption and SECURED_DB gets into permanent recovery mode.

Nice to meet you all. This is the first time I have posted here, so please feel free to let me know if I am wrong. I am doing some research on the Transparent Data Encryption feature. I want to validate the ability to extract data without logging into the database instance and before encryption, by reading the .dbf file directly with HexEditor, Notepad++, or something equivalent. The problem is with the data type VARCHAR2; the value is plaintext, the same as the returns when I query in the instance. But with NUMBER, DATE, or anything that is not VARCHAR2, I cannot find plaintext in the .dbf *Datatypes of columns* *The returns when I query in the instance* *Hex Editor returns plaintext with VARCHAR2 datatype data…* *… but not with NUMBER datatype data* I did a lot of research on the Internet, but it seems there is no clear answer and no demo of the contents of data files before and after using TDE. Why is it like that? And more, I think that Oracle stores non-VARCHAR2 data types in binary format to save storage and VARCHAR2 data types for easily returning in query, am I correct? Thank you for any guidance! Vu Hoang.