How to handle multiple data sources with credentials when you're deploying AAS model through Azure DevOPS release pipeline

Question

I have a tabular model with two data source connections. Both data sources connect to different servers (and databases) to retrieve data for the tables in the model. Within Azure DevOps, I have created a release pipeline to deploy the model.bim file to my Azure Analysis server using the 'Tabular Database Deployment' step.

In this step, you can specify the 'data source type'. Since I have two data sources, I selected 'Advanced (multiple connections)'. Under this field, in 'Advanced datasource security settings', I need to provide the credentials for each data source. If I don't do this and attempt to process the tables in the model, I get an error indicating that the credentials were not provided correctly.

I have two other tasks in my pipeline before the 'Tabular Database Deployment' step. One retrieves the data from my Azure Key Vault and the other is an Azure CLI task that fetches the details of my Service Connection. The 'Tabular Database Deployment' is the final step.

I have tried the following in the 'Advanced datasource security settings':

[
  {
    "type": "structured",
    "name": "TEST1",
    "connectionDetails": {
      "protocol": "tds",
      "address": {
        "server": "room1.net",
        "database": "Test_db1"
      },
      "authentication": null,
      "query": null
    },
    "credential": {
      "AuthenticationKind": "UsernamePassword",
      "kind": "SQL",
      "Username": "bla1",
      "EncryptConnection": true,
      "Password": "$(DS1_PASSWORD)"
    }
  },
  {
    "type": "structured",
    "name": "TEST2",
    "connectionDetails": {
      "protocol": "tds",
      "address": {
        "server": "room2.net",
        "database": "Test_db2"
      },
      "authentication": null,
      "query": null
    },
    "credential": {
      "AuthenticationKind": "UsernamePassword",
      "kind": "SQL",
      "Username": "bla2",
      "EncryptConnection": true,
      "Password": "$(DS2_PASSWORD)"
    }
  }
]

I get the following error:

Invalid object passed in, ':' or '}' expected.

I tried everything, asked ChatGPT, etc., but I don't know what to do. I can't seem to pass the credentials through. The secret is stored in my Azure Key Vault, which is why I use the variable.

Solution: I want to process the tables from the different data sources. The credentials I pass through the Azure DevOps release pipeline must be included in my deployment.

Who can help me? It would be greatly appreciated as I'm stuck on this for a few days now.

EDIT

Full YAML of the last step, as asked by @Kevin Lu-MSFT

steps:
- task: liprec.vsts-release-aas.deploy-aas-db.deploy-aas-db@1
  displayName: 'blabla1'
  inputs:
    connectedServiceNameSelector: connectedServiceNameARM
    connectedServiceNameARM: 'ppppp'
    aasServer: 'pppppp.net'
    databaseName: 'test11_tabular'
    loginType: spn
    tenantId: '$(tenantId)'
    appId: '$(servicePrincipalId)'
    appKey: '$(servicePrincipalKey)'
    pathToModel: '$(System.DefaultWorkingDirectory)/aa/Model/test11_Tabular/bin/Model.asdatabase'
    partitionDeployment: deploypartitions
    roleDeployment: deployrolesandmembers
    connectionType: advanced
    datasources: |
      [
        {
          "type": "structured",
          "name": "TEST1",
          "connectionDetails": {
            "protocol": "tds",
            "address": {
              "server": "room1.net",
              "database": "Test_db1"
            },
            "authentication": null,
            "query": null
          },
          "credential": {
            "AuthenticationKind": "UsernamePassword",
            "kind": "SQL",
            "Username": "bla1",
            "EncryptConnection": true,
            "Password": "$(DS1_PASSWORD)"
          }
        },
        {
          "type": "structured",
          "name": "TEST2",
          "connectionDetails": {
            "protocol": "tds",
            "address": {
              "server": "room2.net",
              "database": "Test_db2"
            },
            "authentication": null,
            "query": null
          },
          "credential": {
            "AuthenticationKind": "UsernamePassword",
            "kind": "SQL",
            "Username": "bla2",
            "EncryptConnection": true,
            "Password": "$(DS2_PASSWORD)"
          }
        }
      ]
    overwrite: false

Answer 1

Invalid object passed in, ':' or '}' expected.

As discuss with @Abdio68, the cause of the issue is that the password contains " in the content.

I can reproduce the same issue when the password contains the " character.

To solve this issue, we can use single quote to define the password value in the json object.

for example:

From

"Password": "$(DS1_PASSWORD)"

To

"Password": '$(DS1_PASSWORD)'

In this case, we can keep the correct password value in the variable and pass the json object successfully.

On the other hand, when changing passwords, we can avoid using single quote and double quotes in the password value.