CODEWITHSUNDEEP
azureazure-devopsazure-pipelines-release-pipelinedacpac
Developer Guy
Developer Guy

Reputation: 2434

Azure DevOps Azure SQL DacpacTask SQL72018 Permission Could Not Be Imported

We have a release pipeline in Azure DevOps that deploys a database project to our Azure SQL Database via the Azure SQL Dacpac Task. Everything has been working fine but suddenly yesterday the pipeline started failing with the following error:

##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.
##[error]Error SQL72018: Permission could not be imported but one or more of these objects exist in your source.

As far as I know, nothing has changed on the database side or in the pipeline. We also ruled out that it could be an issue with the specific dacpac file because previously successful releases now fail with the same error.

I searched extensively for the SQL72018 error, but didn't really find any answers as to what would be causing that so am wondering if there was some Azure DevOps task update or something that we could be missing?

Not sure what would have caused this to break out of nowhere like that.

It does work if we add the p:/ignorePermissions=true parameter to the task, but we have never needed that before this.

UPDATE:

Wanted to update this as I was able to gather a little more information by adding the /Diagnostics:True parameter to the pipeline task in order to print out Diagnostic info from SQLPackage.

When I added that, I also see this error:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 1 : 2022-04-05T08:38:37 : Error detected when reverse engineering the database. Severity:'Warning' Prefix:'' Error Code:'0' Message:The permission 'VDP ' was not recognized and was not imported. If this problem persists, contact customer support.

So it looks like some "VDP" permission is what is causing the issue, but we don't know what that permission is for or where it came from as it's not in the database project.

Upvotes: 5

Views: 3577

Answers (2)

Developer Guy
Developer Guy

Reputation: 2434

We finally got to the bottom of this. It turns out a new permission was added to the database the day before the pipeline started to fail. The database permission that caused the issue was VIEW DATABASE PERFORMANCE STATE. That was the VDP permission that SQLPackage.exe was complaining about.

Unsure why that particular permission caused the error as we manage all of our database permissions outside of the database project, so not sure why other permissions wouldn't have caused issues prior to this one.

Since we are managing permissions outside of our database project, the resolution was to add the p:/ignorePermissions=true SQLPackage parameter to the pipeline permanently. This was confirmed as the appropriate solution by a Microsoft representative after we put a ticket in.

Upvotes: 3

scorpio
scorpio

Reputation: 1625

It seems you have a spurious/orphan permission in your target database - as mentioned in this post How could our database be causing SqlPackage to fail? (SQL72018).

Upvotes: 1

Related Questions