Azure Analysis Services to Azure SQL Database authentication options after enabling “Support only Microsoft Entra authentication”

Bharath Kumar S 70 Reputation points

Hello everyone,

We are planning to enable “Support only Microsoft Entra authentication for this server” on an Azure SQL Database logical server as part of a security hardening initiative.

Current setup

We use Azure Analysis Services (AAS) with a tabular semantic model.

  • The model connects to Azure SQL Database using SQL authentication configured in the model data source:
    • impersonationMode = impersonateAccount A SQL login (User ID=...)
    Model refresh is triggered via the AAS REST API (from Azure Data Factory), using Microsoft Entra authentication.

We understand that once Entra-only authentication is enabled:

  • SQL authentication (User ID/Password) will no longer be supported

The existing AAS data source configuration will fail during refresh

Questions

  1. What are the supported authentication methods for Azure Analysis Services to connect to Azure SQL Database when Entra-only authentication is enabled? Specifically, can AAS use:
    • Service Principal
    • Managed Identity
  2. For automated and scheduled AAS model refreshes, which authentication option is considered best practice from a security and operational standpoint?
  3. Are there any known limitations or special considerations when using Entra authentication from AAS to Azure SQL Database with respect to:
    • Cross-database queries on the same logical SQL server
    • External tables defined in Azure SQL Database

Thanks,

Bharath Kumar S

0 comments No comments
Sign in to comment

2 answers

  1. Anonymous

    Hi @BharathKumarS-1759

    Thank you for contacting Microsoft Q&A. Please find below the detailed steps to address the reported issue -

    Azure Analysis Services → Azure SQL DB with Entra Only Authentication

    When you enable “Support only Microsoft Entra authentication” on Azure SQL Database, SQL authentication (username/password) is disabled. Since Azure Analysis Services (AAS) currently uses SQL authentication, model refreshes will fail unless you switch your data source to Entra ID-based authentication.

    Supported Authentication Methods for AAS → Azure SQL DB (with Entra Only enabled)

    AAS supports Microsoft Entra Service Principal (App Registration) for authentication:

    • Config: impersonationMode = "impersonateServicePrincipal"
    • What you need: Client ID, Client Secret, Tenant ID
    • How it works: AAS authenticates using OAuth2 to get an access token, and this works after SQL authentication is disabled.

    Not Supported:

    • Managed Identity: AAS doesn’t support Managed Identities (system or user-assigned) since it doesn’t rely on Azure Resource Manager/IAM identity infrastructure. Service Principal is the recommended option for automated refreshes.

    Best Practice for Automated & Scheduled Refresh

    • Use a dedicated Entra Service Principal for secure and reliable automated refreshes.
    • Why? It:
      • Works well with AAS impersonation mode.
        • Doesn’t expire like passwords.
          • Supports Key Vault secret rotation.
            • Enables least-privilege role-based access on SQL.

    Required SQL Permissions:

    • Create a user for the Service Principal and assign it:
      • db_datareader role (at minimum)
        • db_datawriter role (only if needed)

    AAS Configuration:

    • In AAS model’s data source:
      • "impersonationMode": "impersonateServicePrincipal"
        • "account": "appId=<APP-ID>; tenantId=<TENANT-ID>; secret=<SECRET>"

    Additional Permissions:

    • AAS Contributor role (for refresh triggers)
    • SQL Database RBAC access

    Special Considerations and Limitations

    • Cross-Database Queries (Same Logical SQL Server):
      • The Service Principal must exist in each database involved in the query.
        • Grant the necessary permissions (e.g., SELECT) to the Service Principal in each database.
        • External Tables:
          • Service Principal must have access to external data sources, which must support Entra ID authentication.
            • If using SQL credentials for external sources, enabling Entra-only won’t affect them, unless SQL authentication is disallowed at the external source.

    Limitation: If the external table uses SQL authentication and the target blocks it, the external data source needs updating.

    1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

      Hi BharathKumarS-1759
      Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    2. Marek Vařeka 0 Reputation points

      Hello @Pilladi Padma Sai Manisha ,

      Thank you for sharing information about the Azure Analysis Services to Azure SQL Database authentication. I am also trying to connect AAS to the Azure SQL DB.

      Our current setup is using SQL authentication with Technical account. The client requires switch to the Azure Service Principal.

      I have followed your manual but the process failed.

      Is there something wrong with my connection ?

      Error:

      Failed to save modifications to the server. Error returned: '<ccon>The data source kind doesn't support the credential type. Data source kind: SQL. Credential type: ServicePrincipal.</ccon>. The exception was raised by the IDbConnection interface.

      The connection setup:

      {
 "createOrReplace": {
 "object": {
 "database": "DB",
 "dataSource": "DataSource"
 },
 "dataSource": {
 "type": "structured",
 "name": "DataSource",
 "connectionDetails": {
 "protocol": "tds",
 "address": {
 "server": "dev.database.windows.net",
 "database": "sqldb-dev"
 }
 },
 "options": {
 "commandTimeout": "PT2H"
 },
 "credential": {
 "authenticationKind": "ServicePrincipal",
 "impersonationMode": "ImpersonateServicePrincipal",
 "servicePrincipalId": "a5",
 "tenantId": "12",
 "servicePrincipalKey": "XYZ"
 }
 }
 }
}
    Sign in to comment

  2. AI answer

    When you enable "Support only Microsoft Entra authentication" for your Azure SQL Database, SQL authentication will indeed be disabled. Here are the answers to your questions regarding Azure Analysis Services (AAS) connecting to Azure SQL Database under this configuration:

    1. Supported Authentication Methods:
      • Service Principal: Yes, AAS can use a Service Principal to connect to Azure SQL Database when Entra-only authentication is enabled.
      • Managed Identity: Yes, AAS can also use Managed Identity for authentication to Azure SQL Database.
    2. Best Practice for Automated and Scheduled AAS Model Refreshes:
      • From a security and operational standpoint, using Managed Identity is often considered the best practice. It provides a secure way to manage credentials without hardcoding them in your application, and it integrates well with Azure services.
    3. Known Limitations or Special Considerations:
      • Cross-database Queries: When using Entra authentication, ensure that the Microsoft Entra administrator is configured for both databases involved in cross-database queries. If not, you may encounter connection issues.
      • External Tables: There may be limitations regarding the use of external tables with Microsoft Entra authentication, so it's essential to verify that the necessary permissions and configurations are in place.

    Make sure to test your configuration thoroughly to ensure that all components work seamlessly after enabling Entra-only authentication.

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer