使用服务主体的Azure分析服务连接不起作用 [英] Azure analysis service connection using Service principal not working
问题描述
我正在尝试使用ADOMD连接到Azure分析服务，并使用服务主体进行身份验证.因此，我完成了以下操作:
I am trying to connect to Azure Analysis services using ADOMD and authenticated using a Service principal. So I have done following:
- 在AAD中创建应用.
- 授予应用程序(服务主体)对Azure Analysis Service的读取权限.
下面是我连接到Azure分析服务的代码.
Below is my code to connect to Azure Analysis service.
var clientId = "******";
var clientSecret = "*****";
var domain = "****.onmicrosoft.com";
var ssasUrl = "northeurope.asazure.windows.net";
var token = await TokenHelper.GetAppOnlyAccessToken(domain, $"https://{ssasUrl}", clientId, clientSecret);
var connectionString = $"Provider=MSOLAP;Data Source=asazure://{ssasUrl}/{modelname};Initial Catalog= adventureworks;User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate";
var ssasConnection = new AdomdConnection(connectionString);
ssasConnection.Open();
var query = @"Evaluate TOPN(10,Customer,Customer[Customer Id],1)";
var cmd = new AdomdCommand(query)
{
Connection = ssasConnection
};
using (var reader = cmd.ExecuteXmlReader())
{
string value = reader.ReadOuterXml();
Console.WriteLine(value);
}
我能够获得有效的访问令牌，但是在尝试打开连接时出现以下错误:
I am able to get a valid access token, but I get following error when trying to open the connection:
AdomdErrorResponseException:"app:xxxxxxx @ xxxxxx"用户无权访问"adventureworks"数据库，或者该数据库不存在.
其他信息::
- 我已验证通过Azure门户对Azure分析服务的Service主体授予了权限(读者也尝试了贡献).
- 我尝试使用服务帐户(用户名和密码)使用相同的代码，并且可以正常工作.
- 如果我从连接字符串中删除"Initial Catalog = Adventureworks"，则我的连接将成功.但是我不明白为什么Analysis Services权限没有传播到模型.
解决方案:
很抱歉，发布此消息后，我自己得到了解决方案.上面的第3点给了我一个线索.通过门户网站对Azure分析服务的授予权限不会传播到服务主体(Azuire AD应用程序)的模型.
Silly that I got the resolution by myself just after posting this. The point no 3 above gave me a clue.Granting permission on the Azure analysis services through the portal does not propagate to the model for the Service principals (Azuire AD apps).
步骤:
- 在Sql服务器Mgmt Studio中打开Azure分析服务.
- 在目标模型中，转到角色.
-
在获得许可的情况下将服务主体添加到所需角色.服务主体以以下格式添加:
- Open the Azure analysis service in Sql server Mgmt Studio.
- In the target model, go to Roles.
Add the service principal into required role with permission. Service principal is added in below format:
app:[appid] @ [tenantid]
app:[appid]@[tenantid]
示例:app:8249E22B-CFF9-440C-AF27-60064A5743CE @ 86F119BE-D703-49E2-8B5F-72392615BB97
example : app:8249E22B-CFF9-440C-AF27-60064A5743CE@86F119BE-D703-49E2-8B5F-72392615BB97
推荐答案
很遗憾，我在发布此文件后自行获得了解决方案.上面的第3点给了我一个线索.通过门户网站对Azure分析服务的授予权限不会传播到服务主体(Azure AD应用程序)的模型.
Silly that I got the resolution by myself just after posting this. The point no 3 above gave me a clue.Granting permission on the Azure analysis services through the portal does not propagate to the model for the Service principals (Azure AD apps).
步骤:
- 在Sql服务器Mgmt Studio中打开Azure分析服务.
- 在目标模型中，转到角色.
-
在获得许可的情况下将服务主体添加到所需角色.服务主体以以下格式添加:
- Open the Azure analysis service in Sql server Mgmt Studio.
- In the target model, go to Roles.
Add the service principal into required role with permission. Service principal is added in below format:
app:[appid] @ [tenantid]
app:[appid]@[tenantid]
示例:app:8249E22B-CFF9-440C-AF27-60064A5743CE @ 86F119BE-D703-49E2-8B5F-72392615BB97
example : app:8249E22B-CFF9-440C-AF27-60064A5743CE@86F119BE-D703-49E2-8B5F-72392615BB97
I have blogged my whole experience here:https://unnieayilliath.com/2017/11/12/connecting-to-azure-analysis-services-using-adomd/
这篇关于使用服务主体的Azure分析服务连接不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
