Azure-pipelines-tasks: Azure SQL Database Deployment Task `Active Directory - Password` Bug

Created on 28 Jan 2020  路  12Comments  路  Source: microsoft/azure-pipelines-tasks

Required Information

Type: Bug

Enter Task Name: Azure SQL Database Deployment Task

Environment

  • Server - Azure Pipelines Hosted windows-2019

Issue Description

When using the Azure SQL Database Deployment task with the Active Directory - Password Authentication Type, the task will not complete if there is a dollar sign ($) in the password.

This issue is similar but ultimately different than the issue reported here: #12012

Steps to Reproduce

  • Create an Active Directory user with a dollar sign ($) in password
  • Create another Active Directory user with no special chars in password
  • Grant permissions to both accounts to Azure SQL Server
  • Create Azure SQL Database Deployment Task
  • Select Active Directory - Password Authentication Type
  • image
  • Test Both user accounts with Task
  • Observe failure when user with dollar sign ($) in password is used
Release
Source
picture ShawnMcGough
👍1

Most helpful comment

I went ahead and looked into the source code. I've discovered that the password is not being escaped for all authentication methods. The password is only escaped (on line 260) when authentication type is server:
https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L253-L260

When authentication type is aadAuthenticationPassword or aadAuthenticationIntegrated, the Get-AADAuthenticationConnectionString function is called:
https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L277-L282

The Get-AADAuthenticationConnectionString function builds the connection string without escaping password - allowing for special chars to be in the connection string:
https://github.com/microsoft/azure-pipelines-tasks/blob/961d90a3b8d4390ea62066ff601a0c3fbc8ea538/Tasks/SqlAzureDacpacDeploymentV1/Utility.ps1#L249-L251

Probable Solution:

Ensure the password is escaped for all authentication types by using EscapeSpecialChars function at the top of the function.
This can be achieved by moving line 260 to line 252, above if statement.

https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L252-L261

I'm not in a position to test this myself but hopefully this can jump-start a fix for this issue.

picture ShawnMcGough on 21 Aug 2020
🚀1 ❤1

All 12 comments

@ShawnMcGough: Though we use escaped SQL password for constructing the connection string, the end password value will not contain the escape back-tick(`). To be sure of that, you can add a dummy password with special characters(like $) as a plain string instead of a secret variable and inspect the logs to take a look at the final command being run. You will notice the right password value getting passed on.

You can run into ManagedBatchParser exception when user does not have sufficient permissions as well. To exclude that case, can u try connecting to the SQL server from SSMS using "Active Directory - Password" and see if that works for you?

image

You can set system.debug variable to true and create a release for getting more verbose logging. You can also add "/Diagnostics:true" in the additional arguments for more detailed exception trace. Please share these logs with us, if user permissions is not the issue.

chperich picture chperich on 13 Feb 2020

@chperich Thank you for looking into this issue.
I believe my original post was not accurate on the root cause. I've now updated my original post. I've confirmed a bug by doing the following.

  • Verified account access via SSMS with dollar sign ($) in password (like your screen shot shows).
  • Created debug.sql file, which simply selects date from database.
  • Verified still failing when dollar sign ($) in password.
  • Generate new account password to remove the dollar sign ($). Verified successful.

The steps are easily reproduced - create an Active Directory account with & without a dollar sign ($).

It's possible this line from the failure log is the issue in that the dollar sign is not escaped an powershell is parsing it:

2020-02-13T16:26:34.5374197Z ##[debug]Run-InlineSql -authenticationType aadAuthenticationPassword -serverName ********.database.windows.net -databaseName TestDatabase -sqlUserName [email protected] -sqlPassword abc$def -sqlInline "select getdate()" -connectionString  -ErrorVariable errors -ConnectionTimeout 120 | Out-String

The success log:

2020-02-13T16:37:29.0749340Z ##[debug]Reaching SqlServer to check connection by running Invoke-SqlCmd
2020-02-13T16:37:29.0804899Z ##[debug]RunlineSql -authenticationType aadAuthenticationPassword -serverName ********.database.windows.net -databaseName TestDatabase -sqlUserName [email protected] -sqlPassword abcdef -sqlInline "select getdate()" -connectionString  -ErrorVariable errors -ConnectionTimeout 120 | Out-String
2020-02-13T16:37:29.0972947Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp8F04.tmp
2020-02-13T16:37:29.1085176Z Invoke-Sqlcmd -connectionString "Data Source=********.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=abcdef"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp8F04.tmp" 
2020-02-13T16:37:32.9483662Z ##[debug]Removing File C:\Users\VssAdministrator\AppData\Local\Temp\tmp8F04.tmp
2020-02-13T16:37:32.9557196Z ##[debug]Message To Parse: 
2020-02-13T16:37:32.9569084Z ##[debug]Column1             
2020-02-13T16:37:32.9581279Z ##[debug]-------             
2020-02-13T16:37:32.9593305Z ##[debug]2/13/2020 4:37:32 PM
2020-02-13T16:37:32.9605950Z ##[debug]
2020-02-13T16:37:32.9618092Z ##[debug]
2020-02-13T16:37:32.9630680Z ##[debug]
2020-02-13T16:37:32.9737950Z ##[debug]
2020-02-13T16:37:32.9810395Z ##[debug]Sql configured firewall wait time is invalid. So, setting it to defaul : 10
2020-02-13T16:37:32.9829790Z ##[debug]Sql firewall configured wait time : 10
2020-02-13T16:37:42.9946399Z ##[debug]filePath = Find-VstsFiles LegacyPattern D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:37:43.0080978Z ##[debug]Entering Find-VstsFiles.
2020-02-13T16:37:43.0346872Z ##[debug] LiteralDirectory: 'LegacyPattern'
2020-02-13T16:37:43.0363086Z ##[debug] LegacyPattern: 'D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-02-13T16:37:43.1305415Z ##[debug]Entering Get-MatchingItems.
2020-02-13T16:37:43.1339374Z ##[debug] IncludePatterns: 'D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-02-13T16:37:43.1381980Z ##[debug] ExcludePatterns: ''
2020-02-13T16:37:43.1423517Z ##[debug] IncludeFiles: 'True'
2020-02-13T16:37:43.1441683Z ##[debug] IncludeDirectories: 'False'
2020-02-13T16:37:43.1460302Z ##[debug] Force: 'False'
2020-02-13T16:37:43.3623984Z ##[debug]Path: D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:37:43.3624176Z ##[debug]Leaving Get-MatchingItems.
2020-02-13T16:37:43.3624287Z ##[debug]Total found: 1
2020-02-13T16:37:43.3624387Z ##[debug]Leaving Find-VstsFiles.
2020-02-13T16:37:43.3624457Z Sql file: D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:37:43.3624594Z Invoke-Sqlcmd -connectionString "Data Source=********.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=abcdef"  -Inputfile "D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql" 
2020-02-13T16:37:43.4047505Z ##[debug]TEST SQL FILE
2020-02-13T16:37:43.4086156Z 
2020-02-13T16:37:43.4105207Z ##[debug]No Firewall Rule was added
2020-02-13T16:37:43.4179459Z ##[debug]Leaving script DeploySqlAzure.ps1
2020-02-13T16:37:43.4184278Z Column1             
2020-02-13T16:37:43.4184582Z -------             
2020-02-13T16:37:43.4184801Z 2/13/2020 4:37:43 PM
2020-02-13T16:37:43.4184967Z 
2020-02-13T16:37:43.4185108Z 
2020-02-13T16:37:43.4955874Z ##[section]Finishing: Azure SQL SqlTask

The failure log:

2020-02-13T16:26:34.5358429Z ##[debug]Reaching SqlServer to check connection by running Invoke-SqlCmd
2020-02-13T16:26:34.5374197Z ##[debug]Run-InlineSql -authenticationType aadAuthenticationPassword -serverName ********.database.windows.net -databaseName TestDatabase -sqlUserName [email protected] -sqlPassword abc$def -sqlInline "select getdate()" -connectionString  -ErrorVariable errors -ConnectionTimeout 120 | Out-String
2020-02-13T16:26:34.5485110Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmpD422.tmp
2020-02-13T16:26:34.5649114Z Invoke-Sqlcmd -connectionString "Data Source=********.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=abc$def"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmpD422.tmp" 
2020-02-13T16:26:38.8414376Z ##[debug]Removing File C:\Users\VssAdministrator\AppData\Local\Temp\tmpD422.tmp
2020-02-13T16:26:38.8477101Z ##[debug]Failed to reach SQL server ********.database.windows.net. One or more errors occurred.
2020-02-13T16:26:38.8557402Z ##[debug]Error Message : System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2020-02-13T16:26:38.8573727Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2020-02-13T16:26:38.8585047Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8598520Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8609751Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8623978Z ##[debug]Message To Parse: System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2020-02-13T16:26:38.8634872Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2020-02-13T16:26:38.8644978Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8655154Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8665057Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-02-13T16:26:38.8744158Z ##[debug]
2020-02-13T16:26:38.8800690Z ##[debug]Sql configured firewall wait time is invalid. So, setting it to defaul : 10
2020-02-13T16:26:38.8815811Z ##[debug]Sql firewall configured wait time : 10
2020-02-13T16:26:48.9051337Z ##[debug]filePath = Find-VstsFiles LegacyPattern D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:26:48.9174715Z ##[debug]Entering Find-VstsFiles.
2020-02-13T16:26:48.9281899Z ##[debug] LiteralDirectory: 'LegacyPattern'
2020-02-13T16:26:48.9298755Z ##[debug] LegacyPattern: 'D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-02-13T16:26:49.0066049Z ##[debug]Entering Get-MatchingItems.
2020-02-13T16:26:49.0088753Z ##[debug] IncludePatterns: 'D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-02-13T16:26:49.0117078Z ##[debug] ExcludePatterns: ''
2020-02-13T16:26:49.0159977Z ##[debug] IncludeFiles: 'True'
2020-02-13T16:26:49.0174125Z ##[debug] IncludeDirectories: 'False'
2020-02-13T16:26:49.0188931Z ##[debug] Force: 'False'
2020-02-13T16:26:49.2652358Z ##[debug]Path: D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:26:49.2681105Z ##[debug]Leaving Get-MatchingItems.
2020-02-13T16:26:49.2724707Z ##[debug]Total found: 1
2020-02-13T16:26:49.2758942Z ##[debug]Leaving Find-VstsFiles.
2020-02-13T16:26:49.2921243Z Sql file: D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-02-13T16:26:49.2921431Z Invoke-Sqlcmd -connectionString "Data Source=********.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=abc$def"  -Inputfile "D:\a\r1\a\drop\drop\IaCSetup\security\debug.sql" 
2020-02-13T16:26:49.9261946Z ##[debug]No Firewall Rule was added
2020-02-13T16:26:49.9332031Z ##[debug]Caught exception from task script.
2020-02-13T16:26:49.9361315Z ##[debug]Error record:
2020-02-13T16:26:50.0000425Z ##[debug]One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-02-13T16:26:50.0012132Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\DeploySqlAzure.ps1:213 char:5
2020-02-13T16:26:50.0023213Z ##[debug]+     throw $errorMessage
2020-02-13T16:26:50.0034847Z ##[debug]+     ~~~~~~~~~~~~~~~~~~~
2020-02-13T16:26:50.0047338Z ##[debug]    + CategoryInfo          : OperationStopped: (One or more err...roubleshooting-:String) [], RuntimeException
2020-02-13T16:26:50.0058743Z ##[debug]    + FullyQualifiedErrorId : One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sql    azuredeployreadme#troubleshooting-
2020-02-13T16:26:50.0069703Z ##[debug] 
2020-02-13T16:26:50.0086183Z ##[debug]Script stack trace:
2020-02-13T16:26:50.0114663Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\DeploySqlAzure.ps1: line 213
2020-02-13T16:26:50.0126832Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-02-13T16:26:50.0138653Z ##[debug]at <ScriptBlock>, <No file>: line 22
2020-02-13T16:26:50.0149740Z ##[debug]at <ScriptBlock>, <No file>: line 18
2020-02-13T16:26:50.0159788Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-02-13T16:26:50.0176705Z ##[debug]Exception:
2020-02-13T16:26:50.0211608Z ##[debug]System.Management.Automation.RuntimeException: One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-02-13T16:26:50.0435886Z ##[error]One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-02-13T16:26:50.0444843Z ##[debug]Processed: ##vso[task.logissue type=error]One or more errors occurred.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-02-13T16:26:50.0445864Z ##[debug]Processed: ##vso[task.complete result=Failed]
2020-02-13T16:26:50.1314141Z ##[section]Finishing: Azure SQL SqlTask
ShawnMcGough picture ShawnMcGough on 13 Feb 2020

@ShawnMcGough

Can you please run the task with -verbose as an additional argument to the task. It will share the exact cause of the failure. Meanwhile, I will try out the task with $ as one of the character of the password.

Ajay-MS picture Ajay-MS on 12 Mar 2020

@Ajay-MS
Here is the log with -verbose. The stack trace seemingly points to ADALNativeWrapper. 

2020-03-12T15:07:56.1360329Z ##[debug]Run-InlineSql -authenticationType aadAuthenticationPassword -serverName ******.database.windows.net -databaseName TestDatabase -sqlUserName [email protected] -sqlPassword *** -sqlInline "select getdate()" -connectionString  -ErrorVariable errors -ConnectionTimeout 120 | Out-String
2020-03-12T15:07:56.1480108Z Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmp14A4.tmp
2020-03-12T15:07:56.1688943Z Invoke-Sqlcmd -connectionString "Data Source=******.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=***"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp14A4.tmp" 
2020-03-12T15:08:00.0049980Z ##[debug]Removing File C:\Users\VssAdministrator\AppData\Local\Temp\tmp14A4.tmp
2020-03-12T15:08:00.0144185Z ##[debug]Failed to reach SQL server ******.database.windows.net. One or more errors occurred.
2020-03-12T15:08:00.0342942Z ##[debug]Error Message : System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2020-03-12T15:08:00.0352502Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2020-03-12T15:08:00.0367921Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0384259Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0399097Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0421760Z ##[debug]Message To Parse: System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: One or more errors occurred.
2020-03-12T15:08:00.0437163Z ##[debug]   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
2020-03-12T15:08:00.0453739Z ##[debug]   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0468686Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0487117Z ##[debug]   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2020-03-12T15:08:00.0586392Z ##[debug]
2020-03-12T15:08:00.0674588Z ##[debug]Sql configured firewall wait time is invalid. So, setting it to defaul : 10
2020-03-12T15:08:00.0695004Z ##[debug]Sql firewall configured wait time : 10
2020-03-12T15:08:10.1208968Z ##[debug]filePath = Find-VstsFiles LegacyPattern d:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-03-12T15:08:10.1382185Z ##[debug]Entering Find-VstsFiles.
2020-03-12T15:08:10.1530682Z ##[debug] LiteralDirectory: 'LegacyPattern'
2020-03-12T15:08:10.1553239Z ##[debug] LegacyPattern: 'd:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-03-12T15:08:10.2158569Z ##[debug]Entering Get-MatchingItems.
2020-03-12T15:08:10.2203845Z ##[debug] IncludePatterns: 'd:\a\r1\a\drop\drop\IaCSetup\security\debug.sql'
2020-03-12T15:08:10.2249068Z ##[debug] ExcludePatterns: ''
2020-03-12T15:08:10.2293544Z ##[debug] IncludeFiles: 'True'
2020-03-12T15:08:10.2313063Z ##[debug] IncludeDirectories: 'False'
2020-03-12T15:08:10.2333084Z ##[debug] Force: 'False'
2020-03-12T15:08:10.4448082Z ##[debug]Path: d:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-03-12T15:08:10.4449399Z ##[debug]Leaving Get-MatchingItems.
2020-03-12T15:08:10.4450471Z ##[debug]Total found: 1
2020-03-12T15:08:10.4451280Z ##[debug]Leaving Find-VstsFiles.
2020-03-12T15:08:10.4451932Z Sql file: d:\a\r1\a\drop\drop\IaCSetup\security\debug.sql
2020-03-12T15:08:10.4453727Z Invoke-Sqlcmd -connectionString "Data Source=******.database.windows.net; Initial Catalog=TestDatabase; Authentication=Active Directory Password; [email protected]; PWD=***"  -Inputfile "d:\a\r1\a\drop\drop\IaCSetup\security\debug.sql" -verbose
2020-03-12T15:08:10.6621403Z ##[debug]Error record:
2020-03-12T15:08:10.7285292Z ##[debug]Invoke-Sqlcmd : One or more errors occurred.
2020-03-12T15:08:10.7298074Z ##[debug]At line:1 char:1
2020-03-12T15:08:10.7310828Z ##[debug]+ Invoke-Sqlcmd -connectionString "Data Source=******.database.win ...
2020-03-12T15:08:10.7322757Z ##[debug]+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2020-03-12T15:08:10.7338305Z ##[debug]    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], AggregateException
2020-03-12T15:08:10.7350613Z ##[debug]    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
2020-03-12T15:08:10.7362111Z ##[debug] 
2020-03-12T15:08:10.7385315Z ##[debug]Script stack trace:
2020-03-12T15:08:10.7420362Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-03-12T15:08:10.7432839Z ##[debug]at Run-SqlCmd, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\SqlAzureActions.ps1: line 291
2020-03-12T15:08:10.7444912Z ##[debug]at Run-SqlFiles, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\SqlAzureActions.ps1: line 208
2020-03-12T15:08:10.7457069Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\DeploySqlAzure.ps1: line 134
2020-03-12T15:08:10.7469024Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-03-12T15:08:10.7483866Z ##[debug]at <ScriptBlock>, <No file>: line 22
2020-03-12T15:08:10.7496168Z ##[debug]at <ScriptBlock>, <No file>: line 18
2020-03-12T15:08:10.7508169Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-03-12T15:08:10.7527405Z ##[debug]Exception:
2020-03-12T15:08:10.7572202Z ##[debug]System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> AdalException: Authentication failed
2020-03-12T15:08:10.7584281Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
2020-03-12T15:08:10.7596349Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, String password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Int64& fileTime)
2020-03-12T15:08:10.7608194Z ##[debug]   at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
2020-03-12T15:08:10.7620015Z ##[debug]   at System.Threading.Tasks.Task`1.InnerInvoke()
2020-03-12T15:08:10.7634133Z ##[debug]   at System.Threading.Tasks.Task.Execute()
2020-03-12T15:08:10.7654475Z ##[debug]   --- End of inner exception stack trace ---
2020-03-12T15:08:10.7666486Z ##[debug]   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
2020-03-12T15:08:10.7680643Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass134_1.<GetFedAuthToken>b__3()
2020-03-12T15:08:10.7692445Z ##[debug]   at System.Threading.Tasks.Task`1.InnerInvoke()
2020-03-12T15:08:10.7704433Z ##[debug]   at System.Threading.Tasks.Task.Execute()
2020-03-12T15:08:10.7716334Z ##[debug]   --- End of inner exception stack trace ---
2020-03-12T15:08:10.7739304Z ##[debug]   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
2020-03-12T15:08:10.7751076Z ##[debug]   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
2020-03-12T15:08:10.7762807Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
2020-03-12T15:08:10.7774593Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
2020-03-12T15:08:10.7786632Z ##[debug]   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
2020-03-12T15:08:10.7799025Z ##[debug]   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
2020-03-12T15:08:10.7810574Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
2020-03-12T15:08:10.7822778Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
2020-03-12T15:08:10.7834909Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
2020-03-12T15:08:10.7846669Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
2020-03-12T15:08:10.7859051Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
2020-03-12T15:08:10.7872982Z ##[debug]   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
2020-03-12T15:08:10.7884923Z ##[debug]   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
2020-03-12T15:08:10.7896662Z ##[debug]   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
2020-03-12T15:08:10.7908549Z ##[debug]   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
2020-03-12T15:08:10.7920679Z ##[debug]   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
2020-03-12T15:08:10.7932427Z ##[debug]   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
2020-03-12T15:08:10.7944375Z ##[debug]   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
2020-03-12T15:08:10.7956669Z ##[debug]   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
2020-03-12T15:08:10.7968331Z ##[debug]   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
2020-03-12T15:08:10.7982414Z ##[debug]   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
2020-03-12T15:08:10.7996440Z ##[debug]   at System.Data.SqlClient.SqlConnection.Open()
2020-03-12T15:08:10.8008160Z ##[debug]   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(String batch)
2020-03-12T15:08:10.8020029Z ##[debug]   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(String str, Int32 num)
2020-03-12T15:08:10.8031965Z ##[debug]---> (Inner Exception #0) System.AggregateException: One or more errors occurred. ---> AdalException: Authentication failed
2020-03-12T15:08:10.8043986Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
2020-03-12T15:08:10.8055814Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, String password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Int64& fileTime)
2020-03-12T15:08:10.8067502Z ##[debug]   at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
2020-03-12T15:08:10.8079164Z ##[debug]   at System.Threading.Tasks.Task`1.InnerInvoke()
2020-03-12T15:08:10.8091454Z ##[debug]   at System.Threading.Tasks.Task.Execute()
2020-03-12T15:08:10.8103249Z ##[debug]   --- End of inner exception stack trace ---
2020-03-12T15:08:10.8123440Z ##[debug]   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
2020-03-12T15:08:10.8135294Z ##[debug]   at System.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass134_1.<GetFedAuthToken>b__3()
2020-03-12T15:08:10.8147063Z ##[debug]   at System.Threading.Tasks.Task`1.InnerInvoke()
2020-03-12T15:08:10.8158970Z ##[debug]   at System.Threading.Tasks.Task.Execute()
2020-03-12T15:08:10.8170802Z ##[debug]---> (Inner Exception #0) AdalException: Authentication failed
2020-03-12T15:08:10.8183019Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, IntPtr password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Boolean* fWindowsIntegrated, Int64& fileTime)
2020-03-12T15:08:10.8194756Z ##[debug]   at ADALNativeWrapper.ADALGetAccessToken(String username, String password, String stsURL, String servicePrincipalName, ValueType correlationId, String clientId, Int64& fileTime)
2020-03-12T15:08:10.8206391Z ##[debug]   at System.Data.SqlClient.ActiveDirectoryNativeAuthenticationProvider.<>c__DisplayClass2_0.<AcquireTokenAsync>b__0()
2020-03-12T15:08:10.8218057Z ##[debug]   at System.Threading.Tasks.Task`1.InnerInvoke()
2020-03-12T15:08:10.8229817Z ##[debug]   at System.Threading.Tasks.Task.Execute()<---
2020-03-12T15:08:10.8241493Z ##[debug]<---
2020-03-12T15:08:10.8253129Z ##[debug]
2020-03-12T15:08:10.8524185Z ##[error]One or more errors occurred.
2020-03-12T15:08:10.8534604Z ##[debug]Processed: ##vso[task.logissue type=error]One or more errors occurred.
2020-03-12T15:08:10.8643237Z ##[debug]No Firewall Rule was added
2020-03-12T15:08:10.8737065Z ##[debug]Caught exception from task script.
2020-03-12T15:08:10.8758085Z ##[debug]Error record:
2020-03-12T15:08:10.8947984Z ##[debug]ManagedBatchParser.ParserException
2020-03-12T15:08:10.8962516Z ##[debug]   at ManagedBatchParser.Parser.Parse()
2020-03-12T15:08:10.8974544Z ##[debug]   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-03-12T15:08:10.8987240Z ##[debug]At D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\DeploySqlAzure.ps1:213 char:5
2020-03-12T15:08:10.9001080Z ##[debug]+     throw $errorMessage
2020-03-12T15:08:10.9013923Z ##[debug]+     ~~~~~~~~~~~~~~~~~~~
2020-03-12T15:08:10.9028116Z ##[debug]    + CategoryInfo          : OperationStopped: (ManagedBatchPar...roubleshooting-:String) [], RuntimeException
2020-03-12T15:08:10.9040762Z ##[debug]    + FullyQualifiedErrorId : ManagedBatchParser.ParserException
2020-03-12T15:08:10.9057304Z ##[debug]   at ManagedBatchParser.Parser.Parse()
2020-03-12T15:08:10.9070608Z ##[debug]       at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)Check out how to     troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-03-12T15:08:10.9083107Z ##[debug] 
2020-03-12T15:08:10.9103605Z ##[debug]Script stack trace:
2020-03-12T15:08:10.9125390Z ##[debug]at <ScriptBlock>, D:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.156.1\DeploySqlAzure.ps1: line 213
2020-03-12T15:08:10.9137771Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-03-12T15:08:10.9150649Z ##[debug]at <ScriptBlock>, <No file>: line 22
2020-03-12T15:08:10.9164820Z ##[debug]at <ScriptBlock>, <No file>: line 18
2020-03-12T15:08:10.9177600Z ##[debug]at <ScriptBlock>, <No file>: line 1
2020-03-12T15:08:10.9198024Z ##[debug]Exception:
2020-03-12T15:08:10.9254235Z ##[debug]System.Management.Automation.RuntimeException: ManagedBatchParser.ParserException
2020-03-12T15:08:10.9266931Z ##[debug]   at ManagedBatchParser.Parser.Parse()
2020-03-12T15:08:10.9279993Z ##[debug]   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-03-12T15:08:10.9300569Z ##[error]ManagedBatchParser.ParserException
   at ManagedBatchParser.Parser.Parse()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-03-12T15:08:10.9302802Z ##[debug]Processed: ##vso[task.logissue type=error]ManagedBatchParser.ParserException%0D%0A   at ManagedBatchParser.Parser.Parse()%0D%0A   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
2020-03-12T15:08:10.9306148Z ##[debug]Processed: ##vso[task.complete result=Failed]
ShawnMcGough picture ShawnMcGough on 12 Mar 2020

@ShawnMcGough Hi, I am unable to repro the issue on my end. Please let me know if you're still facing this issue.

eaarora-ms picture eaarora-ms on 11 May 2020

Hi @eaarora-ms ! Yes, this is still very much an issue. I can reproduce consistently.

In order to reproduce, it requires an Active Directory User with a password that contains a dollar sign ($). I've been able to reproduce this issue consistently by setting the User's Active Directory password to include or not include a dollar sign. It fails with a dollar sign. It succeeds without a dollar sign.

I use the following command to set the User's password to Abcde$12345:

Set-AzureADUserPassword -ObjectId "{objectId of user}" -Password (ConvertTo-SecureString -String 'Abcde$12345' -Force 鈥揂sPlainText)

Task fails.

I use the following command to set the User's password to Abcde!12345:
Set-AzureADUserPassword -ObjectId "{objectId of user}" -Password (ConvertTo-SecureString -String 'Abcde!12345' -Force 鈥揂sPlainText)

Task succeeds.

ShawnMcGough picture ShawnMcGough on 11 May 2020

@ShawnMcGough Many thanks for reporting this issue! I spent last 2h trying to figure out why my script is failing.

@eaarora-ms @Ajay-MS I'm also able to easily reproduce this. If my AD password contains $ sign, the task fails exactly like Shawn described. When I changed my password by replacing $ sign with @ for example for the same account, everything suddenly started to work fine.

arek-avanade picture arek-avanade on 21 May 2020
👍1

As this command to connect to SQL DB is executing in powershell and $ is special character in powershell its failing. Can you add backtick before $ sign and try? like this Abcde`$12345

AmrutaKawade picture AmrutaKawade on 19 Aug 2020

Please try out above workaround. closing this issue. Feel free to open it if you are still facing issue

AmrutaKawade picture AmrutaKawade on 21 Aug 2020

@AmrutaKawade I don't think this resolves the issue. I should not care how this task is implemented under the hood - if it's Powershell, Python or anything else. It's not possible to escape all possible special characters in all languages. This should be fixed in the Task itself.

arek-avanade picture arek-avanade on 21 Aug 2020
👍1

@AmrutaKawade
Please reopen this issue, it has not been resolved nor does the workaround you described work.

In our case, the password is read directly out of Key Vault and cannot be altered. The task should accept the password as-is without modification.

ShawnMcGough picture ShawnMcGough on 21 Aug 2020

I went ahead and looked into the source code. I've discovered that the password is not being escaped for all authentication methods. The password is only escaped (on line 260) when authentication type is server:
https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L253-L260

When authentication type is aadAuthenticationPassword or aadAuthenticationIntegrated, the Get-AADAuthenticationConnectionString function is called:
https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L277-L282

The Get-AADAuthenticationConnectionString function builds the connection string without escaping password - allowing for special chars to be in the connection string:
https://github.com/microsoft/azure-pipelines-tasks/blob/961d90a3b8d4390ea62066ff601a0c3fbc8ea538/Tasks/SqlAzureDacpacDeploymentV1/Utility.ps1#L249-L251

Probable Solution:

Ensure the password is escaped for all authentication types by using EscapeSpecialChars function at the top of the function.
This can be achieved by moving line 260 to line 252, above if statement.

https://github.com/microsoft/azure-pipelines-tasks/blob/c763c141845d8f8a9eae9571c9df8c63f5a44dd7/Tasks/SqlAzureDacpacDeploymentV1/SqlAzureActions.ps1#L252-L261

I'm not in a position to test this myself but hopefully this can jump-start a fix for this issue.

ShawnMcGough picture ShawnMcGough on 21 Aug 2020
🚀1 ❤1
Was this page helpful?
0 / 5 - 0 ratings

Related issues

alexszilagyi picture alexszilagyi  路  3Comments
HenrikStanley picture HenrikStanley  路  3Comments
richard-ob picture richard-ob  路  3Comments
fedemkr picture fedemkr  路  3Comments
timfish picture timfish  路  3Comments