Azure SQL Database Deployment task failing - Could not deploy package

[gleizerowicz]

Hi - we're using the Azure SQL Database Deployment task in VSTS to deploy a dacpac, and getting a vague error "*** Could not deploy package".

All logs look normal (finding SqlPackage.exe, the dacpac file, the publish profile, adding/deleting the firewall rule for the hosted build agent). The last 3 log entries we get:

2016-11-21T16:48:07.9812832Z Publishing to database '<DBNAMEREMOVED>' on server '<SERVERNAMEREMOVED>.database.windows.net'.
2016-11-21T16:48:08.2941674Z Initializing deployment (Start)
2016-11-21T16:48:09.9208950Z Initializing deployment (Failed)

With verbose logging turned on, the best info I can get is that the generic error handler in the deployment script is getting hit

At line:1 char:1
+ . 'C:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,DeploySqlAzure.ps1
 
Script stack trace:
at <ScriptBlock>, C:\a\_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.2\DeploySqlAzure.ps1: line 210
at <ScriptBlock>, <No file>: line 1
at <ScriptBlock>, <No file>: line 22
at <ScriptBlock>, <No file>: line 18
at <ScriptBlock>, <No file>: line 1
Exception:
Microsoft.PowerShell.Commands.WriteErrorException: 

SerializedRemoteException      : 
SerializedRemoteInvocationInfo : 
ErrorRecord                    : *** Could not deploy package.
StackTrace                     :    at System.Management.Automation.Interpreter.ThrowInstruction.Run(InterpretedFrame 
                                 frame)
                                    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(Int
                                 erpretedFrame frame)
WasThrownFromThrowStatement    : True
Message                        : *** Could not deploy package.
Data                           : {System.Management.Automation.Interpreter.InterpretedFrameInfo}
InnerException                 : 
TargetSite                     : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext, 
                                 System.Exception)
HelpLink                       : 
Source                         : System.Management.Automation
HResult                        : -2146233087

[rajatagrawal-dev]

Hi @gleizerowicz
Can you send the complete error logs including the ones from the _diag folder in the agent?
Please send the logs to "RM_Customer_Queries at Microsoft dot com".

[rajatagrawal-dev]

@gleizerowicz Do you still face the issue? Please send the complete logs to "RM_Customer_Queries at Microsoft dot com" or post here itself in case you do.

[gleizerowicz]

Yes we are still facing the issue. I sent the logs on Nov 22. I will resend.

[kcliffe]

I'm also experiencing this issue - at least I think it's the same (note i've sort of obfuscated the links in the log below)

2016-12-05T22:47:37.0664105Z ##[section]Starting: Execute Azure SQL : DacpacTask

2016-12-05T22:47:37.0734111Z ==============================================================================

2016-12-05T22:47:37.0734111Z Task         : Azure SQL Database Deployment

2016-12-05T22:47:37.0734111Z Description  : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD

2016-12-05T22:47:37.0734111Z Version      : 1.1.2

2016-12-05T22:47:37.0734111Z Author       : Microsoft Corporation

2016-12-05T22:47:37.0734111Z Help         : [More Information](https://aka.ms/sqlazuredeployreadme)

2016-12-05T22:47:37.0734111Z ==============================================================================

2016-12-05T22:47:40.0768275Z packageFile= PATH.dacpac

2016-12-05T22:47:44.9359484Z Publishing to database 'XXX' on server 'stpsql12-1.database.windows.net,1433'.

2016-12-05T22:47:46.0570407Z Initializing deployment (Start)

2016-12-05T22:50:01.8693518Z Initializing deployment (Failed)

2016-12-05T22:50:03.7679031Z 

2016-12-05T22:50:03.9789023Z ##[error]

SerializedRemoteException      : 

SerializedRemoteInvocationInfo : 

ErrorRecord                    : *** An error occurred during deployment plan generation. Deployment cannot continue.

StackTrace                     :    at System.Management.Automation.Interpreter.ThrowInstruction.Run(InterpretedFrame 

                                 frame)

                                    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(Int

                                 erpretedFrame frame)

WasThrownFromThrowStatement    : True

Message                        : *** An error occurred during deployment plan generation. Deployment cannot continue.

Data                           : {System.Management.Automation.Interpreter.InterpretedFrameInfo}

InnerException                 : 

TargetSite                     : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext, 

                                 System.Exception)

HelpLink                       : 

Source                         : System.Management.Automation

HResult                        : -2146233087

2016-12-05T22:50:03.9939021Z ##[section]Finishing: Execute Azure SQL : DacpacTask

[rajatagrawal-dev]

@gleizerowicz Sorry but I am not getting your logs on RM_Customer_Queries.
@gleizerowicz @kcliffe Can you send your complete debug logs to raagra at microsoft dot com or just post them here itself

[kcliffe]

@rajatagrawal-dev

I've been using VSTS for all of 2 days. Can you give me instructions for accessing "debug logs" and i'll get right onto it.

Wait ......... you're just talking about the build logs. Ok - they're on their way...

Thanks.

[rajatagrawal-dev]

Hi @kcliffe
To get the debug logs, add a variable "system.debug" in your release definition and set its value to "true". This will give you the debug logs. These logs are more detailed.

[kcliffe]

@rajatagrawal-dev Thanks. Logs on the way...

[rajatagrawal-dev]

It seems that the task is failing at the point where it is executing the SqlPackage.exe command. This suggests that one or more of the parameters to this command are invalid. Please try the following.

1. If you have SqlPackage.exe installed on a system, you can manually try to run the command from the command line. The command is available in the logs(you’ll have to correct the file paths and provide the server credentials though).

"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" /SourceFile:"<path_to_dacpac>" /Action:Publish /TargetServerName:"<server_name.database.windows.net>" /TargetDatabaseName:"<database_name>" /TargetUser:"<server_username>" /TargetPassword:"<server_password>" /TargetTimeout:120

Correct the path to sqlpackage.exe also to point it to its location on your system.
If this shows the same behavior as the release then we can be sure the problem is in one of the parameters of this command.

2. Ensure that the type of dacpac is correct. For deploying to Azure, the dacpac target platform should be v12. (You can also try to add /p:AllowIncompatiblePlatform=true in the "Additional SqlPackage.exe Arguments" field in your release definition. Check if the release passes then. This is just for experimentation and should be generally avoided.)

3. Ensure that the server credentials are correct.

[kcliffe]

@rajatagrawal-dev Thanks.

I tried option 2 first, no luck. I then ran option 1 on my local dev box. I receive an error:
Unable to connect to master or target server 'XXX'. You must have a user with the same password in master or target server.

Googling that gives:
http://stackoverflow.com/questions/36837595/visual-studio-team-services-build-failed-database-project-to-azure-sql

The release definition I've selected auto-detect for the firewall options (as advised for hosted servers?).

The credentials I'm using are from a publishsettings file so i believe they're correct?

[rajatagrawal-dev]

@kcliffe

The task requires the credentials for the azure SQL server on which the DB resides. You must have specified these credentials when you created the server.

Also, can you try creating a dacpac with target platform as Microsoft Azure SQL Database V12 and check if you can deploy that?

[rajatagrawal-dev]

Hi @gleizerowicz

Can you also try the 3 points I mentioned above in one of my comments (3 comments above) and get back to us with the results?

[kcliffe]

@rajatagrawal-dev

Sorry for the delay. I got the SqlPackage command successfully deploying from my dev box - i hadn't added myself to the Azure Sql firewall rules.

It's using the same credentials I've been providing to the deployment task (unfortunately?)

In summary:

• Task still failing
• /p:AllowIncompatiblePlatform=true has no effect
• Running SqlPackage.exe with same credentials as deployment task does succeed locally
• The Target Platform is set to Microsoft Azure Sql Database V12

[kcliffe]

@rajatagrawal-dev

Update: I have a successful deployment. I thought I'd just try again creating a new task from scratch. I'm not aware of what i might have changed - apart from possibly the publish profile i elected to use.

I'll send logs for comparison?

[gleizerowicz]

@rajatagrawal-dev I was able to execute the same command successfully locally against the same Azure SQL Server with the same package and parameters, and it is still failing with the same errors as before. This time I created a new release definition with one task and hardcoded all of the parameter values to keep things simple.

Latest log file is attached.
1_Release.log.txt

[gleizerowicz]

@rajatagrawal-dev I found my issue based on this: #2123 and by actually reading the SqlPackage docs. My database project has a variable, and if none are supplied in the command line arguments then SqlPackage will fail. This issue is resolved for me.

If you have variables, you must supply values in the "Additional SqlPackage.exe Arguments" box:
/variables:MyVariableName=MyVariableValue

Docs for additional arguments: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx