How to pass credentials for data sources during tabular model deployment?

Question

Problem : When I deploy the tabular model using deployment wizard. It works fine. But our problems is that we have 20 data sources and at the time of deployment, we need to provide the credentials 20 times as it asks for credentials for every data source. Which is very painful. That is why we want to automate the deployment.

Approach: I followed this article https://notesfromthelifeboat.com/post/analysis-services-1-deployment/ and I can able to deploy the tabular model without errors but when I refresh the model. It fails with below error

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error:

The credentials provided for the File source are invalid. (Source at \\share\acaidatatempshare\data\lumeneventpropertiesexport.tsv.).
OLE DB or ODBC error: The command has been canceled..
OLE DB or ODBC error: The command has been canceled..
OLE DB or ODBC error: The command has been canceled..

My data source is tsv file and Below is the data source section of the model.bim file. As you can see it does not save the password for the crendential in the model.bim, asdatabase or xmla file.

….
….
      {
        "type": "structured",
        "name": "File/\\\\Share\\AcaiDataTempShare\\Data\\LumenEventPropertiesExport tsv",
        "connectionDetails": {
          "protocol": "file",
          "address": {
            "path": "\\\\share\\AcaiDataTempShare\\Data\\LumenEventPropertiesExport.tsv"
          },
          "authentication": null,
          "query": null
        },
        "credential": {
          "AuthenticationKind": "Windows",
          "kind": "File",
          "path": "\\\\Share\\acaidatatempshare\\data\\lumeneventpropertiesexport.tsv",
          "Username": "domain\\username"
        },
        "contextExpression": [
          "let",
          "    #\"0001\" = Csv.Document(..., [Delimiter = \"#(tab)\", Columns = 3, Encoding = 1252, QuoteStyle = QuoteStyle.None]),",
          "    #\"0002\" = Table.TransformColumnTypes(#\"0001\", {{\"Column1\", type text}, {\"Column2\", type text}, {\"Column3\", type text}})",
         "in",
          "    #\"0002\""
        ]
      },
…..
…..

How can I pass credentials for data sources programmatically during deployment?

Answer 1

Unfortunately, structured (aka. Power Query) data source credentials are not persisted when you deploy the model. I reported this as a bug with the product team some time ago, but have not gotten a response yet. If you can, consider using the legacy (aka. Provider) data sources instead, as these keep the credentials between deployments.

Alternatively, you can apply a password programmatically using a TMSL "createOrReplace" script. The easiest way to create such as script, is to connect to Analysis Services within SSMS, right-click the connection (aka. data source), and choose "Script Connection as" > "CREATE OR REPLACE To" > "New Query Editor Window". In the resulting script, make sure that the password is set correctly:

{
  "createOrReplace": {
    "object": {
      "database": [...] ,
      "dataSource": "File/\\\\Share\\AcaiDataTempShare\\Data\\LumenEventPropertiesExport tsv"
    },
    "dataSource": {
    [...]
    "credential": {
      "AuthenticationKind": "Windows",
      "kind": "File",
      "path": "\\\\Share\\acaidatatempshare\\data\\lumeneventpropertiesexport.tsv",
      "Username": "domain\\username",
      "Password": "<<< YOUR PASSWORD HERE >>>"
    },
    [...]
  }

You can then invoke this script as part of your deployment pipeline - for example using the PowerShell Invoke-AsCmd cmdlet.

Answer 2

Here is the final script I ended up creating.

# Get tools path
$msBuildPath = Get-MSBuildToPath
$Microsoft_AnalysisServices_Deployment_Exe_Path = Get-Microsoft_AnalysisServices_Deployment_Exe_Path

# BUild smproj 
& $msBuildPath $projPath "/p:Configuration=validation" /t:Build

Get-ChildItem $binPath | Copy -Destination $workingFolder -Recurse

$secureStringRecreated = ConvertTo-SecureString -String $AnalysisServerPassword -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($AnalysisServerUserName, $secureStringRecreated)
#$plainText = $cred.GetNetworkCredential().Password

#region begin Update Model.deploymenttargets
# Read Model.deploymenttargets
[xml]$deploymenttargets = Get-Content -Path  $deploymenttargetsFilePath

$deploymenttargets.DeploymentTarget.Database = $AnalysisDatabase
$deploymenttargets.DeploymentTarget.Server = $AnalysisServer
$deploymenttargets.DeploymentTarget.ConnectionString = "DataSource=$AnalysisServer;Timeout=0;UID=$AnalysisServerUserName;Password=$AnalysisServerPassword;"
$deploymenttargets.Save($deploymenttargetsFilePath);
#endregion

#region begin Update Model.deploymentoptions
# Read Model.deploymentoptions
[xml]$deploymentoptions = Get-Content -Path  $deploymentoptionsFilePath

# Update ProcessingOption to DoNotProcess otherwise correct xmla file wont be generated.
$deploymentoptions.Deploymentoptions.ProcessingOption = 'DoNotProcess'
$deploymentoptions.Deploymentoptions.TransactionalDeployment = 'false'
$deploymentoptions.Save($deploymentoptionsFilePath);
#endregion

# Create xmla deployment file.
& $Microsoft_AnalysisServices_Deployment_Exe_Path $asdatabaseFilePath  /s:$logFilePath  /o:$xmlaFilePath

#region begin Update .xmla
#Add passowrd in .xmla file.
$xmladata = Get-Content -Path $xmlaFilePath | ConvertFrom-Json

foreach ($ds in $xmladata.createOrReplace.database.model.dataSources){
    $ds.Credential.AuthenticationKind = 'Windows'
    $ds.Credential.Username = $AnalysisServerUserName

    #Add password property to the object.
    $ds.credential | Add-Member -NotePropertyName Password -NotePropertyValue $AnalysisServerPassword
}

$xmladata | ConvertTo-Json -depth 100 | Out-File $xmlaFilePath
#endregion

#Deploy model xmla.
Invoke-ASCmd -InputFile $xmlaFilePath -Server $AnalysisServer -Credential $cred`enter code here`