SqlAzureDacpacDeployment task login failing

Richard Swinbank 46

(This question is probably not tagged correctly - I can't find a tag for Azure Pipelines).

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

- task: SqlAzureDacpacDeployment@1  
  inputs:  
    azureSubscription: $(ServiceConnectionName)  
    authenticationType: connectionString  
    connectionString: $(ConnectionString)  
    deployType: sqlTask  
    sqlFile: PathToSqlFile.sql

The $(ConnectionString) variable is created via a prior AzureKeyVault task and contains a SQL username and password. When the task executes, it fails with a message like "login failed for <user>". The reported value of <user> is correct, matching the one specified in the connection string. When I use the same task earlier in the pipeline to deploy a database DACPAC, with the same connection string, this succeeds.

The error appears to be being thrown from an underlying PowerShell Invoke-SqlCmd call - is there a problem with the task, or am I using it incorrectly?

Many thanks

Mike Ubezzi 2,776 Reputation points

2020-10-06T04:10:27.207+00:00

Hi, @Richard Swinbank . The tag you are looking for is #azure-dev-tool-integrations and I will pull in an engineer who in very knowledgeable in this area. Can you clarify the $(ConnectionString) variable as it is being passed to the Azure SQL Database instance?
Krish G 2,326 Reputation points

2020-10-06T06:43:47.297+00:00

@Richard Swinbank , can you please include the 'full' error message? Also when you say

When I use the same task earlier in the pipeline to deploy a database DACPAC, with the same connection string, this succeeds.

in both the cases, are the 2 tasks part of same 'Job' in yaml or different? And, do you have any firewall enabled at SQL Azure end which could potentially block connections from build agents?

Also I suggest to post your question in right forum for DevOps which is Developer Community to make sure it is seen by considerable number of active DevOps community experts, since at this moment, DevOps is not yet active here in Q&A.
Richard Swinbank 46 Reputation points

2020-10-06T07:53:04.047+00:00

Thanks @Mike-Ubezzi-MSFT, I've added the dev tool integrations tag.

Richard Swinbank 46

@KrishnenduGhosh-MSFT, @Mike-Ubezzi-MSFT thanks.

Here are my two pipeline tasks. They follow one after the other, just like this, in the same job:

- task: SqlAzureDacpacDeployment@1
  displayName: Deploy MyDatabase
  inputs:
    azureSubscription: $(ServiceConnectionName)
    AuthenticationType: connectionString
    ConnectionString: 'Initial Catalog=MyDatabase;$(ASQL-Administrator-ConnectionString)'
    DacpacFile: $(Pipeline.Workspace)/MyDatabaseDb/MyDatabase.dacpac
    AdditionalArguments: '/p:IncludeCompositeObjects=true /p:CreateNewDatabase=false'

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

Richard Swinbank 46

@KrishnenduGhosh-MSFT, @Mike-Ubezzi-MSFT thanks.

Here are my two pipeline tasks. They follow one after the other, just like this, in the same job:

- task: SqlAzureDacpacDeployment@1
  displayName: Deploy MyDatabase
  inputs:
    azureSubscription: $(ServiceConnectionName)
    AuthenticationType: connectionString
    ConnectionString: 'Initial Catalog=MyDatabase;$(ASQL-Administrator-ConnectionString)'
    DacpacFile: $(Pipeline.Workspace)/MyDatabaseDb/MyDatabase.dacpac
    AdditionalArguments: '/p:IncludeCompositeObjects=true /p:CreateNewDatabase=false'

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

Richard Swinbank 46 Reputation points

2020-10-06T08:09:35.437+00:00
(...continued)

The connection string I'm passing is formed by prepending the database name option to the variable $(ASQL-Administrator-ConnectionString). The variable is populated by using the key vault task to download a secret of that name. The secret's value looks like this:

Server=tcp:myserver.database.windows.net,1433;Persist Security Info=False;User ID=dataadmin;Password=MyPasswordHere;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

The server firewall is configured to allow connections from Azure services. The first task succeeds; the second one fails.
Richard Swinbank 46 Reputation points

2020-10-06T08:10:34.777+00:00

Btw, this is reply 2/2; the first half has been held back for moderation for some reason
Krish G 2,326 Reputation points

2020-10-06T08:21:09.223+00:00

@Richard Swinbank , can you try once by temporarily disabling firewall? Just want to eliminate any low hanging fruits to understand the root cause.

Richard Swinbank 46

No probs. I've done that; same behaviour. Here's the error message btw:

Temporary inline SQL file: C:\Users\VssAdministrator\AppData\Local\Temp\tmpAF9.tmp
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmpAF9.tmp" 
Sql file: D:\a\1\MyDatabaseDb\ProcessList.sql
Invoke-Sqlcmd -connectionString "**********"  -Inputfile "D:\a\1\MyDatabaseDb\ProcessList.sql" 
##[error]Login failed for user 'dataadmin'.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-
Finishing: Deploy process config to MyDatabase DB

Krish G 2,326 Reputation points

2020-10-06T09:24:40.057+00:00

Can you capture detailed log by running the pipeline with debug on?
On the Variables tab, add system.debug and set it to true and queue a run.
Richard Swinbank 46 Reputation points

2020-10-06T17:11:37.147+00:00

Hi, here's the log file. Thanks :)
30475-pipelinetask.txt
Krish G 2,326 Reputation points

2020-10-07T08:50:24.827+00:00

Unfortunately I don't see any other reason than either the password being wrong or some special char in password getting stripped off.
Richard Swinbank 46 Reputation points

2020-10-07T10:37:42.967+00:00

Given that the previous task succeeds I think that the password must be OK. 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'll raise an issue in the task repo.

Thanks very much for your efforts in investigating.

Krish G 2,326

Yes that sounds good. To correlate, I am putting the issue link here https://github.com/microsoft/azure-pipelines-tasks/issues/13671

It might be something really edge case here. Meanwhile, you might want to try it passing the params separately instead of connection string.

- task: SqlAzureDacpacDeployment@1
  inputs:
    AzureSubscription: $(ServiceConnectionName)
    AuthenticationType: 'server'
    ServerName: '{server}'
    DatabaseName: '{db}'
    SqlUsername: '{usr}'
    SqlPassword: '{pwd}'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '{dacpac}'
    IpDetectionMethod: 'AutoDetect'

Richard Swinbank 46 Reputation points

2020-10-07T18:36:04.267+00:00

Thanks; good to have a workaround in the meantime!

Share via

SqlAzureDacpacDeployment task login failing