使用Automation RunAs服务主体连接到Azure Analysis Services并进行处理 [英] Use Automation RunAs service principal to connect to Azure Analysis Services and process

问题描述

总的来说，这些步骤是:

In summary the steps are:

1. 使用正确的代码(本文中的最后一个代码)
2. 以服务器管理员或数据库管理员的身份在SSMS中手动添加您的应用ID

然后您可以从Azure自动化帐户处理Azure Analysis Services多维数据集，而无需创建另一个单独的服务帐户

and then you can process an Azure Analysis Services cube from an Azure Automation Account without needing to create another seperate service account

我正在尝试使用Azure自动化RunAs服务主体处理Azure Analysis Services多维数据集.这是在Azure自动化帐户中运行的

I am trying to process an Azure Analysis Services cube using the Azure Automation RunAs Service Principal. This is run within an Azure automation account

此代码

#Get the existing AzureRunAsConnection connection
$Conn = Get-AutomationConnection -Name AzureRunAsConnection

# Login with service principal account
Login-AzureRMAccount 
    -ServicePrincipal 
    -Tenant $Conn.TenantID 
    -ApplicationId $Conn.ApplicationID 
    -CertificateThumbprint $Conn.CertificateThumbprint

# Process cube
Invoke-ProcessASDatabase -databasename "DB" -server "Server" -RefreshType "Full"

结果

身份验证失败:如果没有用户界面，则需要用户ID和密码 可用.

Authentication failed: User ID and Password are required when user interface is not available.

我的理解是，当您创建Azure自动化帐户时，它还会创建一个运行方式"帐户，该帐户又会创建一个服务主体帐户. (尽管doco似乎有点亮)

My understanding is that when you create an Azure Automation Account, it also creates a 'RunAs' account, which in turn creates a service principal account. (Although the doco seems a bit light on)

我尝试在Azure AD中找到此主要帐户并将其添加到Azure门户中的SSAS管理员中，但是我找不到实际帐户.服务主体实际上是否显示为帐户?

I have tried finding this principal account in Azure AD and adding it to SSAS Admins in the Azure portal, but I can't find the actual account. Do service principals actually appear as accounts?

下面的代码可以正常工作，但是它使用了预先保存的凭据，但是我不想管理另一个帐户.

The code below works fine, but it uses a pre saved credential but I don't want to have to manage yet another account.

# Purpose: Run a full process the SSAS cube
$AzureCred = Get-AutomationPSCredential -Name "MyCredential"

Add-AzureRmAccount -Credential $AzureCred | Out-Null
Invoke-ProcessASDatabase -databasename "MyDB" -server "MyServer" -RefreshType "Full" -Credential $AzureCred 

更新1

我还尝试在SSMS成员资格区域中手动添加这些名称(在自动化帐户的 RunAs帐户区域中查找了指导之后):

Update 1

I have also tried manually adding these in the SSMS membership area (after looking up the guids in the RunAs Account area in the automation account):

app:applicationid@tenantid

obj:serviceprincipalobjectid@tenantid

并且我收到相同的错误消息.

and I get the same error message.

我还与非管理员用户一起运行了脚本，并得到了

I also ran the script with a non-admin user and got

服务器上不存在"zzz"数据库.

The "zzz" database does not exist on the server.

所以看来我的问题是身份验证，而不是授权.换句话说，这不是我无法访问，而是我无法登录.我正在基于该错误考虑，针对Azure Analysis Services调用Invoke-ProcessAsDatabase时-credential不是可选的

So it would appear my issue is authentication, not authorisation. In other words it's not that I don't access, it's that I can't log in. I'm thinking based on that error, that -credential is not optional when calling Invoke-ProcessAsDatabase against Azure Analysis services

此外，我注意到，对于-credential选项，帮助内容为

Also, I note that for the -credential option, the help says

如果未指定凭据，则假定运行脚本的用户的默认Windows帐户

If no credentials are specified, the default Windows account of the user running the script is assume

鉴于Azure Analysis Services在连接到数据源时似乎只能使用SQL凭据(没有任何类型的AD凭据)，我只能推测Azure Analysis Services无法将任何种类的Azure Ad身份验证用于内部流程.

Given that Azure Analysis Services appears to only be able to use SQL credentials when connecting to a data source (no kind of AD credentials), I can only surmise that Azure Analysis Services is unable to use any kind of Azure Ad authentication for internal processes.

恼人的是，这在任何地方都没有提到.

The annoying thing is that this isn't stated anywhere.

因此，我没有足够仔细地阅读链接.起作用的代码大部分位于两个答复者发布的网站上

So I did not read the link carefully enough. The code that works is mostly on the site posted by both answerers here. To pre authenicate to Azure Analysis Server you need to use Add-AzureAnalysisServicesAccount (The linked blog uses Login-AzureAsAccount but I couldn't get it working and couldn't find doco). You need to install powershell module "Azure.AnalysisServices" to use this.

$Conn = Get-AutomationConnection -Name AzureRunAsConnection
Add-AzureAnalysisServicesAccount -RolloutEnvironment "australiasoutheast.asazure.windows.net" -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint
Invoke-ProcessASDatabase -databasename "MYDB" -server "MyServerEndpoint" -RefreshType "Full"

您可以与此同时使用RunAs帐户，然后无需使用-credential

You can use the RunAs account with this, and afterwards you don't need to use -credential

所以..它实际上可以工作并登录，而无需单独的凭据，但是现在它无权访问数据库.而不是登录错误，我得到

So.. this actually works and logs in without needing a seperate credential, but now it doesn't have access to the database. Instead of a login error, I get

服务器上不存在"ZZZZ"数据库.

The "ZZZZ" database does not exist on the server.

我会发现RunAs帐户可以访问服务器，但不能访问数据库，因此我无法找到要授予它访问权限的服务器.

I would appear that the RunAs account has access to the server but not the database and I can't work out to find it to give it access.

这是有关如何授予应用访问模型以便可以构建模型的更直接指南:

This is a more direct guide on how to give the app access to the model so it can be built:

使用Service主体的天蓝色分析服务连接不起作用

请注意，您无法在Azure门户中添加，因为找不到它.在SSMS中手动"添加它，它将起作用，并且也将以管理员身份显示在Azure门户中

Note you can't add in the Azure portal as it won't find it. Add it "manually" in SSMS and it will work, and it will also appear in the Azure Portal as an admin

现在一切正常.

这已成为方便我存储通过MSI进行身份验证的发现的地方

This has become a handy spot to store my discoveries around authenticating through MSI

尽管此问题已解决，但不，我不想使用MSI安全性从其他方式连接到SQL Azure.没有连接字符串支持此功能-任何连接字符串中的身份验证方法均不支持MSI身份验证.我确实找到了一个有趣的链接，这暗示您可以创建一个支持以MSI身份验证的连接字符串:

Although this question is solved, no I want to connect to SQL Azure from something else using MSI security. No connection string supports this - none of the authentication methods in any connection string support MSI authentication. I did find this interesting link which implies you can create a connection string that supports authentication as MSI:

https://docs.microsoft.com/zh-CN/azure/app-service/app-service-web-tutorial-connect-msi

感兴趣的代码是:

az webapp config connection-string set 
--resource-group myResourceGroup 
--name <app name> 
--settings MyDbConnection='Server=tcp:<server_name>.database.windows.net,1433;Database=<db_name>;' 
--connection-string-type SQLAzure

我找不到对参数--connection-string-type的任何引用.但是看起来您只是完全排除了身份验证部分.

I can't find any reference to the parameter --connection-string-type. But it looks like you simply exclude the authentication piece altogether.

推荐答案

在您的示例1中，您的登录Azure Login-AzureRMAccount似乎没有获得错误日志.根据我的了解，Invoke-ProcessASDatabase不是Azure Power Shell cmdlet.实际上，您无需登录Azure订阅.只有Invoke-ProcessASDatabase -databasename "MyDB" -server "MyServer" -RefreshType "Full" -Credential $AzureCred应该适合您.

In your example 1, it seems not your login Azure Login-AzureRMAccount get the error log. Based on my knowledge, Invoke-ProcessASDatabase is not a Azure Power Shell cmdlet. In fact, you no need to login your Azure subscription. Only Invoke-ProcessASDatabase -databasename "MyDB" -server "MyServer" -RefreshType "Full" -Credential $AzureCred should works for you.

是的，如果我提供的证书可以使用，但是我想使用RunAs 凭据.如果我做不到，那有什么意义呢?

Yes if I supply a credential it works, but I want to use the RunAs credential. If I can't, then what is the point of it.

RunAs credential仅适用于登录Azure订阅，不存储SQL的凭据.在您的方案中，您可以将SQL凭据存储在runbook PSCredential中，例如example2.实际上，在您的示例中，您可以删除Add-AzureRmAccount -Credential $AzureCred | Out-Null.

RunAs credential only works for login your Azure subscription, it does not stores credential for your SQL. In your scenario, you could store your SQL credential in runbook PSCredential, like your example2. In fact, in your example, you could remove Add-AzureRmAccount -Credential $AzureCred | Out-Null.

更新:

您应该在Runbook中使用以下脚本.

You should use following script in runbook.

$Conn = Get-AutomationConnection -Name AzureRunAsConnection 
Login-AzureASAccount -RolloutEnvironment "southcentralus.asazure.windows.net" -ServicePrincipal -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint -TenantId $Conn.TenantID 
Invoke-ProcessTable -Server "asazure://southcentralus.asazure.windows.net/myserver" -TableName "MyTable" -Database "MyDb" -RefreshType "Full"

有关此的更多信息，请检查此

More information about this please check this blog.

这篇关于使用Automation RunAs服务主体连接到Azure Analysis Services并进行处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！