question

long-5688 avatar image
0 Votes"
long-5688 asked Cathyji-msft edited

SqlPackage doesn't support databases with symmetric keys?

I am trying use SqlPackage.ext to deploy dacpac file to azure database with symmetric keys. It raises error:
Failed to import target model MyDatabase. Detailed message The element SymmetricKeyname is not supported in Microsoft Azure SQL Database v12.
I do not need to alter any symmetic keys. Just add a new procedure to the target database.

Do I have to remove the symmetric keys from target database, before running sqlPackage?

azure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Some article says setting target platform to SQL 2014 and setting AllowIncompatiblePlatform to true in advanced publish settings and I tried it still shows below error:
Initializing deployment (Start)
A project which specifies SQL Server 2014 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.
Initializing deployment (Failed)
An error occurred during deployment plan generation. Deployment cannot continue.
Warning SQL0: A project which specifies SQL Server 2014 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

Failed to import target model Profile. Detailed message The element SymmetricKey1 is not supported in Microsoft Azure SQL Database v12.
The element SymmetricKey1 is not supported in Microsoft Azure SQL Database v12.
##[error]Process completed with exit code 1.

0 Votes 0 ·

I could be wrong. CLE still needs a public certificate? and sqlpackage does not support it either?
it looks that CLE will create private certificate, from a public certificate, and symmetric key every time when the code does encryption?
and remove them after encryption, otherwise, the key still left in database, which will block sqlpackage?

Currently, I can use VS to publish the code to my database. Why does sqlpackage not implement to do the same way?

0 Votes 0 ·

1 Answer

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

Support for certs and symmetric keys against Azure SQL DB has been complicated by concerns around the impact on data export/import scenarios.

Please read the following excerpt from Microsoft Docs:
"Additionally, it is worth emphasizing that because the symmetric key & asymmetric key objects cannot be exported, data encrypted or signed can be lost when copied to a different database using the Import/Export (I/E) functionality in SQL Database which is based on logical data movementusing bacpac files and the DACFx API."
Source: Recommendations for using Cell Level Encryption in Azure SQL Database.

Even trying to run a CREATE SYMMETRIC KEY statement while using import/export/publishing tools like SDDT and sqlpackage should produce error SQL70015. Maybe removing the symmetric key and the encryption is the only way to go, because database tools do not handle that.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.