How to Turn off Database Encryption Through Powershell
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...
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
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-AzureSqlDatabaseTransparentDataEncryptioncommand.
Source:
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
