Community Center | Not monitored
Tag not monitored by Microsoft.
SqlAzureDacpacDeployment task login failing
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 75%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERS%3C/text%3E%3C/svg%3E)
Richard Swinbank
46
Reputation points
(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
Community Center | Not monitored
{count} votes
-
Mike Ubezzi 2,776 Reputation points2020-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? -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(51.2, 79%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKG%3C/text%3E%3C/svg%3E)
krish-gh 2,331 Reputation points Moderator2020-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 Reputation points
2020-10-06T08:07:29.89+00:00 @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:08:37.767+00:00 @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-gh 2,331 Reputation points Moderator
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 Reputation points
2020-10-06T08:53:16.017+00:00 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-gh 2,331 Reputation points Moderator
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-gh 2,331 Reputation points Moderator
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-gh 2,331 Reputation points Moderator
2020-10-07T12:35:47.727+00:00 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!
Sign in to comment
Sign in to answer