How to turn off database encryption through powershell

Question

I have setup a database which is TDE encrypted. Now I need to disable this encryption through PowerShell. I am able to get some breakthrough but facing the below error

Error:Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key. however encryption key gets switched off but key gets dropped I believe. Below is the screenshot how it looks after first run of the code

Below  is the code that I have written/used:

   function set-EncryptionOff($ExistingDB)
{
    $ExistingDB.EncryptionEnabled=$false
    $ExistingDB.Alter();
    $ExistingDB.DatabaseEncryptionKey.Refresh()
    $ExistingDB.DatabaseEncryptionKey.Drop()

}

score 3 · Accepted Answer · answered May 28 '17 at 03:38

3

You're very close. After you set EncryptionEnabled to false, you need to do a $ExistingDB.Alter() to tell the server to actually do that. Once you do that, you can safely drop the database encryption key using the command you already have.

Full script:

$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName
$ExistingDB=$sqlServer.Databases.Item($dbname) 
$ExistingDB.EncryptionEnabled=$false
$ExistingDB.Alter()
$ExistingDB.DatabaseEncryptionKey.Refresh()
$ExistingDB.DatabaseEncryptionKey.Drop() #should work now

answered May 28 '17 at 03:38

Ben Thul

31,080
4
45
68

Is there any event listener to identify the completion of the DB decryption as the DB is huge and needs to be backed up automatically once the decryption has completed – SQLDoctor May 28 '17 at 07:33
It looks like you asked this as an actual question here: https://stackoverflow.com/questions/44225996/event-handler-for-tde-decryption-process-in-powershell/44229087. Answer provided there. – Ben Thul May 28 '17 at 15:38
I have modified the question again..can you please look into it. – SQLDoctor May 29 '17 at 07:18
If you have a large database, I can see a case where a lot of time would need to shape between the alter database and when you can drop the encryption key (since even though you've stated that you want to want the DB to be decrypted, it takes time for all of the data to actually get decrypted). Per your other question, you can check on the progress of that transition in multiple ways. – Ben Thul May 29 '17 at 12:30
ok. I am using the above code which you have specified on a small DB[210 MB] for the decryption process and still facing the below issue "Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key" – SQLDoctor May 29 '17 at 12:34
The only thing I can tell you is "wait until the database is done decrypting to drop the database encryption key". If you're running the script as is, decryption is unlikely to be finished in the ≈ 100ms between when you issue the `Alter()` and when you try to drop the key. What is your ultimate goal here? – Ben Thul May 29 '17 at 15:07
ok, now I have added a timer object to check the percentage completion before going ahead with dropping the key. – SQLDoctor May 29 '17 at 15:35

score 2 · Answer 2 · answered May 27 '17 at 19:56

It looks like you can use the Azure PowerShell cmdlet Set-AzureRMSqlDatabaseTransparentDataEncryption to do this:

Enabling and Disabling TDE on SQL Database by Using PowerShell

Using the Azure PowerShell you can run the following command to turn TDE on/off. You must connect your account to the PS window before running the command. Customize the example to use your values for the ServerName, ResourceGroupName, and DatabaseName parameters. For additional information about PowerShell, see How to install and configure Azure PowerShell.

..

To disable TDE:
Set-AzureRMSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName
"database1" -State "Disabled"

If using version 0.9.8 use the Set-AzureSqlDatabaseTransparentDataEncryption command.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-with-azure-sql-database

Set-AzureRMSqlDatabaseTransparentDataEncryption is not a recognized cmdlet,can you please tell me which package to be included? — SQLDoctor, May 29 '17 at 09:58
I believe it's part of the AzureRM module https://learn.microsoft.com/en-gb/powershell/azure/install-azurerm-ps?view=azurermps-4.0.0 — Mark Wragg, May 29 '17 at 10:03

How to turn off database encryption through powershell

2 Answers2