如何在表格模型部署期间传递数据源的凭据?

Question

问题： 当我使用部署向导部署表格模型时。它工作正常。但是我们的问题是我们有 20 个数据源，在部署时，我们需要提供凭据 20 次，因为它要求为每个数据源提供凭据。这是非常痛苦的。这就是我们想要自动化部署的原因。

方法： 我 https://notesfromthelifeboat.com/post/analysis-services-1-deployment/遵循了这篇文章，我可以部署表格模型而不会出错，但是当我刷新模型时。它失败并显示以下错误

无法将修改保存到服务器。返回错误："OLE DB 或 ODBC 错误：

The credentials provided for the File source are invalid. (Source at \shareacaidatatempsharedatalumeneventpropertiesexport.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..

我的数据源是 tsv 文件，下面是 model.bim 文件的数据源部分。如您所见，它不会将 crendential 的密码保存在 model.bim、asdatabase 或 xml 文件中。

….
….
{
"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""
]
},
…..
…..

如何在部署期间以编程方式传递数据源的凭据？

Answer 1

不幸的是，结构化(又名。Power Query( 部署模型时，不会保留数据源凭据。我前段时间向产品团队报告了这个错误，但尚未得到回复。如果可以，请考虑使用旧版(又名。提供程序(数据源，因为这些数据源在部署之间保留凭据。

或者，您可以使用 TMSL "createOrReplace" 脚本以编程方式应用密码。创建脚本的最简单方法是连接到 SSMS 中的 Analysis Services，右键单击连接(也称为数据源(，然后选择"将连接脚本编写为">"创建或替换到">"新建查询编辑器窗口"。在生成的脚本中，确保密码设置正确：

{
"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 >>>"
},
[...]
}

然后，可以将此脚本作为部署管道的一部分调用 - 例如，使用 PowerShell Invoke-AsCmd cmdlet。

Answer 2

这是我最终创建的最终脚本。

# 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`