How to check if the database itself is encrypted or only the backups ?

Question

How to check if the database itself is encrypted or only the backups ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
July 9, 2015 at 4:29 am

#319651

Hi All,
I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.
Does that mean that all those 4 DBs are encrypted ?
How to check if the database itself is encrypted or only the backups ?
Note :- I can see backups of key in a particular folder.
Thanks in advance.
San

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL 2008 doesn't have the ability to encrypt only backups, that was added in SQL 2014. is_encrypted in sys.databases indicates whether Transparent Database Encryption is enabled for the database

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Joy Smith San (7/9/2015)
Hi All,
I queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.
Does that mean that all those 4 DBs are encrypted ?
How to check if the database itself is encrypted or only the backups ?
Note :- I can see backups of key in a particular folder.
Thanks in advance.
San

The column you are referring to indicates whether TDE has been enabled, I'm assuming the backups you have found are Certificate backups which were used to enable TDE, can you provide more detail on these at all

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

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 3

Thanks Gila and Perry.

So since SQL 2008 doesn't have option to encrypt only DB its both the DB and Backups are encrypted I assume.

I can see following files saved in a folder.

master_key.dat

tdeCert

tdeCert_key

pvtkey.bak

tdecert.bak

I am not clear when will we need to use all these 🙂 .

Thanks again.

San.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Joy Smith San (7/9/2015)
So since SQL 2008 doesn't have option to encrypt only DB its both the DB and Backups are encrypted I assume.

I said 2008 doesn't have the ability to encrypt only the *backups*

The database will be encrypted and, as a result the backups will as well.

I can see following files saved in a folder.

master_key.dat
tdeCert
tdeCert_key
pvtkey.bak
tdecert.bak
I am not clear when will we need to use all these 🙂 .

You use them to restore the certificate to other servers as, without that certificate, you *cannot* restore or attach the encrypted database to any other server.

You may want to test out your restore process, and make sure that those certificate backups are in a safe place, as you do not want to have to tell business that, because you didn't, you cannot restore the database backup in a disaster.

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 5

Thanks for the clarification Gila.

I may try to restore the DBs in Test server. Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

You may want to do some reading on TDE first.

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Joy Smith San (7/9/2015)
Thanks Gila and Perry.

you're welcome

Joy Smith San (7/9/2015)
I can see following files saved in a folder.
master_key.dat
tdeCert
tdeCert_key
pvtkey.bak
tdecert.bak

Note: you do not need the database master key backup when restoring the database to a new server, just a restorable backup of the certificate.

What you have above is only part of the equation, do you have the passwords recorded that were used to encrypt the certificates private key when the backups were taken. A certificate backup is initiated using the following TSQL. So question is do you have the password recorded that was provided to the ENCRYPTION BY PASSWORD clause?

BACKUP CERTIFICATE [MyNewCert] TO

FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'

WITH PRIVATE KEY(

FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky',

ENCRYPTION BY PASSWORD = 'P@sswordt0encryptcertbackup')

Joy Smith San (7/9/2015)
I am not clear when will we need to use all these 🙂 .
Thanks again.
San.

You're going to need them if you ever restore the database to another server or if the certificate is ever lost from the current server.

IMPORTANT: If you do not have the password(s) recorded, take a fresh backup of the certificate(s) and record the password(s) securely. Remove any old cert backups as they'll be useless

Read my article at this link for more info on TDE

http://www.sqlservercentral.com/articles/Transparent+Data+Encryption+(TDE)/91712/[/url]

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

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Hi Perry,

I was able to create certificate and restore DB in my local PC as password is available.

Below are certificate backup created by some earlier DBA, but named wrongly.

master_key.dat
tdeCert
tdeCert_key

master_key.dat = is the ".pky" with password.

tdeCert = ".cert" bkp and the other one no idea.

I was able to create certificate from these backup with password in test and restored DB.

Below files are actual certificate backups handed over by user long ago.

pvtkey.bak
tdecert.bak

Since you said older backups are useless, I just executed below command in my test machine, with another old password.

create certificate tdeCertLocal

from file = 'D:\tdeCert.bak'

with private key (file='D:\pvtkey.bak', decryption by password = 'oldpwd')

It dint give any error, only "Warning: The certificate you created is expired."

If we are restoring in same server, we don't need any password as such is it and it will be like a normal DB restore is it ?.

Thanks a lot for the link.

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

Joy Smith San (7/13/2015)
Since you said older backups are useless

They'll be useless if you don't have passwords to restore them and take new backups is what I said 😉

Joy Smith San (7/13/2015)
It dint give any error, only "Warning: The certificate you created is expired."

What's the state of the current certificate has that expired. It looks as though an old expired cert has been replaced by a new one, but you'll need to confirm

Joy Smith San (7/13/2015)
If we are restoring in same server, we don't need any password as such is it and it will be like a normal DB restore is it ?.
Thanks a lot for the link.

not sure what you mean here

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

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

What's the state of the current certificate has that expired. It looks as though an old expired cert has been replaced by a new one, but you'll need to confirm

Expiry_date column in source server is of 2014 and when I created certificate in the destination server it took the same date for expiry and hence the message I believe.

As per https://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx] , expiration is not enforced when the certificate is used for encryption. DB got restored when I tried and hope its not an issue for restoring.

Joy Smith San (7/13/2015)
If we are restoring in same server, we don't need any password as such is it and it will be like a normal DB restore is it ?.

not sure what you mean here

Was just asking if we need to do anything with the certificate if we are restoring the database in the same instance where it was encrypted.

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

Joy Smith San (7/15/2015)
As per https://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx] , expiration is not enforced when the certificate is used for encryption.

Yes, that is correct an expired cert will not hinder the backup restore process.

Joy Smith San (7/15/2015)
Was just asking if we need to do anything with the certificate if we are restoring the database in the same instance where it was encrypted.

No, since the cert will already exist in the master database there's nothing to do

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

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 12

Took a fresh backup and documented.

Thanks a lot Perry/Gila for clarifying all the doubts.

Learned something new, thanks for your support.