Share via

Devops Deploy dacpac error after updating sql server

Brian Montesdeoca 5 Reputation points
2023-05-08T11:15:22.44+00:00

Hi, I've updated sql server from version 19 to 22 on a dedicated cloud server with Windows 2018 and after that I can't deploy dacpac from devops.

The error is generic: 

An unexpected failure occurred: The type initializer for 'Microsoft.Data.SqlClient.SqlConnection' threw an exception..

I've installed the sql package DAC FRAMEWORK from github because it was missing, checked the credentials, and db name, also tried few db names.

This is the debug log from the release deploy:

2023-05-08T10:54:49.4271995Z ##[debug]Evaluating condition for step: 'Deploy using : dacpac'
2023-05-08T10:54:49.4273803Z ##[debug]Evaluating: succeeded()
2023-05-08T10:54:49.4274347Z ##[debug]Evaluating succeeded:
2023-05-08T10:54:49.4275542Z ##[debug]=> True
2023-05-08T10:54:49.4276127Z ##[debug]Result: True
2023-05-08T10:54:49.4276905Z ##[section]Starting: Deploy using : dacpac
2023-05-08T10:54:49.4462419Z ==============================================================================
2023-05-08T10:54:49.4462685Z Task         : SQL Server database deploy
2023-05-08T10:54:49.4462905Z Description  : Deploy a SQL Server database using DACPAC or SQL scripts
2023-05-08T10:54:49.4463091Z Version      : 0.198.0
2023-05-08T10:54:49.4463260Z Author       : Microsoft Corporation
2023-05-08T10:54:49.4463530Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
2023-05-08T10:54:49.4463824Z ==============================================================================
2023-05-08T10:54:50.1267958Z ##[debug]VstsTaskSdk 0.8.2 commit 
2023-05-08T10:54:50.2498836Z ##[debug]Entering C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\Main.ps1.
2023-05-08T10:54:50.2567963Z ##[debug]Loading resource strings from: C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\Task.json
2023-05-08T10:54:50.2729279Z ##[debug]Loaded 6 strings.
2023-05-08T10:54:50.2757554Z ##[debug]SYSTEM_CULTURE: 'en-US'
2023-05-08T10:54:50.2773763Z ##[debug]Loading resource strings from: C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\Strings\resources.resjson\en-US\resources.resjson
2023-05-08T10:54:50.2893463Z ##[debug]Loaded 6 strings.
2023-05-08T10:54:50.3054436Z ##[debug]INPUT_TASKTYPE: 'dacpac'
2023-05-08T10:54:50.3071547Z ##[debug]INPUT_DACPACFILE: 'C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac'
2023-05-08T10:54:50.3089143Z ##[debug]INPUT_SQLFILE (empty)
2023-05-08T10:54:50.3129324Z ##[debug]INPUT_EXECUTEINTRANSACTION: 'false'
2023-05-08T10:54:50.3154588Z ##[debug] Converted to bool: False
2023-05-08T10:54:50.3171815Z ##[debug]INPUT_EXCLUSIVELOCK: 'false'
2023-05-08T10:54:50.3184779Z ##[debug] Converted to bool: False
2023-05-08T10:54:50.3200709Z ##[debug]INPUT_APPLOCKNAME (empty)
2023-05-08T10:54:50.3217328Z ##[debug]INPUT_INLINESQL (empty)
2023-05-08T10:54:50.3233645Z ##[debug]INPUT_TARGETMETHOD: 'server'
2023-05-08T10:54:50.3250153Z ##[debug]INPUT_SERVERNAME: 'localhost'
2023-05-08T10:54:50.3266346Z ##[debug]INPUT_DATABASENAME: 'MYDB'
2023-05-08T10:54:50.3282069Z ##[debug]INPUT_AUTHSCHEME: 'sqlServerAuthentication'
2023-05-08T10:54:50.3297801Z ##[debug]INPUT_SQLUSERNAME: 'SQLUSER'
2023-05-08T10:54:50.3315130Z ##[debug]INPUT_SQLPASSWORD: 'PASSWORD'
2023-05-08T10:54:50.3330805Z ##[debug]INPUT_CONNECTIONSTRING (empty)
2023-05-08T10:54:50.3346709Z ##[debug]INPUT_PUBLISHPROFILE (empty)
2023-05-08T10:54:50.3362388Z ##[debug]INPUT_ADDITIONALARGUMENTS (empty)
2023-05-08T10:54:50.3377876Z ##[debug]INPUT_ADDITIONALARGUMENTSSQL (empty)
2023-05-08T10:54:50.3395074Z ##[debug]Loading module from path 'C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psd1'.
2023-05-08T10:54:50.3470329Z ##[debug]Loading module from path 'C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\ps_modules\TaskModuleSqlUtility\TaskModuleSqlUtility.psm1'.
2023-05-08T10:54:50.3620895Z ##[debug]NonInteractive: False
2023-05-08T10:54:50.3910997Z ##[debug]Exporting function 'Invoke-DacpacDeployment'.
2023-05-08T10:54:50.3923899Z ##[debug]Exporting function 'Invoke-SqlQueryDeployment'.
2023-05-08T10:54:50.3937066Z ##[debug]Adding exceptions types.
2023-05-08T10:54:50.5145369Z ##[debug]Importing function 'Invoke-DacpacDeployment'.
2023-05-08T10:54:50.5193011Z ##[debug]Importing function 'Invoke-SqlQueryDeployment'.
2023-05-08T10:54:50.5733768Z ##[debug]Processed: ##vso[telemetry.publish area=SqlDacpacDeploymentOnMachineGroup;feature=SqlDacpacDeploymentOnMachineGroup]{"serverName": "49960de5880e8c687434170f6476605b8fe4aeb9a28632c7995cf3ba831d9763","databaseName": "ef4ed4c0441dae23adde06feec0016afc71bc20477c254d99f60ddd727623f21"}
2023-05-08T10:54:50.5735929Z ##[debug]Finding files with pattern C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac
2023-05-08T10:54:50.5812658Z ##[debug]Entering Find-VstsFiles.
2023-05-08T10:54:50.5900883Z ##[debug] LegacyPattern: 'C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac'
2023-05-08T10:54:50.6169234Z ##[debug]Entering Get-MatchingItems.
2023-05-08T10:54:50.6195170Z ##[debug] IncludePatterns: 'C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac'
2023-05-08T10:54:50.6231216Z ##[debug] ExcludePatterns: ''
2023-05-08T10:54:50.6261632Z ##[debug] IncludeFiles: 'True'
2023-05-08T10:54:50.6275331Z ##[debug] IncludeDirectories: 'False'
2023-05-08T10:54:50.6288818Z ##[debug] Force: 'False'
2023-05-08T10:54:50.7360865Z ##[debug]Path: C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac
2023-05-08T10:54:50.7393314Z ##[debug]Leaving Get-MatchingItems.
2023-05-08T10:54:50.7409790Z ##[debug]Total found: 1
2023-05-08T10:54:50.7424149Z ##[debug]Leaving Find-VstsFiles.
2023-05-08T10:54:50.7438306Z ##[debug]Matched files = C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac
2023-05-08T10:54:50.7477903Z ##[debug]Entering script SqlPackageOnTargetMachines.ps1
2023-05-08T10:54:50.8185164Z ##[debug]Sql Versions installed on machine 44B3945 as read from registry: 160 150 110 100 90
2023-05-08T10:54:50.8477260Z ##[debug]Sql Version Specific Root Dir for version 160 as read from registry: C:\Program Files\Microsoft SQL Server\160\
2023-05-08T10:54:50.8522149Z ##[debug]Dac Framework installed with SQL Version 160 found at C:\Program Files\Microsoft SQL Server\160\Dac\bin\SqlPackage.exe on machine 44B3945
2023-05-08T10:54:50.8537757Z ##[debug]Sql Version Specific Root Dir for version 160 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\160\
2023-05-08T10:54:50.8745134Z ##[debug]Dac Framework installed with SQL Version 160 found at C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe on machine 44B3945
2023-05-08T10:54:50.8807871Z ##[debug]vswhere.exe location:'C:\Program Files (x86)\Microsoft Visual Studio\Installer\vswhere.exe'
2023-05-08T10:54:50.9209318Z ##[debug]Latest Visual Studio (version: '0.0.0.0)' found at: ''
2023-05-08T10:54:50.9314351Z ##[debug]Visual Studio versions found on machine 44B3945 as read from registry: 14.0 12.0 8.0
2023-05-08T10:54:50.9364347Z ##[debug]Dac Framework (installed with Visual Studio) not found on machine 44B3945
2023-05-08T10:54:50.9470342Z ##[debug]File is C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac
2023-05-08T10:54:50.9582111Z ##[debug]Sqlpackage.exe arguments :  /SourceFile:"C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac" /Action:Publish /TargetServerName:"localhost" /TargetDatabaseName:"MYDB" /TargetUser:"SQLUSER" /TargetPassword:"PASSWORD" 
2023-05-08T10:54:50.9595005Z ##[debug] /SourceFile:"C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac" /Action:Publish /TargetServerName:"localhost" /TargetDatabaseName:"MYDB" /TargetUser:"SQLUSER" /TargetPassword:"PASSWORD" 
2023-05-08T10:54:50.9606397Z ##[debug]Executing command: C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe  /SourceFile:"C:\agent\_work\r6\a\_MYBLAZORAPP\drop\MYPROJECT.Database.dacpac" /Action:Publish /TargetServerName:"localhost" /TargetDatabaseName:"MYDB" /TargetUser:"SQLUSER" /TargetPassword:"PASSWORD" 
2023-05-08T10:54:51.6449344Z *** An unexpected failure occurred: The type initializer for 'Microsoft.Data.SqlClient.SqlConnection' threw an exception..
2023-05-08T10:54:51.6588838Z ##[debug]Deployment failed with error : Publishing to database 'MYDB' on server 'localhost'. 
2023-05-08T10:54:51.6599267Z ##[debug]Initializing deployment (Start) 
2023-05-08T10:54:51.6609714Z ##[debug]Initializing deployment (Failed) 
2023-05-08T10:54:51.6618845Z ##[debug]Time elapsed 0:00:00.34 
2023-05-08T10:54:51.6861249Z ##[debug]
2023-05-08T10:54:51.6956579Z ##[debug]Write-Exception error:
2023-05-08T10:54:51.6986132Z ##[debug]Publishing to database 'MYDB' on server 'localhost'. 
2023-05-08T10:54:51.6998025Z ##[debug]Initializing deployment (Start) 
2023-05-08T10:54:51.7006011Z ##[debug]Initializing deployment (Failed) 
2023-05-08T10:54:51.7013766Z ##[debug]Time elapsed 0:00:00.34 
2023-05-08T10:54:51.7021150Z ##[debug]
2023-05-08T10:54:51.7045275Z ##[debug]Caught exception from task script.
2023-05-08T10:54:51.7064696Z ##[debug]Error record:
2023-05-08T10:54:51.7725670Z ##[debug]Publishing to database 'MYDB' on server 'localhost'. 
2023-05-08T10:54:51.7735102Z ##[debug]Initializing deployment (Start) 
2023-05-08T10:54:51.7745257Z ##[debug]Initializing deployment (Failed) 
2023-05-08T10:54:51.7755066Z ##[debug]Time elapsed 0:00:00.34 
2023-05-08T10:54:51.7765022Z ##[debug]
2023-05-08T10:54:51.7772990Z ##[debug]At C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\ps_modules\TaskModuleSqlUtility\SqlPackageOnTargetMachines.ps1:567 char:10
2023-05-08T10:54:51.7779999Z ##[debug]+          throw  $result
2023-05-08T10:54:51.7788545Z ##[debug]+          ~~~~~~~~~~~~~~
2023-05-08T10:54:51.7796826Z ##[debug]    + CategoryInfo          : OperationStopped: (Publishing to d...d 0:00:00.34 
2023-05-08T10:54:51.7804118Z ##[debug]:String) [], RuntimeException
2023-05-08T10:54:51.7811824Z ##[debug]    + FullyQualifiedErrorId : Publishing to database 'MYDB' on server 'localhost'. 
2023-05-08T10:54:51.7819407Z ##[debug]Initializing deployment (Start) 
2023-05-08T10:54:51.7827124Z ##[debug]Initializing deployment (Failed) 
2023-05-08T10:54:51.7834579Z ##[debug]Time elapsed 0:00:00.34 
2023-05-08T10:54:51.7841856Z ##[debug]
2023-05-08T10:54:51.7849585Z ##[debug] 
2023-05-08T10:54:51.7862190Z ##[debug]Script stack trace:
2023-05-08T10:54:51.7887329Z ##[debug]at ExecuteCommand, C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\ps_modules\TaskModuleSqlUtility\SqlPackageOnTargetMachines.ps1: line 567
2023-05-08T10:54:51.7894699Z ##[debug]at Invoke-DacpacDeployment, C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\ps_modules\TaskModuleSqlUtility\SqlPackageOnTargetMachines.ps1: line 534
2023-05-08T10:54:51.7902399Z ##[debug]at <ScriptBlock>, C:\agent\_work\_tasks\SqlDacpacDeploymentOnMachineGroup_4b506f7f-720f-47bb-bf21-029bac6a690d\0.198.0\Main.ps1: line 124
2023-05-08T10:54:51.7910055Z ##[debug]at <ScriptBlock>, <No file>: line 1
2023-05-08T10:54:51.7917776Z ##[debug]at <ScriptBlock>, <No file>: line 22
2023-05-08T10:54:51.7925477Z ##[debug]at <ScriptBlock>, <No file>: line 18
2023-05-08T10:54:51.7932918Z ##[debug]at <ScriptBlock>, <No file>: line 1
2023-05-08T10:54:51.7945638Z ##[debug]Exception:
2023-05-08T10:54:51.7973222Z ##[debug]System.Management.Automation.RuntimeException: Publishing to database 'MYDB' on server 'localhost'. 
2023-05-08T10:54:51.7984753Z ##[debug]Initializing deployment (Start) 
2023-05-08T10:54:51.7992496Z ##[debug]Initializing deployment (Failed) 
2023-05-08T10:54:51.8000056Z ##[debug]Time elapsed 0:00:00.34 
2023-05-08T10:54:51.8007717Z ##[debug]
2023-05-08T10:54:51.8176738Z ##[error]Publishing to database 'MYDB' on server 'localhost'. 
Initializing deployment (Start) 
Initializing deployment (Failed) 
Time elapsed 0:00:00.34 

2023-05-08T10:54:51.8183467Z ##[debug]Processed: ##vso[task.logissue type=error]Publishing to database 'MYDB' on server 'localhost'. %0D%0AInitializing deployment (Start) %0D%0AInitializing deployment (Failed) %0D%0ATime elapsed 0:00:00.34 %0D%0A
2023-05-08T10:54:51.8185309Z ##[debug]Processed: ##vso[task.complete result=Failed]
2023-05-08T10:54:51.8378078Z ##[section]Finishing: Deploy using : dacpac

Thank you!

Azure SQL Database
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,585 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Brian Montesdeoca 5 Reputation points
    2023-05-08T17:24:12.3166667+00:00

    Problem solved!. After hours debugging all devops ps scripts, I found the problem: .Net 5.

    I Updated .net to 6 removing the old version and the error was gone, but, after that I had problems with the authentication between the devops agent and SQL Server, which with the parameter /TargetTrustServerCertificate:True ended the issue.

    I did the following steps to ensure all was working fine:

    • Uninstalled .NET 5
    • Installed .NET6
    • Installed SQL Package from dotnet tool install -g microsoft.sqlpackage
    • Ensured that the SQL Dac folder was created (C:\Program Files\Microsoft SQL Server\160*DAC\Bin*), If not, can be downloaded from Here.
    • Added C:\Program Files\Microsoft SQL Server\160*DAC\Bin* to PATH in environment variables.
    • Tested the credentials from a .UDL file. I created a new SQL user just for this project.
    • Added /TargetTrustServerCertificate:True in the dacpac deploy job in DevOps

    Now is all sorted and working as it should. :D

    1 person found this answer helpful.
  2. Bhadiyadra, Manoj 0 Reputation points
    2024-03-07T06:51:47.28+00:00

    User's image

    by executing dacpac on powershellcommand prompt I am getting below error. anyone faced such error in past?