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

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.

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.