May 23, 2017 at 5:33 pm
I am new to TDE. I would like to try TDE on my sand box server.
Also Like to try for restore a backup of a database to another server.
I don't have a second sandbox of SQL 2016 instance to try to restore the database.
But I do have a SQL box that used by other developers.
I would like to use that one.
I am wondering after trying, is it easy to remove TDE on that dev server without leaving any footprints of what I experimented about TDE, for I don't want to affect the other databases on the server?
Thanks
May 24, 2017 at 6:00 am
The Microsoft documentation will get you going. You can remove the master key and the certificate as well as the database encryption key, which are the things that allow you to turn encryption on. So yeah, you can turn it off. Just be cautious about your key management as outlined in the documentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2017 at 7:02 am
TDE encrypts the database, not the entire server. You must create a master key and a certificate on the development server in the process of restoring the encrypted database, those are server level objects, but you can drop them afterward. Unless you inadvertently restore on top of an existing development database, or drop an existing key or certificate, it is unlikely you will break anything in development while testing TDE.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 25, 2017 at 9:32 am
Grant Fritchey - Wednesday, May 24, 2017 6:00 AMThe Microsoft documentation will get you going. You can remove the master key and the certificate as well as the database encryption key, which are the things that allow you to turn encryption on. So yeah, you can turn it off. Just be cautious about your key management as outlined in the documentation.
Eric M Russell - Wednesday, May 24, 2017 7:02 AMTDE encrypts the database, not the entire server. You must create a master key and a certificate on the development server in the process of restoring the encrypted database, those are server level objects, but you can drop them afterward. Unless you inadvertently restore on top of an existing development database, or drop an existing key or certificate, it is unlikely you will break anything in development while testing TDE.
TDE affects the server level too. Once enabled it encrypts TempDB, when removing TDE totally you'll need to restart the instance to remove the encryption from TempDB
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 25, 2017 at 9:36 am
That is good to know, thank you!
May 25, 2017 at 1:21 pm
Perry Whittle - Thursday, May 25, 2017 9:32 AMTDE affects the server level too. Once enabled it encrypts TempDB, when removing TDE totally you'll need to restart the instance to remove the encryption from TempDB
The effect of TDE on TEMDB is transparent to other databases and processes on the server. I don't believe it's a requirement to restart the server after removing TDE; I've created and removed TDE while testing and didn't have to do anything special in regard to TEMPDB. Encrypting and decrypting a database is an asynchronous and entirely online operation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 26, 2017 at 4:51 am
Eric M Russell - Thursday, May 25, 2017 1:21 PMPerry Whittle - Thursday, May 25, 2017 9:32 AMTDE affects the server level too. Once enabled it encrypts TempDB, when removing TDE totally you'll need to restart the instance to remove the encryption from TempDBThe effect of TDE on TEMDB is transparent to other databases and processes on the server. I don't believe it's a requirement to restart the server after removing TDE; I've created and removed TDE while testing and didn't have to do anything special in regard to TEMPDB. Encrypting and decrypting a database is an asynchronous and entirely online operation.
Maybe, but is still has some impact at the server level.
Since tempdb is usually constantly in use a restart of the service is usually required
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2017 at 8:34 am
TDE will show as encrypted until restart, because part of it may be encrypted. No one will likely check, and evidence will be erased once the instance is restarted.
May 26, 2017 at 10:55 am
Thanks everyone.
One more question related this, I know the TDE purpose is to protect the data of rest. that is encrypt the mdf, ldf, and backup file so that the data files cannot be restored to another instance without certificate and key.
But I also found out using import and export wizard you can still import encrypted data to another server, which destroys the purpose of using TDE, then what is the benefit of using TDE?
Thanks.
May 26, 2017 at 11:20 am
Also since I am experiment TDE, I would like to remove it totally after I experiment.
I think the process is :
1. disable encryption on the encrypted database
2. do a full backup of the database
3. drop the certificate (no other databases are encrypted on the server)
4, drop the master database master key
5. restart instance so that tempdb is recreated.
Is it a good clean up, or any risk?
Thanks
May 26, 2017 at 11:52 am
sqlfriends - Friday, May 26, 2017 11:20 AMAlso since I am experiment TDE, I would like to remove it totally after I experiment.I think the process is :
1. disable encryption on the encrypted database
2. do a full backup of the database
3. drop the certificate (no other databases are encrypted on the server)
4, drop the master database master key
5. restart instance so that tempdb is recreated.Is it a good clean up, or any risk?
Thanks
This will work fine.
May 26, 2017 at 11:54 am
sqlfriends - Friday, May 26, 2017 10:55 AMThanks everyone.
One more question related this, I know the TDE purpose is to protect the data of rest. that is encrypt the mdf, ldf, and backup file so that the data files cannot be restored to another instance without certificate and key.But I also found out using import and export wizard you can still import encrypted data to another server, which destroys the purpose of using TDE, then what is the benefit of using TDE?
Thanks.
The data is encrypted at rest, but this is transparent to your application for authorized users. Meaning, a user authorized to connect to the database can read the data just fine. The import/export wizard is a legitimate client. This isn't something the server does, but this is you, or any other user with an account, connecting to SQL Server and reading data. That the target is a file instead of a screen is irrelevent.
Do you expect this to prevent exports from legitimate users?
May 26, 2017 at 12:00 pm
Thanks got it. It is Ok for legitimate user to import data.
Also I just tried to restore the encrypted database on another SQL 2016 SP1 standard edition, and then I found I cannot because it is only available in Enterprise edition. That is too bad, since most of our servers are standard edition.
That means I cannot restore the database on this instance then.
I also found even that restore failed, but it does make the tempdb showing encryption status =3 when I created the master key and certificate on the restore server.
I remember I read some where saying , why is that? thanks
Database backup encryption is a Standard Edition feature, meaning that it's available on the Standard and Enterprise editions of Microsoft SQL Server 2014.
While you can only backup with Standard or Enterprise Edition, an encrypted backup can be restored to any edition of Microsoft SQL Server 2014, including Express and Web editions.
May 26, 2017 at 12:40 pm
TDE is an Enterprise only feature, so it cannot be enabled on SQL 2014 Standard.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply