question

ShainanHemrajani-5099 avatar image
0 Votes"
ShainanHemrajani-5099 asked SaurabhSharma-msft commented

Azure Analysis Server impersonation with Azure Sql Server with AAD setup

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-databaseazure-analysis-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @shainanhemrajani-5099,

Thanks for using Microsoft Q&A !!
I do not think it will work as you cannot pass any credentials when you are selecting Impersonation Account.
123195-image.png
Also, Impersonate Service Account specifies the model use the security credentials associated with the Analysis Services service instance that manages the model. Please refer to the documentation for details

I am checking on this one if this is possible and get back to you.

Thanks
Saurabh



0 Votes 0 ·
image.png (22.5 KiB)

Thanks a lot @SaurabhSharma-msft for the response. This line i have read in the docs and cannot fully understand - 'security credentials associated with the Analysis Services service instance'. As for AAS there were no credentials created, it directly works on AAD login details. And there is an option for service account so surely there could be a way to set that up.
If I move ahead with Microsoft login and then when my data factory will be triggering AAS refresh via managed identity, how will impersonation work?

0 Votes 0 ·
SaurabhSharma-msft avatar image SaurabhSharma-msft ShainanHemrajani-5099 ·

Hi @shainanhemrajani-5099, I am checking internally on this one and get back to you.

Thanks
Saurabh

0 Votes 0 ·
Show more comments
SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered

Hi @ShainanHemrajani-5099,
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShainanHemrajani-5099 avatar image
0 Votes"
ShainanHemrajani-5099 answered SaurabhSharma-msft commented

Thanks @SaurabhSharma-msft .
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?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @shainanhemrajani-5099,
1. Processing the cube from SSMS takes the credentials (impersonation mode which works with the database server) from database connections defined at database on Analysis server (See screenshot).
2. It will take the deployed settings of AAD.

Thanks
Saurabh

0 Votes 0 ·

Hi @shainanhemrajani-5099,

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

Thanks
Saurabh

0 Votes 0 ·

Thanks @SaurabhSharma-msft for the response.
I still haven't got solution for my problem. I do not have username and p/w set up for azure sql db, auth is done through AAD with MFA. Now how do I setup AAS to auth to db with putting any credentials.
Also I would like to deploy to server making sure no user is involved every time it is refreshed.

0 Votes 0 ·
Show more comments
ShainanHemrajani-5099 avatar image
0 Votes"
ShainanHemrajani-5099 answered SaurabhSharma-msft commented

Hi @SaurabhSharma-msft, 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ShainanHemrajani-5099,

Sorry it is not possible as currently client_credentials flow is not supported by AAS that would be necessary to use a service principal or explicit user credentials to connect from AAS to DB versus an access token that requires regular refresh.

Thanks
Saurabh

0 Votes 0 ·