microsoft / microsoft/DacFx

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

Open
#147 5 comments 0 reactions 1 assignee Claimed by @ssreerama View on GitHub
enhancement triaged
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- 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.<>c__DisplayClass67_2.b__2()
at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
at Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass67_1.b__0(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(Func`1 streamGetter, String databaseName, DacExportOptions exportOptions, IEnumerable`1 tables, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacExportOptions options, IEnumerable`1 tables, Nullable`1 cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.ExportBacpac(String packageFileName, String databaseName, DacSchemaModelStorageType modelStorageType, IEnumerable`1 tables, Nullable`1 cancellationToken)
at Microsoft.SqlServer.Management.Dac.DacWizard.ExportDatabase.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.