TDE Table Data Encryption

Question

Post reply

TDE Table Data Encryption

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 8, 2015 at 12:25 pm

#298800

I'm having problems with the following code:
--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO
CREATE DATABASE mssqltips_tde
GO
USE mssqltips_tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
-------------------------------------------------------------------------------------------
-- Set Encryption off
ALTER DATABASE mssqltips_tde SET ENCRYPTION
Msg 33108, Level 16, State 1, Line 1
Cannot disable database encryption because it is already disabled.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
What am I missing?
What do I have to do if I get in a situation where I need to back out and start over?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not sure, but it looks like you never turned encryption on using ALTER DATABASE ... SET ENCRYPTION ON;

https://msdn.microsoft.com/en-us/library/bb522682.aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I included the script for testing.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Welsh Corgi (6/8/2015)
I included the script for testing.
Thanks.

Yes, I see that. I am not going to test your script on my laptop (now if I had a dedicated VM to work with I might). And as I said, I don't see an ALTER DATABASE ... SET ENCRYPTION ON in your script. Do you:

Welsh Corgi (6/8/2015)
I'm having problems with the following code:
--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO
CREATE DATABASE mssqltips_tde
GO
USE mssqltips_tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
-------------------------------------------------------------------------------------------
-- Set Encryption off
ALTER DATABASE mssqltips_tde SET ENCRYPTION
Msg 33108, Level 16, State 1, Line 1
Cannot disable database encryption because it is already disabled.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
What am I missing?
What do I have to do if I get in a situation where I need to back out and start over?

jasona.work SSC Guru Points: 50217 More actions · Answer 4

Lynn is correct, there's no "Alter database blah set encryption on" command.

Just creating the DEK does not encrypt the database, you have to explicitly enable it.

I'd be willing to bet that the encryption_state value when you run

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length

FROM sys.dm_database_encryption_keys

is not = 3, but is instead = 1

https://msdn.microsoft.com/en-us/library/bb677274.aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt01!@'

CREATE CERTIFICATE CERT05 WITH SUBJECT = 'Cert4u12!@'

USE Adventureworks2012

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256 -- Error

ENCRYPTION BY SERVER CERTIFICATE TDECert

USE MASTER

ALTER Database Adventureworks2012 SET ENCRYPTION ON

SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length

FROM sys.dm_database_encryption_keys

-- SELECT * FROM sys.certificates

-------------------------------------------------------------------------------------------

-- Set Encryption off

ALTER DATABASE Adventureworks2012 SET ENCRYPTION OFF

-- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.

-- If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you

-- will not be able to open the database.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Welsh Corgi (6/8/2015)
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt01!@'
CREATE CERTIFICATE CERT05 WITH SUBJECT = 'Cert4u12!@'
USE Adventureworks2012
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 -- Error
ENCRYPTION BY SERVER CERTIFICATE TDECert
USE MASTER
ALTER Database Adventureworks2012 SET ENCRYPTION ON
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
-- SELECT * FROM sys.certificates
-------------------------------------------------------------------------------------------
-- Set Encryption off
ALTER DATABASE Adventureworks2012 SET ENCRYPTION OFF
-- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.
-- If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you
-- will not be able to open the database.

And ??? Is there a question here?

Joie Andrew One Orange Chip Points: 27361 More actions · Answer 7

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt01!@'
CREATE CERTIFICATE CERT05 WITH SUBJECT = 'Cert4u12!@'
USE Adventureworks2012
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 -- Error
ENCRYPTION BY SERVER CERTIFICATE TDECert
USE MASTER
ALTER Database Adventureworks2012 SET ENCRYPTION ON
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
-- SELECT * FROM sys.certificates
-------------------------------------------------------------------------------------------
-- Set Encryption off
ALTER DATABASE Adventureworks2012 SET ENCRYPTION OFF

Not sure if this last script resulted in an error for you, but it does not look like it would work anyway. When you create your DEK you reference the certificate "TDECert", but the certificate in your script is "Cert05". Unless you left the master key and old cert in the master database I am not sure that statement would succeed.

When you run the statements individually, where is it failing?

Joie Andrew
"Since 1982"

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

It works.

I had to go and accidently clicked too soon.

Lynn Pettis (6/8/2015)
Welsh Corgi (6/8/2015)
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Encrypt01!@'
CREATE CERTIFICATE CERT05 WITH SUBJECT = 'Cert4u12!@'
USE Adventureworks2012
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 -- Error
ENCRYPTION BY SERVER CERTIFICATE TDECert
USE MASTER
ALTER Database Adventureworks2012 SET ENCRYPTION ON
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
-- SELECT * FROM sys.certificates
-------------------------------------------------------------------------------------------
-- Set Encryption off
ALTER DATABASE Adventureworks2012 SET ENCRYPTION OFF
-- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.
-- If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you
-- will not be able to open the database.
And ??? Is there a question here?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Perry Whittle SSC Guru Points: 234064 More actions · Answer 9

TDE does not enable Table Data Encryption, it enables Transparent Database Encryption.

Do you understand what it does and why you would enable it would be my initial question?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Perry Whittle (6/9/2015)
TDE does not enable Table Data Encryption, it enables Transparent Database Encryption.
Do you understand what it does and why you would enable it would be my initial question?

Yes I got the part where it enables Encryption at the Database level.

It needs to be enabled to prevent sensitive from being displayed?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Perry Whittle SSC Guru Points: 234064 More actions · Answer 11

Welsh Corgi (6/9/2015)
It needs to be enabled to prevent sensitive from being displayed?
Thanks.

Boooom and that's NOT what TDE does, it does not stop someone from querying the database and reading data

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

Welsh Corgi (6/9/2015)
It needs to be enabled to prevent sensitive from being displayed?

Nope, it does not do that.

Column encryption is what prevents someone unauthorised from querying data.

Why are you using it? Do you understand the impact? Do you understand what it means for backup and restore?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

There are a lot of negative as backup and restore.Thanks for setting me straight.

I used encryption to encrypt a data element. and that went pretty smooth.

I did not want to do Database encryption.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Perry Whittle SSC Guru Points: 234064 More actions · Answer 14

GilaMonster (6/9/2015)
Why are you using it? Do you understand the impact? Do you understand what it means for backup and restore?

Already asked and had this response

Welsh Corgi (6/9/2015)
Perry Whittle (6/9/2015)
TDE does not enable Table Data Encryption, it enables Transparent Database Encryption.
Do you understand what it does and why you would enable it would be my initial question?
Yes I got the part where it enables Encryption at the Database level.
It needs to be enabled to prevent sensitive from being displayed?
Thanks.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉