Connecting Azure Analysis Service with a Synapse Dedicated SQL Pool using a Service Principal credentials

JayZee64 0 Reputation points
2026-03-19T08:26:35.6366667+00:00

The issue I am facing with is on Visual Studio Community 2026 there is no where to add the service principal credentials on the data source when the power query comes up. I have tried adding it to the property values and updating the model.bim file. When I have done that it and go to deploy, the credential box appears and I'm forced to add the basic SQL login.

We have created a service principal called "sp-aas-admin"

On the dedicated SQL pool I have added the following. 

CREATE USER [sp-aas-admin] FROM EXTERNAL PROVIDER
GRANT SELECT TO [sp-aas-admin];
EXEC sp_addrolemember N'db_datareader', N'sp-aas-admin'

On Azure Analysis Service server I have given it server administrator using the following app:clientid:tenantid

At the database level on Azure Analysis service I have given it a full control role by adding the service principal as app:clientid:tenantid

The longer term achievement is using the service principal as the connection source but then have schema and row level security roles.

Azure Analysis Services
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-03-19T08:26:54.8766667+00:00

    Visual Studio’s Power Query-based data source UI for Azure Analysis Services does not support entering Microsoft Entra service principal credentials directly. It always prompts for an interactive identity (SQL login or Entra user), which is why the SQL login dialog appears at deploy time even after editing model.bim.

    To use a service principal for Azure Analysis Services to connect to a Synapse dedicated SQL pool, configure the service principal and connection outside of the Visual Studio credential dialog and use the supported service principal syntax:

    1. Ensure the service principal is correctly provisioned in Synapse
      • In the dedicated SQL pool, create the Entra user for the service principal and grant permissions, as already done: 
             CREATE USER [sp-aas-admin] FROM EXTERNAL PROVIDER;
     GRANT SELECT TO [sp-aas-admin];
     EXEC sp_addrolemember N'db_datareader', N'sp-aas-admin';
      • This aligns with the documented pattern of using Microsoft Entra identities (including service principals) for Synapse SQL authentication.
    2. Ensure the service principal is correctly configured in Azure Analysis Services
      • Add the service principal as a server administrator using the required format:
        • app:{service-principal-client-id}@{azure-ad-tenant-id}
      • This must be done at server deployment or via ARM/PowerShell. The documentation explicitly requires this format for service principals in the server admin role.
    3. Use service principal syntax in the Analysis Services connection string
      • For client libraries and tools that support it (AMO/ADOMD and compatible modules), service principals are passed in connection strings using:
        • app:AppID plus password, or
        • cert:thumbprint for certificate-based auth.
      • For example, when using AMO/ADOMD or PowerShell automation against Azure Analysis Services, the connection string uses app:AppID rather than a UPN.
      • Visual Studio’s Power Query UI does not expose this directly; instead, configure and deploy the model, then manage or automate processing/operations using a service principal-aware client (e.g., PowerShell or a custom app) that uses the app:AppID syntax.
    4. Accept the limitation of the Visual Studio designer
      • The Visual Studio 2026 designer cannot be forced to store and use the service principal credentials for the data source at design time. The credential prompt at deployment is by design for that UI.
      • The supported pattern is:
        • Use Visual Studio to design the model and structure the data source.
        • Use service principal-based connections for runtime/automation (processing, refresh, operations) via tools/libraries that support app:AppID in connection strings.
    5. Implement schema and row-level security
      • Once the service principal is correctly configured as an admin and the model is deployed, define roles in the Azure Analysis Services model for schema and row-level security.
      • Grant users/groups to those roles; the service principal itself typically remains an admin/automation identity, not a constrained RLS identity.

    Managed identities are not supported for Azure Analysis Services, so the approach must use a regular service principal as described.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Manoj Kumar Boyini 19,590 Reputation points Microsoft External Staff Moderator
    2026-03-20T18:41:32.1133333+00:00

    Hi JayZee64,

    it sounds like you’ve done the bulk of the SPN setup (AAD user in the dedicated SQL pool, SPN as server-admin and full-control in AAS) but you’re still being forced into a Basic (SQL user) prompt in VS because the Tabular Model tooling isn’t picking up your SPN credential as a “Service Account” connection. Here’s a recipe that’s worked for me:

    Generate and store your SPN secret

    • In Azure AD, open your registered app (sp-aas-admin) and create a new client secret. Copy that value somewhere safe.

    Edit your model.bim to use the SPN as a service account In the node of your .bim, locate your Azure Synapse SQL pool connection and set: • ImpersonationMode = “ServiceAccount” • CredentialUserName = “app:@” • CredentialPassword = “”

    For example:

         "DataSources": [
    {
      "Name": "DedicatedSqlPool",
      "ConnectionString": "Provider=MSOLAP;Data Source=asazure://<region>.asazure.windows.net/<server>;Initial Catalog=<database>;",
      "ImpersonationMode": "ServiceAccount",
      "CredentialUserName": "app:******@yourTenant.onmicrosoft.com",
      "CredentialPassword": "XXXXXXXXXXXXXXXXXXXX"
    }
  ]

    Deploy via Tabular Model SQL Server Database Project (SSDT) or Tabular Editor

      - Open the .bim in Tabular Editor (free edition works).
  
     - Expand Model → Data Sources → click your Synapse source.
     
        - In Properties, verify Impersonation is ServiceAccount and that the two credential fields are populated.
        
           - Save and deploy. You should no longer see an interactive prompt.
           
           Alternative: use PowerShell or TMSL for unattended SPN deployment If VS still forces a prompt, bypass SSDT and script the deploy. For example with PowerShell:
           
           ```powershell
           Connect-AzAccount -ServicePrincipal `

    -ApplicationId $appId -TenantId $tenantId -Credential (New-Object pscredential $appId, (ConvertTo-SecureString $secret -AsPlainText -Force))

    Invoke-ProcessDatabase -Server "asazure://<region>.asazure.windows.net/yourAASserver" -Database "YourModel" -ModelFile "path\to\model.bim" -ServicePrincipal -ApplicationId $appId -TenantId $tenantId ` -Credential $Credential ```

               After deployment, you can layer your Synapse role-based and row-level security within the tabular model by creating roles in Tabular Editor or SSDT as usual.

    Hope that helps you wire up a true SPN connection without the basic-SQL prompt. Let me know if you hit any errors, and share:

    • Which version of the Analysis Services tools you have installed in VS 2026 • Whether you’ve set ImpersonationMode to ServiceAccount in the .bim • If you see any JSON validation or deployment errors when you save/deploy

    —Reference documentation—

    1. Add SPN to Azure Analysis Services admin role https://learn.microsoft.com/azure/analysis-services/analysis-services-addservprinc-admins
    2. Sign in via SPN to AAS (connection string syntax) https://learn.microsoft.com/azure/analysis-services/analysis-services-service-principal
    3. Dedicated SQL Pool AAD user creation & permissions https://learn.microsoft.com/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-manage-security
    4. PowerShell deploy with ServicePrincipal flag (Az.AnalysisServices / SqlServer module) https://learn.microsoft.com/azure/analysis-services/analysis-services-service-principal

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.