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

Abdio68
July 26, 2024
149 views
1 vote
2 Answers

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

Answers

As mentioned in the comments, the $(DS1_PASSWORD) and $(DS2_PASSWORD) variables might contain values that need to be escaped, such as " (double quotes).

Running a quick test using the Windows Powershell ISE:

I’d recommend to add an extra task to escape these variables into a proper json string and then use the escaped values in the liprec.vsts-release-aas.deploy-aas-db.deploy-aas-db@1 task.

Example:

variables:
  - name: DS1_PASSWORD
    value: 'my "pass$:word' # <----------- password with double quotes
  - name: DS2_PASSWORD
    value: 'my other pass:word'

steps:
  - pwsh: |
      $DS1_PASSWORD_JSON = (${Env:DS1_PASS} | ConvertTo-Json -Compress).Trim()
      $DS2_PASSWORD_JSON = (${Env:DS2_PASS} | ConvertTo-Json -Compress).Trim()

      Write-Host "##vso[task.setvariable variable=DS1_PASSWORD_JSON;issecret=true]$DS1_PASSWORD_JSON"
      Write-Host "##vso[task.setvariable variable=DS2_PASSWORD_JSON;issecret=true]$DS2_PASSWORD_JSON"
    displayName: Encode passwords
    env:
      # use environment variables to avoid handling special characters in the script body
      DS1_PASS: $(DS1_PASSWORD)
      DS2_PASS: $(DS2_PASSWORD)

  # use DS1_PASSWORD_JSON and DS2_PASSWORD_JSON variables 
  # instead of DS1_PASSWORD and DS2_PASSWORD
  - 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_JSON)"
            }
          },
          {
            "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_JSON)"
            }
          }
        ]
      overwrite: false

- KevinLuMSFT
- July 26, 2024 at 4:29 pm
- 0 votes
0
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.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.