Copy SQL database with encryption from one Azure subscription to another

Question

I need to copy an entire Azure SQL Database from one subscription to another.

The challenge I have is that it has a certificate with symmetrical key and if I try to export it to a .bacpac file I get this error:

SQL71626 (certificate/symmetric key is not supported in Microsoft Azure SQL Database v12)

Like all things IT there are probably 101 ways of doing this, and a number of sites explain some options (but none that I have found mention encryption and how best to deal with it).

Some additions info: The destination subscription already exists It has a SQL Server, I can add more if required.

Answer 1

Yes, you can Copy the SQL database with encryption from one Azure subscription to another. But you have to move your database along with an Azure SQL Server. Not just the database itself. You can follow my steps as a workaround.

I tested this successfully with the following steps:

1. Subscription 1 Create a new resource group, an Azure SQL Server and Azure SQL database which is empty.

2. Create a certificate and symmetric key. Example from Microsoft documentation. Certificate and Symmetric key.

   CREATE CERTIFICATE Shipping04   
     ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
     WITH SUBJECT = 'Sammamish Shipping Records',   
     EXPIRY_DATE = '20201031';  
   GO  
   
   CREATE SYMMETRIC KEY JanainaKey09   
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Shipping04;  
   GO   
   

3. Create another resource group in the same subscription as the staging area. Create a new Azure SQL Server and copy the database once the backup is available. You need this because when you move the resource they will not available at source after the move.

 Restore-AzSqlDatabase `
-FromPointInTimeBackup `
-PointInTime (Get-Date).AddMinutes(-10) `
-ResourceGroupName $Database.ResourceGroupName `
-ServerName $secondaryServerName `
-TargetDatabaseName $secondarydatabaseName `
-ResourceId $Database.ResourceID `
-Edition "Premium" `
-ServiceObjectiveName "P1" }

4. Move Azure SQL Server and refreshed database to a different subscription. You can move to an existing resource group and create a new one. Use Move-AzResource command. Details here and here.
5. Now copy the database to your existing SQL Server.
6. Delete the resources in step 4.

If you try to move only the database this the error message you get.

"properties": {
        "statusCode": "BadRequest",
        "serviceRequestId": null,
        "statusMessage": "{\"error\":{\"code\":\"InvalidResourceMoveRequest\",\"message\":\"The list of resources in move definition cannot be null or empty.\"}}"
    },

Answer 2

SQLPackage.exe is used to export the database and it does not support symmetric keys. If you can copy the database, then drop the symmetric key and the certificate on the database created by the copy operation. After that you will be able to export that copy of the database as a bacpac.

Answer 3

The easy way will be to remove the symmetric key and add it back after backup.

DROP SYMMETRIC KEY SymKey;
GO

CREATE SYMMETRIC KEY SymKey   
WITH ALGORITHM = AES_256   
ENCRYPTION BY PASSWORD = 'xxxx';
GO