SQL Server target platform dacpacs won't publish to Microsoft Azure SQL v12

Question

I am recreating an Azure resource group deployment that contains a working azure sql server. I am repeating the targeting of a dacpac to an azure sql server instance that has exactly the same specs. I believe I am doing it the same way I did it a year ago.

My method uses a Visual Studio database project spun off ASP Net Core Web App Solution (web app database in VS SQL Server Object Explorer>Create New Project)

Create targeted dacpac

• right-click on project name in VS Solution Explorer & choose Properties
• under VS Project Settings* choose the target platform
• replace default Target Connection String in Properties>Debug
• navigate back to VS Solution Explorer. Right-click on VS Project again & choose Project Snapshot
• the following file type rollbasesqldatabase_20230301_14-19-35.dacpac is written to Snapshots folder in VS project
• navigate to connected Azure sql server instance in VS SQL Server Object Explorer
• right click and choose Publish Data-tier Application

The error I'm getting is familiar. But I'm getting it for all versions: Publish failed. An error occurred during deployment plan generation. Deployment cannot continue. A project which specifies SQL Server 2014 as the target platform cannot be published to Microsoft Azure SQL Database V12.
This occurs for me on target platforms SQL Server 2012, 2016 & 2019 too

My data source server is:

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-rollbase-6D7D2449-EB25-45CE-A8D3-EDAB69CDCAB3;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

My publish database connection is on this server in VS:

Data Source=(localdb)\ProjectsV13;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

My Properties>Debug edit is from:

Data Source=(localdb)\ProjectsV13;Initial Catalog=rollbasesqldatabase;Integrated Security=True;Pooling=False;Connect Timeout=30

Edited Data Source=rollbasesqlserver.database.windows.net;Initial Catalog=rollbasesqldatabase;Persist Security Info=True;User ID=rollbaseAdmin;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=True;TrustServerCertificate=False

I noticed the V13 reference of the server which hosts the database that I (right-click) Publish Data-tier Application. Is this relevant? This post Purpose of ProjectsV13 LocalDB instance seems inconclusive to me.

Other than this I have no idea why this dacpac deployment fails when I have done it before.

Answer 1

There are several ways to deploy an ASP Net Core Entity Framework SQL Server Database to Azure. In order to run a Web App with an Azure SQL backend.

• GitHub Actions/Workflow dacpac with YAML file
• Visual Studio publish Data-tier Application using dacpac
• Microsoft SQL Server Management Studio (same as above)
• As well as others like Azure Data Studio & the command line sqlpackage all using dacpac file

But I found another way that produced more helpful warning/error messages. Assuming the Azure resources are present. And therefore there is a Azure SQL Server & Database.

• In MSSM connect to the Azure SQL Server Database
• Right click on the database name
• Choose Tasks
• Choose Upgrade Data-tier Application

So in this way I take it I was upgrading from blank Azure SQL Database template to one with a schema.

I still got errors. But these were specific so helpful.

Here the first warning is the previous 'Publish failed. An error occurred ... etc' But it's just a warning. And qualifies this by saying there may be 'compatibility issues'. The real error is that I made a dumb mistake by leaving an sa user hanging around after development. Deleting this user allowed the upgrade to go ahead successfully.

This shows how much variation there is with error messages. Prior to getting this information I had no idea why my database deployment was failing.