How to set password during automated SSAS Tabular deployment?

Question

Dear SSAS Tabular Gurus!

We are setting up an automated build/release-pipeline for a SQL Server Analysis Server Tabular model (SSAS Tabular). For this, we are using Visual Studio Team Services (VSTS) and PowerShell. It will target 3 parallel environments:

(localdb)\ProjectsV13 <- SSAS-integrated workspace
SQL-dev <- SSAS-dev
SQL-test <- SSAS-test
SQL-prod <- SSAS-prod

When SSAS is deployed, there are two impersonation methods for connecting to source data:

Specific Windows account. (A special data-reader account.)
Service account. (The process running the SSAS service.)

It is recommended you specify a Windows user account and password for impersonation credentials. A Windows user account can be configured to use least privileges necessary to connect to and read data from the data source. Impersonation (SSASTabular)

Now, we try to follow the recommended practice with windows account. Whether deploying through Visual Studio (SSDT) or the Deployment Wizard, the password for the windows account is entered in a separate pop-up box. These credentials are stored directly in-memory of the SSAS instance, until the it is restarted or re-deployed. The password is (luckily) never written in deploy scripts. Now, we automate this using PowerShell to replace environment-specific SQL/SSAS targets in the deploy files, and deploy with the deployment utility. Everything works, except:

How do we programatically set the passwords directly on the server post-deployment?

We can manually set the password via SSMS, but we require automation. We are also concerned that a possible server-restart will require manual password entry. We have tried libraries Analysis Management Objects (AMO) and Tabular Object Model (TOM), but not found a way to set the password.

Do you mean you need to specify the credential manually if you deploy it using PowerShell and Deployment utility? — starian chen-MSFT
No, optimally the password is stored as a secret variable in VSTS, and that is passed automatically during/after deployment. — Martin Thøgersen
@MartinThøgersen - you never got an answer? I have the same problem - setting the connection string and impersonation account ID and password at deployment time in a Jenkins job. I have the credentials stored securely but need to understand how to deploy the asdatabase with those credentials. — Richard Schaefer
If you connect the SSAS instance in management studio you will see the data source defined in there. You can right click and script it out (alter). You should be able to edit the script to include whatever password is required. So basically you need to add the script step to the end of your deployment. — Nick.McDermaid

Brandon McClure Brandon McClure · Accepted Answer · 2018-07-09T20:18:40

I have not used this before as all of my tabular models impersonate the service account, which was a poor decision that I will defend based on the difficulty of automating the deployment with a dedicated read account. With TOM, our deployments have gotten much better.

I would create a Secure string on your build box using the build account. I have used this blog post to setup a secure string that my TFS build scripts can read from, and can only be decrypted by the build account, on a single build machine. With the password secured, I would use TOM to modify the data source(s) for the models and set the credentials

On the build box, as the build user, generate the encrypted secure string:

$secureString = ConvertTo-SecureString -String "MyPassword" -AsPlainText -Force
$encryptedSecureString = ConvertFrom-SecureString -SecureString $secureString
Write-Host $encryptedSecureString

This will give you a long alpha numeric string that can only be decrypted by the same user that generated it, on the same machine. You can store that in a TFS variable, or hardcode it into your script. If/when your build boxes change you will need to regenerate the secure string and update wherever you store it.

In your build script, you load the TOM assemblies, read/deserialize the .bim file, iterate through your data sources as needed and set the Account and Password using the decrypted secure string, and serialize | Set-Content to your bim file so that you can continue to use your build scripts that call the wizard.

$password = "01000000d08c9ddf0115d1118c7a00c04fc297eb01000000ce22fea7de22154fa3d76724ff4e4f5b0000000002000000000003660000c00000001000000066929eac2e7f09246011f85f6cffc6580000000004800000a000000010000000e724ec16b436fa822c65e731140aa2f318000000e2e93a7f5841d38030e8d8c7d4bbc51c7c0c9579eb9cd0e814000000a78a107dcdeec47b0992a5529a879f2b2c57ef7e"
Write-Host "Passed Password: $password"
$secureStringRecreated = ConvertTo-SecureString -String $password
$cred = New-Object System.Management.Automation.PSCredential('UserName', $secureStringRecreated)
$plainText = $cred.GetNetworkCredential().Password
Write-Host "SecureStringRecreated: $plainText"

$bimFilePath = 'C:\Source\SSAS_TabularModels\myModel\myModel.bim'
$modelBim = [IO.File]::ReadAllText($bimFilePath)
$db = [Microsoft.AnalysisServices.Tabular.JsonSerializer]::DeserializeDatabase($ModelBim)

foreach ($ds in $db.Model.Model.DataSources){
    $ds.ImpersonationMode = "ImpersonateAccount"
    $ds.Account = 'MyUserName'
    $ds.Password = $plainText
}

[Microsoft.AnalysisServices.Tabular.JsonSerializer]::SerializeDatabase($db) | Set-Content $bimFilePath

How to set password during automated SSAS Tabular deployment?

1 Answers