Azure Function通过TOM连接Power BI数据集时连接字符串格式错误排查

我在构建Azure Function，尝试通过Tabular Object Model（TOM）从Power BI数据集获取数据，运行时抛出错误：

Exception: Microsoft.AnalysisServices.ConnectionException: The connection string is not valid. ---> System.FormatException: Input string was not in a correct format.

已确认的前提条件：

• 拥有对目标PPU工作区完全权限的应用注册，可通过REST API正常操作
• 工作区已启用XMLA端点，权限配置完成

我尝试了多种连接字符串，但问题仍未解决，代码如下：

using System.Net;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Extensions.Logging;
using Microsoft.AnalysisServices.Tabular;
using System;
using RestSharp;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Net.Http;

namespace GetRLSDetails
{
    public class Function1
    {
        private readonly ILogger _logger;

        public Function1(ILoggerFactory loggerFactory)
        {
            _logger = loggerFactory.CreateLogger<Function1>();
        }

        [Function("Function1")]
        [Obsolete]
        public HttpResponseData Run([HttpTrigger(AuthorizationLevel.Function, "get", "post")] HttpRequestData req)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");
            
            var response = req.CreateResponse(HttpStatusCode.OK);
            response.Headers.Add("Content-Type", "text/plain; charset=utf-8");            
            string datasetname = Environment.GetEnvironmentVariable("datasetname");
            string tenantId = Environment.GetEnvironmentVariable("tenantId");
            string appId = Environment.GetEnvironmentVariable("appId");
            string appSecret = Environment.GetEnvironmentVariable("appSecret");
            string workspaceConnection = $"powerbi://api.powerbi.com/v1.0/{tenantId}/BI Management TEST";
            Server server = new Server();

            //first version
            string connectStringUser = $"Provider = MSOLAP;Data source = {workspaceConnection};initial catalog={datasetname};User ID=app:{appId};Password={appSecret};";
            //second version
            string connectStringUser = $"Provider = MSOLAP;Data Source ={workspaceConnection};Initial Catalog ={datasetname};User ID =app:{appId}@{tenantId}; Password ={appSecret}; Persist Security Info = True; Impersonation Level = Impersonate";
            //third version
            string connectStringUser = $"Provider=MSOLAP;Data Source={workspaceConnection};User ID=app:{appId}@{tenantId};Password={appSecret};";
            //fourth version
            string connectStringUser = $"Data Source={workspaceConnection};User ID=app:{appId}@{tenantId};Password={appSecret};";
            //using PBI  access token 
            string connectStringUser = $"Provider=MSOLAP;Data Source={workspaceConnection};UserID=;Password={accessToken};";

            server.Connect(connectStringUser);
            string response_text = "";
            foreach (Database database in server.Databases)
            {
                response_text= response_text+database.Name+',';
            }
            response.WriteString(response_text);
            return response;
        }
    }
}

解决方案 #

1. 正确的连接字符串格式 #

应用身份认证（App ID + Secret）

string connectString = $"Provider=MSOLAP;Data Source={workspaceConnection};Initial Catalog={datasetname};User ID=app:{appId};Password={appSecret};Persist Security Info=True;Impersonation Level=Impersonate";

关键注意点：

• User ID格式为app:{appId}，无需追加租户ID
• 必须保留Provider=MSOLAP字段，TOM依赖该驱动标识
• 确保workspaceConnection中的工作区名称无格式错误（代码中字符串拼接已处理空格问题）

Power BI访问令牌认证

如果使用访问令牌替代App ID/Secret，格式如下：

string connectString = $"Provider=MSOLAP;Data Source={workspaceConnection};Initial Catalog={datasetname};Password={accessToken};Persist Security Info=True;Impersonation Level=Impersonate";

关键注意点：

• 无需设置User ID字段，直接将有效访问令牌填入Password
• 访问令牌需包含Dataset.Read.All或对应数据集的权限，且是针对Power BI服务的有效令牌

2. 额外配置检查 #

• MSOLAP驱动依赖：如果Azure Function使用隔离运行时，需手动部署MSOLAP驱动；消费计划下确保Microsoft.AnalysisServices.Tabular NuGet包使用最新稳定版
• 环境变量验证：确认datasetname、tenantId、appId、appSecret等环境变量无空值或格式错误
• XMLA权限设置：确保应用注册在目标工作区的XMLA权限为管理员或成员（仅读取权限可能无法通过TOM建立连接）

修正后的代码片段示例 #

_logger.LogInformation("C# HTTP trigger function processed a request.");

var response = req.CreateResponse(HttpStatusCode.OK);
response.Headers.Add("Content-Type", "text/plain; charset=utf-8");            

string datasetname = Environment.GetEnvironmentVariable("datasetname");
string tenantId = Environment.GetEnvironmentVariable("tenantId");
string appId = Environment.GetEnvironmentVariable("appId");
string appSecret = Environment.GetEnvironmentVariable("appSecret");
string workspaceConnection = $"powerbi://api.powerbi.com/v1.0/{tenantId}/BI Management TEST";

Server server = new Server();
string response_text = "";

try
{
    // 使用正确的应用身份认证连接字符串
    string connectString = $"Provider=MSOLAP;Data Source={workspaceConnection};Initial Catalog={datasetname};User ID=app:{appId};Password={appSecret};Persist Security Info=True;Impersonation Level=Impersonate";
    server.Connect(connectString);

    foreach (Database database in server.Databases)
    {
        response_text += database.Name + ',';
    }
}
catch (Exception ex)
{
    _logger.LogError(ex, "连接Power BI数据集失败");
    response.StatusCode = HttpStatusCode.InternalServerError;
    response_text = "连接失败：" + ex.Message;
}
finally
{
    if (server.Connected)
    {
        server.Disconnect();
    }
}

response.WriteString(response_text);
return response;

内容的提问来源于stack exchange，提问作者Lev Gelman