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()
}

2 Answers

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

7

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:

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.