Azure Analysis Server impersonation with Azure Sql Server with AAD setup

Shainan Hemrajani 1 Reputation point
2021-08-12T16:51:35.683+00:00

Hi,
I have azure sql db that has AAD setup with MFA. So we do not have any username or password setup for it.
Now I have created an azure analysis server in the same subscription and I want to add this azure db as a source. I can choose Microsoft Account and sign in with my id. But I want to set Impersonation mode so no user details are used to access source db. Can Impersonation service account help me?
I have tried below steps-
Created a app registration in AAD. And added that as an admin on AAS. Also I added this service principal as a user in azure sql database and added it to owner role.

Azure SQL Database
Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
438 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Saurabh Sharma 23,751 Reputation points Microsoft Employee
    2021-08-19T00:10:12.237+00:00

    Hi @Shainan Hemrajani ,
    Here is an update -
    The impersonation settings only apply to on-prem AD scenarios. There is no impersonation of AAD credentials in AAS – the “current user” is always a local service account, which doesn’t get propagated to the datasource.

    Connections to datsources can use username/password in the connection string and/or credentials section of the structured datasource. However, I believe the AAS Mashup engine doesn’t support service principals.

    With AAS, you might be able to use legacy datasources instead of structured datasources if the data provider (e.g. MSOLEDBSQL) supports SPNs as mentioned in the below documentations -

    Please let me know if you have any questions.

    Thanks
    Saurabh

    ----------

    Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

    0 comments
  2. Shainan Hemrajani 1 Reputation point
    2021-08-20T09:38:41.597+00:00

    Thanks @Saurabh Sharma .
    So if AAD credentials are not supported in AAS then if I deploy the cube on server and someone goes in SSMS and try to refresh the cube. Which credentials it will take?

    Also if my data factory is refreshing the cube using Rest API, will it access azure sql db using data factory managed identity? or deployed setting of AAD?

  3. Shainan Hemrajani 1 Reputation point
    2021-09-09T10:26:04.653+00:00

    Hi @Saurabh Sharma , Thanks for your response. I understand the manual steps. But how do I deploy the AAS model?
    When I am deploying or creating release pipelines for cube deployment, what should I enter for AAS to connect to DB?
    For information I am trying to create YAML pipelines to deploy AAS to different environments.