The task is used to deploy Azure SQL Database to an existing Azure SQL Server, either by using DACPACs or SQL Server scripts. The DACPACs are deployed using SqlPackage.exe and the SQL Server scripts are deployed using the Invoke-Sqlcmd cmdlet. DACPACs and SqlPackage.exe and Invoke-Sqlcmd cmdlet provides for fine-grained control over the database creation and upgrades, including upgrades for schema, triggers, stored procedures, roles, users, extended properties etc. Using the task, multiple different properties can be set to ensure that the database is created or upgraded properly. 

While deploying sql script using this task, it invokes Invoke-Sqlcmd cmdlet. It is to be noted that VSTS automatically adds firewall extension rule for hosted agent and deploy the script. So we can rule firewall rules as out of question here.

While working on a recent deployment, we kept getting error like this:

2017-08-08T13:56:21.2276855Z ##[section]Starting: Execute Azure SQL : SqlTask
2017-08-08T13:56:21.2406854Z ==============================================================================
2017-08-08T13:56:21.2406854Z Task : Azure SQL Database Deployment
2017-08-08T13:56:21.2406854Z Description : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD
2017-08-08T13:56:21.2406854Z Version : 1.1.18
2017-08-08T13:56:21.2406854Z Author : Microsoft Corporation
2017-08-08T13:56:21.2406854Z Help : [More Information](https://aka.ms/sqlazuredeployreadme)
2017-08-08T13:56:21.2406854Z ==============================================================================
2017-08-08T13:56:23.2202068Z packageFile= d:\a\r1\a\******\drop\DatabaseScripts\mysqlscript.sql
2017-08-08T13:56:24.1173854Z Invoke-Sqlcmd -ServerInstance ****** -Database “***** -Username ******* -Password ****** -Inputfile “d:\a\r1\a\******\drop\DatabaseScripts\mysqlscript.sql” -ConnectionTimeout 120
2017-08-08T13:56:25.1727504Z ##[error]Login failed for user ‘*****’.
2017-08-08T13:56:25.1857485Z ##[section]Finishing: Execute Azure SQL : SqlTask

After troubleshooting, we finally identified that sql password could not contain special letters like $ (Dollar Character) or ” (Double quotes). This is because special characters are interpreted (the same for username containing special characters). It is not the case with single quoted strings.

For example, this password will cause an authentication error because the character $ is evaluated:

$pw = "p@ss$ord!"

But if you use single quotes, it will work:

$pw = 'p@ss$ord!'

Please note that the same principle applies to command lines, so this wil not work:

Invoke-Sqlcmd ... -Password "p@ss$ord!"

You should write:

Invoke-Sqlcmd ... -Password 'p@ss$ord!'

However, there is no way to achieve a single quote as mentioned above while deploying using VSTS. So the only option seem to left is to make sure that sql login password does not contain above mentioned special character.

Many thanks to Benjamin on this stackoverflow post: https://stackoverflow.com/questions/22335651/powershell-invoke-sqlcmd-login-failed for pointing me in the right direction.