C# XMLA TOM Connection Failing

[TCavins]

I've set up a premium workspace and I have a PPU license. I can connect to the endpoint in SSMS and see the reports, tables etc. However, I cannot connect to it using C# and TOM. This is the preferred way as I can automate things through loops rather than manually clicking on reports in SSMS to get the info I need.

using Microsoft.AnalysisServices.Tabular;

public static Server server = new Server();

static void Main(string[] args)
{
  string xmlaEndpoint = "powerbi://api.powerbi.com/v1.0/myorg/{workspace}";
  string scope = "https://analysis.windows.net/powerbi/api/.default";
  string connectionString = "DataSource=powerbi://api.powerbi.com/v1.0/myorg/{workspace};"; 


  var tenantId = "xxxxxx";
  var clientId = "yyyyyyy"; 
  var clientSecret = "zzzzzzzz";
  var res = $"https://analysis.windows.net/powerbi/api";
  var authority = $"https://login.microsoftonline.com/{tenantId}";

  string connectStringApp = $"DataSource={xmlaEndpoint};User ID=app:{clientId}@{tenantId};Password={clientSecret};";

  server.Connect(connectStringApp); // Getting invalid connection string

  server.Disconnect();
}

I'm getting an invalid connection string on the server.Connect.

I've tried logging in with my userid and password but get the same issue.  I've granted the Service Principal, name of the registered app to have access to the workspace also.

What is a valid way to connect to the service in C# using the Tabular Object Model?

[v-sgandrathi]

Hi @TCavins,

Thank you for providing the screenshot. Your Power BI Service API permissions are currently set as Delegated, which are for user sign-in authentication. Since you're using a service principal and app-only authentication, you need to set these permissions as Application instead. Keep the same scopes App.Read.All, Dataset.ReadWrite.All, Report.ReadWrite.All, SemanticModel.ReadWrite.All, and Workspace.ReadWrite.All, but add them under Application and grant admin consent in Azure AD.

Then, in the Power BI Admin Portal - Tenant Settings, confirm that service principals can use Power BI APIs and that the XMLA endpoint is enabled with Read/Write access. Also, make sure your workspace is in PPU or Premium capacity and that your service principal is set as an Admin for that workspace.

Once these changes are made, use token-based authentication instead of User ID and Password. Obtain the token with the Microsoft Authentication Library (MSAL), and connect using a string like:
DataSource=powerbi://api.powerbi.com/v1.0/myorg/<WorkspaceName>;Password=<access_token>;
You don't need to change myorg to your domain, as this format is required by Power BI. These steps should help resolve “invalid connection string” or “format exception” errors when connecting through TOM.

Thank you.

[TCavins · Accepted solution]

@v-sgandrathi @GilbertQ I appreciate the back and forth you've provided.  I have been unable to get the Tabular Object Model to work.  However, I have found a way using the ADOMD connection to get the datasource names that includes the actual table or view named used in each Power BI report and this helps. 

Again thank you for your time. I may revisit this as I'd like to figure out how to get TOM to work. I'm not sure if it's my .Net Framework, if it needs Core or something else.

[TCavins]

@v-sgandrathi Thank you for your reply.  I read somewhere today that Premium Per User workspaces cannot use a service principal.  So, I tried to do it with an interactive token but I still get the same invalid connection string error.

When I go to the Application API permissions, I only see two entries.

So, this led me down to make sure I have other items set up correctly and screenshots are below:
XMLA Endpoint:

Service Principal permissions - all set with a security group that the service principal is a member of:

Am I missing something else?

[GilbertQ]

PPU workspaces can use Service Principals. All workspaces can use Service Principals, you just need to add them to the Roles for each App Workspace.

[TCavins]

@GilbertQ The service principal has direct access and also through the security group. I tried each one individually but I still get the same error.

From my readings, it doesn't sound like I can assign Application permissions in the Azure App for Power BI, it has to be delegated permissions but this goes against what @v-sgandrathi has suggested. So I don't know if I need to apply additional settings/permissions as to what I've shared in screenshots or if I need to try something else.

I've verified through SSMS that I can connect to the powerbi://api.powerbi.com endpoint so it's not a network/firewall issue. At least I don't think so.

[v-sgandrathi]

Hi @TCavins,

Thank you for following up. Based on your description, it appears the main problem is related to authentication in your connection string. Instead of using User ID and Password, try connecting with an Azure AD access token. The Tabular Object Model (TOM) supports token-based authentication, which is more secure and reliable for Power BI XMLA connections. After generating the token with MSAL (Microsoft Authentication Library), you can use it in the server.Connect() method.

Ensure your service principal has access to the Power BI workspace as an admin or with build permissions. Also, verify that your workspace is on a Premium or PPU capacity, since XMLA read/write access is only available there. Using token-based authentication should resolve the “invalid connection string” error and allow your C# code to connect.

Thank you,
Sahasra.

[TCavins]

I believe I'm creating the token correctly. I could be wrong but it is still saying invalid connection string.  I have tried multiple examples that I've found online and they all return the same. A lot of the examples use Microsoft.AnalysisServices.retail.amd64 which appears to be obsolete.

I do have a PPU license, the worksapce is set up for PPU. I'm able to authenticate and use the API to pull the report names but it doesn't give the names of the datasources.

Here's one of the examples that I've tried but still receive an invalid connection string.

using System;
using System.Threading.Tasks;
using Microsoft.Identity.Client; // Install Microsoft.Identity.Client NuGet package
using Microsoft.AnalysisServices.Tabular; // Install Microsoft.AnalysisServices.retail.amd64 NuGet package

class Program
{
    // Replace with your Azure AD app registration details
    private const string TenantId = "YOUR_TENANT_ID";
    private const string ClientId = "YOUR_CLIENT_ID";
    private const string ClientSecret = "YOUR_CLIENT_SECRET"; // For service principal auth
    private const string WorkspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE_NAME";

    // Scope for Power BI XMLA endpoint
    private static readonly string[] Scopes = { "https://analysis.windows.net/powerbi/api/.default" };

    static async Task Main()
    {
        try
        {
            // 1. Acquire token using MSAL (Service Principal)
            var app = ConfidentialClientApplicationBuilder.Create(ClientId)
                .WithClientSecret(ClientSecret)
                .WithAuthority($"https://login.microsoftonline.com/{TenantId}")
                .Build();

            var result = await app.AcquireTokenForClient(Scopes).ExecuteAsync();
            string accessToken = result.AccessToken;

            Console.WriteLine("✅ Token acquired successfully.");

            // 2. Connect to Power BI workspace via TOM using token
            using (var server = new Server())
            {
                // Pass token in connection string
                string connectionString = $"DataSource={WorkspaceConnection};Password={accessToken};";
                server.Connect(connectionString);

                Console.WriteLine("✅ Connected to workspace via TOM.");

                // Example: List all datasets (models) in the workspace
                foreach (var db in server.Databases)
                {
                    Console.WriteLine($"Dataset: {db.Name} (ID: {db.ID})");
                }

                server.Disconnect();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"❌ Error: {ex.Message}");
        }
    }
}

[GilbertQ]

Hi @TCavins 

Can you confirm that you have got the service principle set up correctly in terms of the API permissions? It needs to connect to the semantic model. Creating an Azure App or Service Principal to Call Power BI REST API | by kinsun | Medium

[TCavins]

@GilbertQ @v-sgandrathi 

Here are my assigned permissions.  Service principle is set as an admin on the Power BI workspace that is PPU. I appreciate the help thus far and do believe I'm overlooking/missing a small detail.

[v-sgandrathi]

Hi @TCavins 

The valid way to connect to a Power BI semantic model using the Tabular Object Model (TOM) is through the XMLA endpoint of a Premium or PPU workspace. You can authenticate either using a service principal or an Azure AD token obtained via MSAL. The connection must reference your tenant ID in the XMLA URL rather than myorg, and the service principal must be granted the necessary permissions within both Azure AD and the Power BI workspace.

Refer the Microsoft’s official documentations:
Semantic model connectivity and management with the XMLA endpoint in Power BI - Microsoft Fabric | M...
Programming Power BI semantic models with the Tabular Object Model (TOM) | Microsoft Learn

Thank you.

[TCavins]

@v-sgandrathi Thank you for your reply.

I've seen those links you shared and have attemped both of the following:

string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE";
string tenantId = "YOUR_TENANT_ID";
string appId = "YOUR_APP_ID";
string appSecret = "YOUR_APP_SECRET";
string connectStringApp = $"DataSource={workspaceConnection};User ID=app:{appId}@{tenantId};Password={appSecret};";
server.Connect(connectStringApp);

string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE";
string userId = "YOUR_USER_NAME";
string password = "YOUR_USER_PASSWORD";
string connectStringUser = $"DataSource={workspaceConnection};User ID={userId};Password={password};";
server.Connect(connectStringUser);

Even when I replace myorg with my domain, I get the error: FormatException: Input string was not in a correct format.

I can connect via SSMS using myorg and not the domain name but I have the need to use code and loop through all the databases rather than manually clicking each one in SSMS.