DacFx: Unable to read data-tier application registration after Publish using SqlPackage

Here are the steps to reproduce the problem I met:

Run SQL Server Container for Linux

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Ver7CompleXPW" -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2019-latest

Deploy ContosoUniversity.dacpac as data-tier application

sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /p:RegisterDataTierApplication=true /p:BlockWhenDriftDetected=true  /TargetTrustServerCertificate:true

Deploy again using the exact same above command.

sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /p:RegisterDataTierApplication=true /p:BlockWhenDriftDetected=true  /TargetTrustServerCertificate:true

Here is the output:

Publishing to database 'ContosoUniversity' on server '.'.
Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Unable to read data-tier application registration.
Time elapsed 0:00:10.68

I’m trying to create a DriftReport

sqlpackage /Action:DriftReport /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /OutputPath:DriftReport.xml

Here is the output:

Generating drift report for database 'ContosoUniversity' on server '.'.
*** Could not generate drift report.
Unable to read data-tier application registration.
Time elapsed 0:00:00.64

If I delete the data-tier application and register it from SSMS. Then the above commands are all works.

If I use the following command to publish again.

sqlpackage /Action:Publish /SourceFile:"ContosoUniversity.dacpac" /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /p:RegisterDataTierApplication=true /p:BlockWhenDriftDetected=false

Note: the /p:BlockWhenDriftDetected is false which means this don’t read the data-tier application registration.

Then the /Action:DriftReport still showing Unable to read data-tier application registration.* error message.

sqlpackage /Action:DriftReport /TargetDatabaseName:"ContosoUniversity" /TargetServerName:"." /TargetUser:sa /TargetPassword:Ver7CompleXPW /OutputPath:DriftReport.xml

logs:

Generating drift report for database 'ContosoUniversity' on server '.'.
*** Could not generate drift report.
Unable to read data-tier application registration.
Time elapsed 0:00:00.64

Is this a bug?

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 3
Comments: 16 (3 by maintainers)

Most upvoted comments

I’m having the same problem on current release:

Version number: 161.6374.0
Build number: 16.1.6374.0
Release date: November 9, 2022

I tested against SQL Server versions 2014 through 2019. I’ve attached a complete repo script with my output.

@llali Can you re-open? You didn’t mention what version of SqlPackage you tested with (the content of the page you linked to will change with each release), but if the problem wasn’t there in the version you tested, it seems it’s back in the current version. SqlPackageTest.zip

nmbell on Dec 12, 2022

Adding @dzsquared

pensivebrian on Jun 21, 2021

A similar problem occurs when we try to update the database using the dacpac file through DacServices.Deploy from DacFx version 150.5164.1:

Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package.
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
Inner exception:
---> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to read data-tier application registration.
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentRegistrar.ReadModel(FileInfo path, ModelStorageType modelStorageType)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentRegistrar.CheckDrift(Boolean useContextTargetAsCurrent, ModelStorageType modelStorageType)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.ReportDrift()
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.ReportDrift()
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.DatabaseHasDrifted()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass18_1.<CreatePlanInitializationOperation>b__1()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass18_0.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   --- End of inner exception stack trace ---

Pay attention to the inner exception, it is also thrown when trying to read the model for a drift report:

Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to read data-tier application registration.

klukyanov76 on Jun 21, 2021

Still an issue with sqlpackage 16.1.8089.0

here are the options i’m publishing with that caused the issue /p:BlockOnPossibleDataLoss=False /p:ExcludeObjectTypes="Permissions;RoleMembership;Credentials;Users" /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:DropObjectsNotInSource=True /p:RegisterDataTierApplication=True /p:IncludeTransactionalScripts=True /p:BlockWhenDriftDetected=False

FWIW I was able to perform a deploy without any issue as long as I didn’t RegisterDataTierApplication in the options, - makes me think there’s an issue with the way that sqlpackage is writing the metadata into sql server since SSMS can’t read it either.

drbergeron on Apr 19, 2023

Drift Report is still broken for DacFx 19.1 (16.0.6161.0) and keeps resulting in the Unable to read data-tier application registration error.

aadje on Jul 18, 2022

I also got this problem today. Does @pensivebrian and @dzsquared have some time to take a look on this issue?

suxiaobu9 on Nov 9, 2021