Login failed during Azure Release Pipeline DACPAC deployment

Question

I am trying to deploy a dacpac to a database in Azure SQL server using a release pipeline in azure devops. I was able to deploy a dacpac to one database successfully by adding Azure Object Name as db_owner of that database. I repeated the same step for a different dacpac, but I am getting an error saying the access failed.

2022-04-06T21:54:52.4823080Z Initializing deployment (Start)
2022-04-06T21:55:49.0595299Z Initializing deployment (Failed)
2022-04-06T21:55:49.0721494Z Time elapsed 0:00:57.89
2022-04-06T21:55:49.2784337Z ##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.
2022-04-06T21:55:49.2883974Z ##[error]Login failed for user '<token-identified principal>'.

using service principal as authentication (using SPN of service connection for the service principal) DB server : xyz DB databases : xyz.a xyz.b

a is successful, but b fails on same release pipeline.

it seems like access token was successfully grabbed in the script.

Answer 1

Some dacpac deployments were successful because the dacpacs had permissions that were included in the SG which was set as the AAD Admin of the SQL servers.

The dacpacs which had permissions which were not included in the AAD Admin SG were failing. The solution was to drop the permission objects using the sqlpackage.exe flag:

/p:ExcludeObjectTypes=Users;ServerRoleMembership;ServerRoles;DatabaseRoles;RoleMembership;Users;Permissions