SqlAzureDacpacDeployment connection string authentication fails for SqlTask deployment

[richardswinbank]

Note

Issues in this repo are for tracking bugs, feature requests and questions for the tasks in this repo

For a list:
https://github.com/Microsoft/azure-pipelines-tasks/tree/master/Tasks

If you have an issue or request for the Azure Pipelines service, use developer community instead:

https://developercommunity.visualstudio.com/spaces/21/index.html )

Required Information

Entering this information will route you directly to the right team and expedite traction.

Question, Bug, or Feature?
Type: Bug

Enter Task Name: SqlAzureDacpacDeployment

list here (V# not needed):
https://github.com/Microsoft/azure-pipelines-tasks/tree/master/Tasks

Environment

• Server - Azure Pipelines or TFS on-premises?

  • If using TFS on-premises, provide the version:

  • If using Azure Pipelines, provide the account name, team project name, build definition name/build number: boomin, Boomin, data-platform.20201006.2

• Agent - Hosted or Private:

  • If using Hosted agent, provide agent queue name: Azure Pipelines

  • If using private agent, provide the OS of the machine running the agent and the agent version:

Issue Description

I'm trying to use the task to execute a SQL script file against an Azure SQL database, like this:

- task: SqlAzureDacpacDeployment@1
  displayName: Deploy process config to DataPlatformAdmin DB
  inputs:
    azureSubscription: $(ServiceConnectionName)
    AuthenticationType: connectionString
    ConnectionString: 'Initial Catalog=DataPlatformAdmin;$(ASQL-Administrator-ConnectionString)'
    deployType: SqlTask
    SqlFile: $(Pipeline.Workspace)/DataPlatformAdminDb/ProcessList.sql

The task fails with the message "Login failed for user 'dataadmin'". This task follows immediately after another SqlAzureDacpacDeployment@1 task in the same job -- the prior task uses the same service connection & connection string to deploy a DACPAC, which runs successfully. The variable $(ASQL-Administrator-ConnectionString) is provided by an AzureKeyVault task earlier in the pipeline.

I notice that internally the DACPAC deployment uses SqlPackage.exe but the that SQL script uses Powershell's Invoke-SqlCmd - I wonder if there's an issue with the preparation of the cmdlet call? I see that the task always makes an initial connection check via Invoke-SqlCmd; these fail on both occasions but in the DACPAC deployment the subsequent SqlPackage execution succeeds. FYI, I initially asked this question at Microsoft Q&A but wasn't able to find a resolution.

Task logs

I've included two task logs:

• sqlscript_fail.txt relates to the task run which fails
• dacpac_success.txt is the log from the previous, successful task. In this log you can see that Invoke-SqlCmd fails but that SqlPackage.exe succeeds

tasklogs.zip

I've changed the server & database names in the task log but that's all.

Troubleshooting

Checkout how to troubleshoot failures and collect debug logs: https://docs.microsoft.com/en-us/vsts/build-release/actions/troubleshooting

Error logs

2020-10-06T10:38:15.9720592Z ##[debug]Caught exception from task script.
2020-10-06T10:38:15.9771620Z ##[debug]Error record:
2020-10-06T10:38:16.1123518Z ##[debug]Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-10-06T10:38:16.1175965Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.171.3\DeploySqlAzure.ps1:213 char:5
2020-10-06T10:38:16.1195299Z ##[debug]+     throw $errorMessage
2020-10-06T10:38:16.1214674Z ##[debug]+     ~~~~~~~~~~~~~~~~~~~
2020-10-06T10:38:16.1294593Z ##[debug]    + CategoryInfo          : OperationStopped: (Login failed fo...roubleshooting-:String) [], RuntimeException
2020-10-06T10:38:16.1314736Z ##[debug]    + FullyQualifiedErrorId : Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.    ms/sqlazuredeployreadme#troubleshooting-
2020-10-06T10:38:16.1424898Z ##[debug] 
2020-10-06T10:38:16.1459469Z ##[debug]Script stack trace:
2020-10-06T10:38:16.1525432Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.171.3\DeploySqlAzure.ps1: line 213
2020-10-06T10:38:16.1556461Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-10-06T10:38:16.1579868Z ##[debug]at <ScriptBlock>, <No file>: line 22
2020-10-06T10:38:16.1632252Z ##[debug]at <ScriptBlock>, <No file>: line 18
2020-10-06T10:38:16.1654731Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-10-06T10:38:16.1691804Z ##[debug]Exception:
2020-10-06T10:38:16.1757872Z ##[debug]System.Management.Automation.RuntimeException: Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-10-06T10:38:16.2075019Z ##[error]Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-10-06T10:38:16.2093736Z ##[debug]Processed: ##vso[task.logissue type=error]Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-10-06T10:38:16.2096436Z ##[debug]Processed: ##vso[task.complete result=Failed]

[richardswinbank]

Digging into this a bit further, I've since been able to get the task to work using a connection string with a different password. The original password contains a $ character which is causing PowerShell some problems - I think it (and possible other characters) need escaping when Invoke-SqlCmd is built.

[AmrutaKawade]

This is fixed and will be deployed after 4 weeks from now.