Questions tagged [transparent-data-encryption]

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

Filter by
Sorted by
Tagged with
40
votes
2answers
19k 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 ...
16
votes
2answers
50k 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 ...
15
votes
3answers
3k 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 ...
13
votes
1answer
759 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, ...
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
1k 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
1k 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
406 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
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 ...
8
votes
3answers
9k 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 ...
8
votes
1answer
336 views

Internals of backup compression with TDE (SQL Server)

Does anyone have any (inside?) knowledge of how backup compression works with TDE on SQL Server (version 2016+). In general I would expect compression of encrypted data to have minimal benefit, ...
8
votes
1answer
4k 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
595 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 ...
7
votes
1answer
6k 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
2k 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
2answers
649 views

Turn TDE off when restoring SQL databases

We are just getting started with TDE. Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system. My question is ...
6
votes
2answers
3k views

TDE on replicated databases

I have implemented Transactional replication between two databases. Now I have to implement Transparent Data Encryption (TDE) on the Publisher 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
805 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
163 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
592 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
2k 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 ...
5
votes
1answer
550 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
373 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
478 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

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
60 views

Good practices about Data Encryption

I would like to know (better ideas/best practices...) what can I use to encrypt my data. I've used TDE in the past and I really don't like it. because if something happens with the certificate, and ...
4
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 ...
4
votes
1answer
744 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
475 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
5k 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
212 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
3k 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
2k 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
3k 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
2answers
454 views

SQL TDE and Certificate Handling Recommendations

I've seen the information on this post: But I don't think it answers this particular question/concern: I have multiple SQL servers with multiple databases. We want to use TDE on a few of them. ...
3
votes
1answer
4k views

How to change SQL Server master key password?

I created a master key, then a certificate and encrypted my database. CREATE MASTER KEY ENCRYPTION BY PASSWORD='A Very Strong Password'; GO CREATE CERTIFICATE foo WITH SUBJECT='Database Encryption ...
3
votes
1answer
1k 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
92 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
598 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
3answers
1k 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 ...
3
votes
1answer
496 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 ...
3
votes
0answers
4k 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....
2
votes
1answer
182 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
579 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
1answer
636 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
293 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
2answers
5k views

Protect SQL Server Express data file

I need to protect a Microsoft SQL Server 2008 data file (.mdf) against restoring/attaching on another machine. I'm aware of TDE or Transparent Data Encryption but unfortunately this option is not ...