Set-AzureRmSqlDatabase command is failing while lowering the Azure SQL DB pricing tier due to key vault soft delete

Question

I'm using an automated powershell script to downgrade the pricing tier of the database backup copy. While supplying the below command the tier downgrade fails. The error, if I understood correctly is referring to key-vault with a key named same as my server name, since there is no such key exists(hence the soft delete can also be not enabled), this command fails.

The command has been set-up when my application was not set-up with key-vault and seems like now it's failing.

Command used:

Set-AzureRmSqlDatabase -DatabaseName <*Back-up DB name*> -ServerName <*SQL server name*> -ResourceGroupName <*Resource Group name*> -Edition Standard -RequestedServiceObjectiveName S0

Error:

Set-AzureRmSqlDatabase : 45377: The provided Key Vault uri 'https://****.vault.azure.net/keys/<SERVERNAME>/<Subscription/some ID> is not valid. Please ensure the key vault has been configured with soft-delete. (https://aka.ms/sqltdebyoksoftdelete) At line:1 char:2 + Set-AzureRmSqlDatabase -DatabaseName <Back-up DB name> -ServerName <SQL server name>... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Set-AzureRmSqlDatabase], CloudException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Database.Cmdlet.SetAzureSqlDatabase

Questions :
1.Why Set-AzureRmSqlDatabase command is referring to a key-vault URI, when not mentioned explicitly ?

2.Is there a option we need to set at the server/DB level to allow this command to read the server/DB name directly rather than searching for a key with server name?

3. Is this anywhere related to Transparent data encryption ?

4. Are we suppose to make any changes to this command if the key-vault comes later than the application/DB/key-vault set-up ?

Answer 1

This issue was purely related to TDE(Transparent data encryption) as I thought of. Since Azure SQL databases were secured by TDE, it was expected that the key-vault should also be enabled with soft delete to recover any deleted keys.
While trying to enable soft-delete, I figured out that the Azure powershell installed on my machine doesn't support soft delete property.

I am able to resolve this issue by following steps :

1. Upgraded powershell : Installation Package

2. Login into your azure subscription and run this command

   $vault = Get-AzureRmKeyVault -VaultName myvault; $vault.EnableSoftDelete

3. If the above doesn't work run the below command. This will find the resourceId of the key-vault and then will enable soft-delete -

($resource = Get-AzureRmResource -ResourceId (Get-AzureRmKeyVault -VaultName "YourKeyVaultNameHere").ResourceId).Properties | Add-Member - MemberType "NoteProperty" -Name "enableSoftDelete" -Value "true"

Set-AzureRmResource -resourceid $resource.ResourceId -Properties $resource.Properties

4. Verify if the key-vault soft-delete is enabled by below command

Get-AzureRmKeyVault -VaultName "YourKeyVaultNameHere"

Hope this would be helpful for someone facing the similar issue.

Answer 2

Here are some personal opinions for you to refer.

First, per my test, the command works fine on my side.

Note: In my test environment, it is a sql server and database without any other things, like transparent data encryption.

Set-AzureRmSqlDatabase -DatabaseName joydatabase -ServerName joydb -ResourceGroupName joywebapp -Edition Standard -RequestedServiceObjectiveName S0

Why Set-AzureRmSqlDatabase command is referring to a key-vault URI, when not mentioned explicitly ?

On my side, I catch the request via fiddler, it is not referring to a key-vault URL, refer to the screenshot.

Is there a option we need to set at the server/DB level to allow this command to read the server/DB name directly rather than searching for a key with server name?

On my side, I think we needn't to do so.

Is this anywhere related to Transparent data encryption ?

I think there is a great possibility that it is related to it. You could create a new sql server and database to have a try. Here is an article about transparent data encryption for azure sql server, you could refer to it.

Are we suppose to make any changes to this command if the key-vault comes later than the application/DB/key-vault set-up ?

I think it seems not make any change to this command.