[SOLVED] Connection string fails in migration when stored as variable in Azure pipeline

Connection string fails in migration when stored as variable in Azure pipeline

I'm executing migration using the following task. It works just as supposed to.

- task: SqlAzureDacpacDeployment@1
  displayName: "App DB migration"
  inputs:
    azureSubscription: $(SrvConn)
    AuthenticationType: 'connectionString'
    ConnectionString: "Server=tcp:my-server.database.windows.net,1433;Database=my-db;Persist Security Info=False;User=the_user;Password=hash_puppy;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    deployType: 'SqlTask'
    SqlFile: '$(Pipeline.Workspace)/migration/db_app.sql'
    IpDetectionMethod: 'AutoDetect'

I'd like, for security reasons, move the string a more secure location and the choice landed in using pipe variables (as discussed in this blog, secret value section). After declaring one called connstring and assigning the exact value from the sample above, I run the updated task. However, it produces a range of errors.

Using ConnectionString: '%CONNSTR%', ConnectionString: $(CONSTRDEV) or ConnectionString: $(env:CONSTRDEV).

##[error]Format of the initialization string does not conform to specification starting at index 0.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting

Using ConnectionString: %CONNSTR%.

While scanning for the next token, find character that cannot start any token.

When I execute the below, the value is there, clearly available on the screen.

- pwsh: Write-Host ${env:CONSTRDEV}

I can't see what I'm doing incorrectly. Reading the docs gave me not much. Theoretically, it works (printout confirmation). However, the taks of migration fails regardless.

Solution

It could be that you originally had double quotation marks:

 ConnectionString: "Server=tcp:my-server.database

In the later one you used single quotation marks:

ConnectionString: '%CONNSTR%'

Also you should change your password