Unable to create bacpac from Azure SQL DB if it has a symmetric key

[erinstellato-ms]

• SqlPackage or DacFx Version: 19.2
• .NET Framework (Windows-only) or .NET Core: .NET
• Environment (local platform and source/target platforms): Windows 10 and Azure SQL DB

Steps to Reproduce:

1. Within the Azure SQL DB run the following:

CREATE CERTIFICATE MyTestCer
ENCRYPTION BY PASSWORD = 'abcdef123456!&'
WITH SUBJECT = 'TestExport',
EXPIRY_DATE = '20221231';
GO

CREATE SYMMETRIC KEY SymKeyTest
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE MyTestCer;
GO

2. After creating the cert and key, try to export directly using sqlpackage. This also fails in SSMS.

SqlPackage 19.2: "C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Export /ssn:tcp:,1433 /SourceDatabaseName:WWI_PS /su: /sp: /tf:C:\Temp\WWI.bacpac /p:Storage=File)

SSMS 19 Preview 3 (and SSMS 18.12.1): right-click on the database, select Tasks, Export Data-tier application.

Did this occur in prior versions? If not - which version(s) did it work in? I assume it's always been an issue

Error in SqlPackage: Connecting to database 'WWI_PS' on server 'tcp:,1433'. Extracting schema Extracting schema from database Resolving references in schema model Validating schema model Validating schema model for data package Validating schema The ledger data in system views will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2165963. Element [dbo].[Ledger_History] is a history table for the [dbo].[LedgerTableName] updatable ledger table. Migrating data in history tables is not supported. The data will not be included in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2165963. Element [dbo].[LedgerTableName].[StartTransactionID] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. Element [dbo].[LedgerTableName].[EndTransactionID] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. Element [dbo].[LedgerTableName].[StartSeqNum] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. Element [dbo].[LedgerTableName].[EndSeqNum] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. Element [dbo].[Ledger_AddOnly].[ledger_start_transaction_id] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. Element [dbo].[Ledger_AddOnly].[ledger_start_sequence_number] is a column with system-generated values (a GENERATED ALWAYS column) in a ledger table. The data stored in the column will not be captured in the resulting bacpac file or the dacpac file. For more information see https://go.microsoft.com/fwlink/?linkid=2166135. *** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package. Error SQL71626: The element Certificate: [LocalCert] is not supported in Microsoft Azure SQL Database v12. Error SQL71626: The element Symmetric Key: [SymKeyTest] is not supported in Microsoft Azure SQL Database v12.

Error in SSMS: One or more unsupported elements were found in the schema used as part of a data package. Error SQL71626: The element Certificate: [LocalCert] is not supported in Microsoft Azure SQL Database v12. Error SQL71626: The element Symmetric Key: [SymKeyTest] is not supported in Microsoft Azure SQL Database v12. (Microsoft.SqlServer.Dac)

---

Program Location:

at Microsoft.SqlServer.Dac.DacServices.<>cDisplayClass67_2.b2() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DacServices.<>cDisplayClass67_1.b0(Object operation, CancellationToken token) at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(Func1 streamGetter, String databaseName, DacExportOptions exportOptions, IEnumerable1 tables, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacExportOptions options, IEnumerable1 tables, Nullable1 cancellationToken) at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacSchemaModelStorageType modelStorageType, IEnumerable1 tables, Nullable1 cancellationToken) at Microsoft.SqlServer.Management.Dac.DacWizard.ExportDatabase.DoWork() at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

[ssreerama]

Tested with latest version of sqlpackage and can be reproduced in Sql DB and Sql DW databases, with the test certificate alone is failing "Error exporting database:One or more unsupported elements were found in the schema used as part of a data package. Error SQL71626: The element Certificate: [MyTestCer] is not supported in Microsoft Azure SQL Database v12." Will need to check if it's a regression or an enhancement!

[ssreerama]

Update: Its not a regression, as the error exists in 15.0.5164.1 version too, but Extract action has no issue with the certificate.

[ssreerama]

@erinstellato-ms, this is by design, as we support extracting the keys, but DacFx should never be exporting then as "SQL has never supported crypto objects though DacFx". Closing this Issue and if have any requests we can look into it.

[llali]

@ssreerama this is a feature request so we should keep it open and add label as feature request

[smholvoet]

Just ran into issue when trying to export to a BACPAC, happy days 😣

Error SQL71626: The element Certificate: [Cert_foo_20221124_21541231_45366] is not supported in Microsoft Azure SQL Database v12.