In my last blog post, I’ve described how you can deploy SSAS cubes to different stages with the Tabular Editor by using a OATUH authentication for the data source. This post describes a cube by using a SQL authenticated connection. Firstly, I’ve created a cube by using a SQL Auth connection. Visual Studio does not save the password for the connection inside the BIM file. But you can add this, by adding this parameter inside the BIM file:

But you must awa43e of, that the password is stored as plain text inside your code. You don’t have the possibility to encrypt it. After deploying the cube, I created s pipeline inside Azure DevOps like the other from Part 1.

The opposite to the other pipeline is, that we have a step to edit the data source.

This PowerShell script will add the password to the connection string:

$AlterDatasourceCommand = @"
{
  "createOrReplace": {
    "object": {
      "database": "Cube",
      "dataSource": "SQL/sqlpl001 database windows net;sql01"
    },
    "dataSource": {
      "type": "structured",
      "name": "SQL/sqlpl001 database windows net;sql01",
      "connectionDetails": {
        "protocol": "tds",
        "address": {
          "server": "sqlpl002.database.windows.net",
          "database": "sql01"
        },
        "authentication": null,
        "query": null
      },
      "credential": {
        "AuthenticationKind": "UsernamePassword",
        "kind": "SQL",
        "path": "sqlpl002.database.windows.net;sql01",
        "Username": "ok",
		"Password": "XXXXX",
        "EncryptConnection": true
      }
    }
  }
}  
"@    

$myPassword = ‘XXXXX'
$myUsername = 'philipp@plenz.onmicrosoft.com'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)

Invoke-ASCmd -Server asazure://eastus.asazure.windows.net/aspltest -Database Cube -Query $AlterDatasourceCommand -Credential $credential

If you need to script your connection, you can right click to your connection and choose the Replace option. But here you must also add the password option.