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

[doggy8088]

Here are the steps to reproduce the problem I met:

1. 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
   

2. 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
   

3. 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
   

   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
   

4. 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
   

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

6. 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.

7. 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?

[klukyanov76]

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.

[doggy8088]

@pensivebrian Can you take a look on this issue?

[pensivebrian]

Adding @dzsquared

[suxiaobu9]

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

[BradHarder]

I just wanted to add what I've seen to hopefully get a bit of traction for this issue.

I've been playing with different releases of the package, and it looks like v150.4286.1 seems to work fine, but after that it starts returning the above error. I have tried the latest 160.5339.7 preview which still encounters the error. I whipped up some test apps to try out the versions and from my limited check I've found the following:

• Using v160.5339.7, initial deployment seems to work fine, update operation the error is encountered.
• Installing using v160.5339.7 and then upgrading using v150.4286.1 also displays the error
• Installing using v150.4286.1 and then upgrading using v160.5339.7 works fine, but then updates thereafter display the error

For reference, the error seems to be occurring in the "Initializing deployment" section (based on output from DacServices.Message and ProgressChanged).

My test app for v160.5339.7 is using .net 5 My test app for v150.4286.1is using .net 4.7.2

Is there any workaround for it at the moment?

[alesacchi]

Hi! Any update on this? I'm getting the same error with sqlpackage v16.0.5400.1.

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2022-03-25T09:58:22 : Microsoft.SqlServer.Dac.DacServicesException: Could not generate drift report.
 ---> 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) in F:\B\16835\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentRegistrar.cs:line 352
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentRegistrar.CheckDrift(Boolean useContextTargetAsCurrent, ModelStorageType modelStorageType) in F:\B\16835\6200\Sources\Product\Source\SchemaSql\Deployment\SqlDeploymentRegistrar.cs:line 78
   at Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass55_0.<GenerateDriftReport>b__0(Object operation, CancellationToken token) in F:\B\16835\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 1507
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context) in F:\B\16835\6200\Sources\Product\Source\DeploymentApi\Operation.cs:line 74
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken) in F:\B\16835\6200\Sources\Product\Source\DeploymentApi\IOperation.cs:line 28
   at Microsoft.SqlServer.Dac.DacServices.GenerateDriftReport(String targetDatabaseName, DacSchemaModelStorageType modelStorageType, Nullable`1 cancellationToken) in F:\B\16835\6200\Sources\Product\Source\DeploymentApi\DacServices.cs:line 1518
   --- End of inner exception stack trace ---
*** Could not generate drift report.
Unable to read data-tier application registration.
Time elapsed 0:00:01.37
Microsoft.Data.Tools.Diagnostics.Tracer Information: 0 : 2022-03-25T09:58:22 : SqlPackage completed

Thanks!

[doggy8088]

@pensivebrian @dzsquared @kisantia Is there anyone can take a look on this issue?

[adermon]

is there any movement or workaround for this? I have same issue, any DAC deployments from SSMS will create and upgrade OK. Using the DacServices.Deploy from DacFx 160.5196.4-preview will deploy but not upgrade from SSMS with the same stack trace. I've not tried latest version of DacFx. UPDATE: i have the same result using 160.6057.0-preview to deploy the DAC, which deploys OK but upgrade from SSMS gives

Could not generate drift report. (Microsoft.SqlServer.Dac)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dac.DacServices.GenerateDriftReport(String targetDatabaseName, DacSchemaModelStorageType modelStorageType, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.GenerateDriftReport(String targetDatabaseName, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Management.Dac.DacWizard.CheckDriftModel.CheckDrift()
   at Microsoft.SqlServer.Management.Dac.DacWizard.CheckDriftPage.validateWorker_DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

===================================

Unable to read data-tier application registration. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
Program Location:

   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.SqlServer.Dac.DacServices.<>c__DisplayClass3c.<GenerateDriftReport>b__3b(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.GenerateDriftReport(String targetDatabaseName, DacSchemaModelStorageType modelStorageType, Nullable`1 cancellationToken)

UPDATE: If i attempt an upgrade with Deploy(dp, databaseName, upgradeExisting: upgrade, new DacDeployOptions() { RegisterDataTierApplication = true, BlockWhenDriftDetected = false }); It succeeds but with BlockWhenDriftDetected = true fails

[aadje]

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.

[llali]

I cannot repro this using the latest version sqlpackage.exe https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

[adermon]

I cannot repro this using the latest version sqlpackage.exe https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

I can still repo this bug on Windows both with sqlpackage (19.2) and the Microsoft.SqlServer.DacFX (160.6296.0). Can this be re-opened?. /p:RegisterDataTierApplication=true /p:BlockWhenDriftDetected=true are key to reproducing the issue.

[nmbell]

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

[drbergeron]

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.

[zijchen]

Fixed with 162.1.167

[doggy8088]

Note: Install from here.

[doggy8088]

@zijchen There is another error message shown.

Version:

C:\>sqlpackage /version
162.1.167.1

My command can't execute twice:

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

The result (Database has drifted from its registered data-tier application.):

Publishing to database 'ContosoUniversity' on server '.'.
Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Error SQL0: Database has drifted from its registered data-tier application.

Time elapsed 0:00:13.35

Should I have to remove the /p:RegisterDataTierApplication=true argument on my second try? After I removed the /p:RegisterDataTierApplication=true argument, the command can run, but the /p:BlockWhenDriftDetected=True is not working any more. It will not block my publish, it just overwrite my schema, even I change the schema of the database. It's drifted.