Azure SQL Database – Copy Encrypted Database Between Subscriptions

azureazure-sql-databasesql server

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.

Best Answer

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" }
  1. 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.
  2. Now copy the database to your existing SQL Server.
  3. 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.\"}}"
    },
Related Question