Publish-DbaDacPackage throws exception calling ImportBacpac, Microsoft.Isam.Esent.Interop.EsentInstanceUnavailableException

Rodric75 commented 2 years ago

Verified issue does not already exist?

Yes

What error did you receive?

System.Management.Automation.MethodInvocationException: Exception calling "ImportBacpac" with "4" argument(s): "This instance cannot be used because it encountered a fatal error" ---> Microsoft.Isam.Esent.Interop.EsentInstanceUnavailableException: This instance cannot be used because it encountered a fatal error at Microsoft.Isam.Esent.Interop.Api.JetEndSession(JET_SESID sesid, EndSessionGrbit grbit) at Microsoft.Isam.Esent.Interop.Session.ReleaseResource() at Microsoft.Isam.Esent.Interop.EsentResource.Dispose(Boolean isDisposing) at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.EseManager.Dispose(String newModelFilename) at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.EseManager..ctor(ModelCollation collation, StorageSchema storageSchema, String modelFileName, Boolean createNewModel, List1 elementTables, List1 annotationTables) at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore.ConstructModelStore(ModelCollation collation, String modelFileName, Boolean createNewModel) at Microsoft.Data.Tools.Schema.SchemaModel.ModelStore..ctor(SqlPlatforms platform, ModelCollation collation, ModelSchema modelSchema, String modelFileName, ModelStorageType modelStorageType, Boolean storeSourceCodePositionAnnotations) at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel..ctor(SqlPlatforms platform, ModelCollation collation, ModelSchema storeSchema, String modelFileName, ErrorManager errorManager, ModelStorageType modelStorageType, Boolean storeSourceCodePositionAnnotations) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModel..ctor(SqlDatabaseSchemaProvider databaseSchemaProvider, SqlPlatforms platform, ModelCollation collation, String modelFileName, ErrorManager errorManager, Boolean validationEnabled, Boolean initializeBuiltIns, Boolean validateCasingOnIdentifiers, Boolean convertCrossDbRefToWarning, Boolean isMsdb, ModelStorageType modelStorageType, Boolean storeSourceCodePositionAnnotations) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlSchemaModelConstructor.ConstructServiceImplementation() at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.ModelContentsState.InitializeModel(DataSchemaModelHeader headerData) at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.DataSchemaModelContentsState.ProcessStartElement() at Microsoft.Data.Tools.Schema.SchemaModel.XmlDeserializerEngine.ReadData(DeserializerState initialState, Boolean keepCurrentReaderPosition) at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.DeserializeXml(TextReader input, ErrorManager errors, String source, Action3 constructorParametersSetter) at Microsoft.Data.Tools.Schema.SchemaModel.DataSchemaModel.DeserializePackage(SqlPackage package, ErrorManager errors, Action3 constructorParametersSetter) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.LoadModel(ErrorManager errors) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointPackage.OnLoad(ErrorManager errors, DeploymentEngineContext context) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.PrepareModels() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.InitializePlanGeneratator() at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateController(Action1 msgHandler) at Microsoft.SqlServer.Dac.DacServices.CreateController(SqlDeployment deploymentEngine, ErrorManager errorManager) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<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.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, Action3 reportPlanOperation, Boolean executePlan) at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable1 cancellationToken) at CallSite.Target(Closure , CallSite , Object , Object , Object , Object , Object ) --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception) at System.Management.Automation.Interpreter.ActionCallInstruction2.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Steps to Reproduce

$databaseName = "Incident1031"
$AzureSQLServerURL = "tcp:redacted.database.windows.net,1433" 
$this.Creds = Get-AutomationPSCredential -Name "SQLConnectionDev"
$this.SqlInstance = Connect-DbaInstance -SqlInstance $AzureSQLServerURL -SqlCredential $Creds 
$backupLocation = "C:\Users\Client\Temp\Incident1031.dacpac"

Publish-DbaDacPackage -Type Bacpac -SqlInstance $this.SqlInstance -Database $databaseName -Path $backupLocation -SqlCredential $this.Creds -EnableException

Are you running the latest release?

Yes

Other details or mentions

Same code runs successfully from local powershell via Terminal, but fails if executed from Azure Automation.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Option for Azure Automation not found.

Name Value

PSVersion 5.1.15063.726
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.15063.726
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

.NET Framework Version

.NET Framework 4.7.3163.0

andreasjordan commented 1 year ago

@Rodric75 - is this still a problem with the current version of dbatools?

@potatoqualitee - do you have any ideas?

potatoqualitee commented 1 year ago

a while back @SQLDBAWithABeard fixed a similar issue with azure automation. i have never used azure automation but im going to set something up and add a test for it. @Rodric75 im going to close this and do my best to ensure that commands work in azure automation with dbatools 2.0 which is due within the next month. if you continue to have issues once dbatools 2.0 has been published, please reopen or recomment.

dataplat / dbatools