Error in a release to Azure SQL

jdms754

I am receiving the following error: Sqlcmd: Error: The environment variable: 'SQLCMDPASSWORD' has invalid value: 'edit.com'.

In the log I see the DatabasePassword environmental variable is set correctly. Is this a problem with the build or the release definition?

Diogo

Hi @jdms754,

We need a bit more information about your setup. Are you using the SCA extensions in VSTS with a SCA project (formerly known as a ReadyRoll project)?
If this is not the case can you please describe your pipeline to us?

jdms754

I am using VSTS for building and releasing. I was using ReadyRoll build and release components, but I just swapped them for SCA. I am able to build and create a database release artifact. During the deployment phase I am now getting a different error. "Releaser" is the name of my sql user, I am not sure why the value is being assigned to SQLCMDPASSWORD. Also I have to specify the database connection while creating the release artifact and again when deploying. Is this correct?

2018-06-27T14:39:41.2588725Z ##[warning]C:\Users\VssAdministrator\AppData\Local\Temp\DLM Automation\cbxmau4r.3vy: An error occurred while attempting to deploy project scripts:
2018-06-27T14:39:41.2589553Z ##[warning]Sqlcmd: Error: The environment variable: 'SQLCMDPASSWORD' has invalid value: 'Releaser'.
2018-06-27T14:39:41.2590562Z ##[warning]Sqlcmd: Error: The environment variable: 'SQLCMDPASSWORD' has invalid value: 'Releaser'.
2018-06-27T14:39:42.0658692Z ##[error]System.Management.Automation.CmdletInvocationException: Failed to execute the release for ODeSsa ---> RedGate.Versioning.Automation.Shared.Errors.TerminatingException: Failed to execute the release for ODeSsa
2018-06-27T14:39:42.0665648Z    at RedGate.Versioning.Automation.EngineFacade.Deploy.DeployTaskExecutor.Execute(IDatabaseProject databaseProject, DeployTaskExecutorParameters deployTaskExecutorParameters)
2018-06-27T14:39:42.0666901Z    at RedGate.Versioning.Automation.Compare.DeployRelease.ScaReleaseArtifactExecutor.Release(ScaReleaseArtifact releaseArtifact, DeployReleaseArtifactParameters deployReleaseArtifactParameters)
2018-06-27T14:39:42.0667694Z    at RedGate.Versioning.Automation.Compare.DeployRelease.ReleaseArtifactExecutor.DeployReleaseArtifact(IReleaseArtifact releaseArtifact, DeployReleaseArtifactParameters parameters)
2018-06-27T14:39:42.0670881Z    at RedGate.Versioning.Automation.PowerShell.Commands.UseDatabaseReleaseArtifactCommandImpl.Process()
2018-06-27T14:39:42.0672217Z    at RedGate.Versioning.Automation.PowerShell.DlmAutomationCommandImpl`1.ProcessRecord()
2018-06-27T14:39:42.0673245Z    at RedGate.PowerShell.CommandBase`2.DelegateTo(Action doImplementation)
2018-06-27T14:39:42.0673526Z    --- End of inner exception stack trace ---
2018-06-27T14:39:42.0673914Z    at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
2018-06-27T14:39:42.0675816Z    at System.Management.Automation.PowerShell.Worker.ConstructPipelineAndDoWork(Runspace rs, Boolean performSyncInvoke)
2018-06-27T14:39:42.0676637Z    at System.Management.Automation.PowerShell.Worker.CreateRunspaceIfNeededAndDoWork(Runspace rsToUse, Boolean isSync)
2018-06-27T14:39:42.0677344Z    at System.Management.Automation.PowerShell.CoreInvokeHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
2018-06-27T14:39:42.0677679Z    at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
2018-06-27T14:39:42.0679155Z    at Microsoft.TeamFoundation.DistributedTask.Handlers.LegacyVSTSPowerShellHost.VSTSPowerShellHost.Main(String[] args)

jdms754

Some additional information: my build pipeline is currently a single task to create a nuget package. My release pipeline creates a release artifact from this package and then deploys from the release artifact.

jdms754

I attempted to deploy from a build artifact directly and received the same error.

Diogo

Hi @jdms754

I'm afraid SQLCMD variables are not yet supported on the SQL Change Automation VSTS extension. We are aware of this and it is a priority so we expect a fix in the near future. At present, if you're using SQLCMD variables, we recommend that you continue to use the old ReadyRoll VSTS extension.

With regards to your second question, you are correct that the database connection needs to be specified in both tasks.

jdms754

I was not originally using any variables, but was still receiving the error. Is Azure SQL V12 a supported target for SCA release Task or should I use a ReadyRoll task?

jdms754

I believe I figured the issue out. I had a character which requires escaping in powershell in my password. I changed the password and the release was successful.