Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [transparent-data-encryption]

Transparent Data Encryption, a feature used to provide protection for data at rest.

39
votes
2answers
18k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
14
votes
2answers
33k views

Query to List Encryption Certificate for Databases

What certificate is being used to encrypt each of the databases on the instances. I can get the data using the following but how do I write the queries USE master GO -- this provides the list of ...
13
votes
1answer
510 views

Backup Compression Causing Corruption In SQL 2017 TDE Database

On SQL Server 2017 (CU3), whenever I enable backup compression on one of my TDE databases, the backup process always corrupts a specific page in the database. If I run the backup without compression, ...
13
votes
3answers
2k views

When do I need to backup the Service Master Key?

I'm reading some documentation and whitepaper about Transparent Data Encryption. Some documentation mention to backup the Service Master Key as well (For clarification I'm not talking about the ...
10
votes
3answers
6k views

Can I recover a TDE certificate by restoring the MASTER database?

(We are, fortunately, not currently in this situation, just planning ahead to see what our options would be if it ever occurred.) For a database encrypted with Transparent Date Encryption (TDE), a ...
10
votes
3answers
677 views

Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used

Update: @AmitBanerjee - Senior Program Manager for the Microsoft SQL Server Product Group confirmed that MS will look into the issue as it is a defect. Has anyone encountered issue restoring backups ...
10
votes
2answers
951 views

TDE prep: key/certificate backup for restores

I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate ...
9
votes
2answers
335 views

Is network traffic encrypted when writing remote backups using SQL Server TDE?

They do say there is no such thing as a “stupid question”, so here goes: I understand that SQL Server Transparent Data Encryption (TDE) encrypts data at rest, so that your database files (.mdf) and ...
8
votes
3answers
7k views

Alternatives to SQL Server TDE

Due to the high cost of SQL Server Enterprise Edition which includes the Transparent Data Encryption feature I am looking for an alternative product and have only found a couple of options: DbDefence ...
7
votes
1answer
2k views

BACKUP MASTER KEY failing with cannot find symmetric master key because it does not exist

I'm trying to backup the master key for a TDE database, but SQL Server says there isn't one. It's a bit weird, but I'm sure I'm just doing something wrong. I'm sysadmin on the server, so I should be ...
7
votes
1answer
5k views

Set “Encryption Enabled” to FALSE…what can go wrong?

I'm migrating a database from SQL Server 2012 to SQL Server 2014 on the same server. I'm configuring LOG SHIPPING on a database that is apparently encrypted: When setting the log shipping I got this ...
7
votes
2answers
2k views

SharePoint 2013 Database encryption

I'm using SharePoint 2013 with SQL Server 2012 as persistence layer. I need to encrypt the data stored in the database. I stumbled upon the SQL Server feature Transparent Data Encryption (TDE). What ...
7
votes
3answers
1k views

Enabling TDE without breaking existing mirror

I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended ...
6
votes
1answer
2k views

TDE - Encryption on a large database

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server. What kind of problems (if ...
6
votes
1answer
287 views

TDE Master Key Rotation

Does changing the TDE Master Key (DB Master Key and/or the DB encryption key) always require decryption and re-encryption? If not, at what version did SQL Server begin to allow you to change the ...
6
votes
2answers
2k views

TDE on replicated databases

I have implemented Master-Slave replication between two databases. Now I have to implement Transparent Data Encryption (TDE) on the master database. Is it possible to implement TDE in the master ...
6
votes
1answer
2k views

Enable TDE for existing AlwaysOn databases

I need to do a little benchmarking of AlwaysOn, both with and without TDE enabled. All the instructions I see cover adding encrypted databases to an availability group, but I see no mention of ...
5
votes
2answers
294 views

Best practice around non-production databases refreshes and TDE

Say we have a production system with several databases encrypted using TDE and a self-generated certificate. We regularly need to take this database and refresh our non-production systems. This ...
5
votes
1answer
117 views

Swapping TDE Cert Slow, any way to improve performance?

Fair warning, I am very new to encryption with DBs. Right now we use the following statement to swap certificates when in the process backing/restoring DBs between servers: ALTER DATABASE ENCRYPTION ...
5
votes
2answers
540 views

Removing TDE from server

I have removed Transparent Data Encryption (TDE) from my server, dropped the key and switched the databases to Simple, shrunk the log and then back to Full. The LOG backups are now smaller as well as ...
5
votes
1answer
521 views

Moving TDE database to a new database but having problems with the cert

This is my first time working with TDE so hopefully I haven't done anything horribly wrong. I'm trying to move my TDE database from one server to another. I have created a master key on the master ...
5
votes
2answers
352 views

Separation of Duties - Can a DBA patch or Upgrade a SQL Server 2008 R2 DB without TDE Encryption keys?

Are there any considerations I should have in mind when I patch or upgrade a TDE encrypted database? In the context of separation of duties, does the person executing the patch or upgrade process need ...
5
votes
2answers
120 views

Database Encryption scan for database <name> was aborted. Reissue ALTER DB to resume the scan

I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is ...
4
votes
1answer
1k views

Should you encrypt the system databases?

A finding from a STIG evaluation, CCE-19832-5 reports that the [master] database should be encrypted with a Database Master Key using an encrypted password that meets DoD standards. This a result of ...
4
votes
1answer
896 views

TDE Encryption on database tripled size of backup files

After enabling TDE Encryption my database backup file .bak is 3x larger than before. Is there a way to improve this? Is this due to compression not being as efficient on an encrypted file?
4
votes
1answer
702 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
4
votes
1answer
395 views

Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers. Our backups are taken from ...
4
votes
3answers
4k views

Open source & cheap 'data at rest' encryption solutions

So I am exploring some options about database encryption. The best options are commercial (TDE). I am looking for an open-source implementation. Recent releases of MySQL and MariaDB have data-at-rest ...
4
votes
1answer
202 views

TDE restored DB encryption state

I've been looking into using TDE in our SQL Server 2014 environment and have a few questions. I think I have a fairly solid grasp on the numerous "how-to's" that are out there on initially ...
3
votes
1answer
2k views

Microsoft SQL Server 2016 Service Pack 1 (SP1) support for TDE (transparent data encryption)

Does Microsoft SQL Server 2016 Service Pack 1 add support for TDE (Transparent Data Encryption) to Standard Edition as well? As per What's new in Database Engine - SQL Server 2016 and A Big Deal : ...
3
votes
1answer
1k views

TDE cannot encrypt database

In order to encrypt a SQL db, I run the following commands: CREATE CERTIFICATE <certname> ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>' WITH SUBJECT = 'certificate subject', ...
3
votes
2answers
2k views

Replacing SQL Server TDE soon expiring certificate

What is the best way to replace a soon expiring certificate? Downtime should be avoided. Databases size is 100GB.
3
votes
1answer
952 views

SQL Server 2012 Transparent Data Encryption and indexes

Does SQL Server TDE allow a query like this: select * from Patient where lastname = 'Smith' to occur without a full-table scan if the lastname column is indexed?
3
votes
2answers
42 views

I only have backup of a TDE encrypted SQL Server database--can it be restored it to another server?

My situation is little weird--I have no access to the source database server, but I do have the database backups of a database that is encrypted with TDE. When I restore it to another server, I am ...
3
votes
1answer
541 views

Backup File Size Almost 50% of Database File Size with TDE Enabled

I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse. Fully restored, the database is around 707GB. ...
3
votes
1answer
1k views

TDE in a SQL Cluster

I'm researchig into databnase encryption and whilst TDE seems to do exactly what it says on the tin, there seems to be no information on how to configure it up in a Microsoft failover SQL Cluster, or ...
3
votes
1answer
302 views

TDE enabled database backup taking too long

We have recently implemented TDE and backup compression is also enabled. Before TDE backup used to take about 20min but now it takes about one hour. I have set @MaxTransferSize to 3MB, 2MB, 1MB and ...
2
votes
1answer
171 views

TDE Performance and Cost Threshold for Parallelism

Is there any documentation, test data, or other evidence to suggest that the Cost Threshold for Parallelism (CTP) setting might impact performance when TDE is enabled on a database? Without going ...
2
votes
2answers
2k views

Backup and restore of TDE Databases to different server

There seems to be a lot of information on backing up and restoring TDE databases, but it's generally all the same information, and there seems to be some contradiction about certain points. I have ...
2
votes
1answer
495 views

Encrypt / Decrypt String

I've been approached with a requirement. One of our devs wants to be able to store information in the database, but once there it needs to be encrypted, so that if anyone ever gets a copy of the ...
2
votes
3answers
526 views

What do I need to restore an encrypted MSSQL database?

I am trying to get my head around MSSQL TDE encryption. I have followed these steps to encrypt a database: 1) Create a master key on the master database: USE master GO CREATE MASTER KEY ENCRYPTION ...
2
votes
1answer
429 views

TDE Change Encryption Key - Is it safe?

We use SQL Server 2012 Enterprise Edition and Transparent Data Encryption (TDE). Microsoft have a command to let you change the encryption on the database "ALTER DATABASE ENCRYPTION KEY". The docs ...
2
votes
3answers
235 views

Deploying transparent data encryption

I have an installation of SQL 2016 Enterprise and my boss want to deploy TDE. I googled online and all the instruction said about "you can use a self sign certificate for test/dev purpose". Most ...
2
votes
1answer
214 views

Microsoft TDE Permissions For Changing Keys

I am setting up roles as a layer of protection inside of MSSQL. One of the roles will only have the ability to change database encryption keys for TDE on the database server. The server will be using ...
2
votes
0answers
36 views

TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2

As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted. I have done TDE in SQL Server 2016, and when I check through SELECT ...
2
votes
0answers
46 views

Mirrored TDE DB Performance

Performing a large index reorg on a mirrored SQL Server 2014 TDE database creates a large redo log on the mirror and the mirroring monitor displays an infinite restore time. The exact same process ...
2
votes
2answers
59 views

Trying to read a log file encrypted by TDE

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 ...
2
votes
0answers
161 views

Alwayson Database Recovery Failure during TDE encryption

Last week I enabled TDE encryption on my AlwaysOn Availability Group databases of SQL Server 2012. During the encryption of one of the larger databases, bigDB (~200gb) a failover event occurred. Of ...
2
votes
0answers
3k views

SQL Server TDE Stuck at Encryption_State 2 and 0% on a Tiny DB

I'm having a heck of a time with this issue and can't figure out what is wrong. I'm not sure how long this tiny DB has been in 'encryption_state = 2' from query: SELECT * FROM sys....
1
vote
1answer
475 views

Can we use TDE as an anti-injection technique

TDE prevent unauthorized access to the data by restoring the files to another server, because it requires the original encryption certificate and master key. If i applied a table level encryption ...