2

I'm setting up a CI for Azure SQL DB.

When running 

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130>sqlpackage.exe /SourceFile:"MyPackage.dacpac" /Action:Publish /TargetServerName:"my-server.database.windows.net" /TargetDatabaseName:"db-name" /TargetUser:myuser /TargetPassword:"SomeSecurePassword" /TargetTimeout:120

I'm getting

Initializing deployment (Start)

Initializing deployment (Failed)

An error occurred during deployment plan generation. Deployment cannot continue. Failed to import target model db-name. Detailed message The element SYMMETRIC_KEY_NAME is not supported in Microsoft Azure SQL Database v12.

SYMMETRIC_KEY_NAME is defined as:

CREATE SYMMETRIC KEY [SYMMETRIC_KEY_NAME]
AUTHORIZATION [dbo] 
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE [AAACERT];

It is my understanding that symmetric keys encryption is supported by Azure SQL (https://learn.microsoft.com/en-us/azure/sql-database/sql-database-transact-sql-information) although architecture of certificates storage is different.

Is there a workaround I can use (a parameter for sqlpackage.exe that I can set up on a build agent for instance) or a flag that can suppress this error to bypass this limitation?

So far I've tried:

/p:AllowIncompatiblePlatform=True

/p:ExcludeObjectTypes=Certificates;SymmetricKeys

/p:ScriptDatabaseCompatibility=False

/p:TreatVerificationErrorsAsWarnings=True

/p:UnmodifiableObjectWarnings=False

/p:VerifyDeployment=False

/p:RunDeploymentPlanExecutors=False

Arthur P
  • 1,050
  • 9
  • 16
  • During my test, the deploy succeeds with /p:AllowIncompatiblePlatform=True and /p:ExcludeObjectTypes=SymmetricKeys specified. And i could see both certificate and symmetric key exists on SQL database. I'm suspecting it's a sqlpackage.exe issue. – Hello Feb 13 '17 at 06:15
  • Please elaborate - question was about the fact that sqlpackage.exe does not deploy anything when these keys are specified. Are you saying it did deploy for you? What version of sqlpackage.exe are you using? – Arthur P Feb 14 '17 at 18:57
  • @ArthurP I had a similar issue - "create symmetric key statement is not supported" with Azure SQL V12 target profile while trying to deploy a DB designed for SQL 2014 to Azure SQL. But the error was not rising on the publish step, but rather during the build, so I was not able to proceed with publish at all. I finished with setting target profile back to SQL 2014 and setting AllowIncompatiblePlatform to true in advanced publish settings - it did the trick. Seems to be a dirty hack since the create assymetric key is actually supported by Azure SQL and should with out of box but anyway... – Vladimir Glushkov Oct 10 '17 at 15:12

0 Answers0