SqlAzureDacpacDeployment fails on Hosted Windows 2019 agent

[IvanAlekseev]

Required Information

Question, Bug, or Feature?
Type: Bug

Enter Task Name: SqlAzureDacpacDeployment

Environment

• Server - Azure Pipelines
• Agent - Hosted:
  • If using Hosted agent, provide agent queue name: Hosted Windows 2019 with VS2019

Issue Description

Looks like a bug was introduced in one of the recent versions of sqlpackage.exe Publish is working on our private agents with the older version of sqlpackage but fails on most recent hosted Windows 2019. With the more recent version the publish command it still gets some bits from connection string specified in publish profile.

Here is the command line from SqlAzureDacpacDeployment task:

C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"d:\Database.dacpac" /TargetServerName:"*****" /TargetDatabaseName:"*****" /TargetUser:"***" /TargetPassword:"********" /Profile:"d:\Database.publish.xml" /p:AllowIncompatiblePlatform=true 

It was working when the sqlpackage.exe was coming from the folder: C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe

Error I am getting:

Could not deploy package.
Unable to connect to target server '*****'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
Windows logins are not supported in this version of SQL Server.

If I remove TargetConnectionString from the publish profile, or change Integrated Security to False, then publish command works perfectly.

We did a comparison of decompiled versions of sqlpackage 140 and 150 and looks like for some reason builder.IntegratedSecurity is not set to false anymore when username and password are specified

[vincent1173]

@IvanAlekseev , thanks for your patience. We have directed this issue to SQL package team. Will updaye you once there is any update. In the meanwhile, please get unblocked with Hosted VS2017 pool.

[kisantia]

@IvanAlekseev Yes it appears that this line was removed. It should have been a no-op, but looks like we missed something. Can you give us the profile so that we can have the exact command line to repro and fix this?

[kisantia]

@IvanAlekseev we have fixed the bug. The fix will be in the DacFx 18.3 release in a month or two.

[zarochi]

Unfortunately, I ran into this same issue on the Hosted VS2017 pool. It looks like it has had its SQLpackage.exe updated too. I removed our company specific stuff, but this is the path it's using when I use the DacPac task on the Hosted VS2017 pool: 

"C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"D:\a\1\s\Pipelines\filename.dacpac" /TargetServerName:"servername.database.windows.net" /TargetDatabaseName:"DBName" /TargetTimeout:120

Any better pools/workarounds you would suggest? I can install SQLpackage.exe on one of my pools, but I'd rather use a hosted one for this step.

Update: I found the pool "Hosted" still works because it is an older pool (it uses version 140), but I hit the same error here as the OP because the DB version is 150 :-/

[IvanAlekseev]

@zarochi as a workaround we are now running powershell script to modify the profile and change Integrated Security to False:

$FileName = "$(System.DefaultWorkingDirectory)/..../Database.publish.xml"

((Get-Content -path $FileName -Raw) -replace 'Integrated Security=True','Integrated Security=False') | Set-Content -Path $FileName

[zarochi]

@IvanAlekseev are you omitting something in the .... part of the path? I tried running the same thing, but it wasn't able to find the XML file. I also tried ../.. to no avail. I'll keep poking the file pathing because this seems promising. I feel like maybe my default working directory is different for some reason (I'm still a little new to pipelines in devops).

Clever solution!

Edit: I think I have a better handle on this now after a little research. Are you shipping an XML file with your dacpac? I'm thinking my issue is that I need to make that XML file since I don't have one.

Edit 2: Ok, I think I figured out where our process differs. I only have a dacpac file because I'm just exporting it directly from the DB. Am I correct in assuming you use the PublishProfile option in the YAML file? I'm tempted to make a publish XML to use for this.

Edit 3: I created the XML file, but I still have the same issue. XML is below; I'm filling in PLACEHOLDER as an earlier step in the pipeline:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <IncludeCompositeObjects>True</IncludeCompositeObjects>
        <TargetDatabaseName>PLACEHOLDER</TargetDatabaseName>
        <DeployScriptFileName>model.sql</DeployScriptFileName>
        <TargetConnectionString>Server=tcp:PLACEHOLDER.database.windows.net,1433;Initial Catalog=PLACEHOLDER;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=90;Integrated Security=False</TargetConnectionString>
        <ProfileVersionNumber>1</ProfileVersionNumber>
    </PropertyGroup>
</Project>

[zarochi]

Ok, I finally got something working, but I pulled the dacpac steps out into powershell to have direct control over my connection string. For anyone who wants to do it this way this is my step:

- powershell: '& "$(sql-package)" /Action:Publish /SourceFile:"$(Build.SourcesDirectory)\$(dacpac)" /TargetConnectionString:$env:connectionstring'
      name: Deploy

I'm setting the connectionstring as a variable on an earlier line, but I have to reference it as $env:connection string because my connection string is made up of other variables I pulled from keyvault earlier on. Hopefully this helps someone in the same boat I was in. I ended up transitioning to my build server to have better control over the firewall, but this might work on a hosted pool too.

[IvanAlekseev]

@zarochi yes I omitted some stuff from the path to remove our artifacts name. We are using VS .sqlproj projects and they have publish profiles to set some publish settings. I am using classic releases, that's the yml it shows for a publish task (I removed some names here as well)

- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL Publish'
  inputs:
    azureSubscription: '*****'
    ServerName: '$(database_server_name)'
    DatabaseName: *****
    SqlUsername: '$(database_admin_username)'
    SqlPassword: '$(database_admin_password)'
    DacpacFile: '$(System.DefaultWorkingDirectory)/_db/Database.dacpac'
    PublishProfile: '$(System.DefaultWorkingDirectory)/_db/Database.publish.xml'
    AdditionalArguments: '/p:AllowIncompatiblePlatform=true'

[IvanAlekseev]

@kisantia hello, I see that new version of sql package got released, but I can't find any mention of this bugfix in release notes. Is it there?

[kisantia]

@IvanAlekseev Yes the fix is there. We will add it to the release notes. Thanks!