ASAZURE to Fabric Warehouse SQL Endpoint

[danjo914]

What we are trying to do is to set up an ASAZURE model to pull from a warehouse via sqlendpoint from a Microsoft Fabric Workspace. This works fine if I use my own Microsoft Credentials, however, the token only lasts a day. We want to automate this using Microsoft Azure Automation however, it doesn't seem to support Managed Instances or Service Principals and requires us to use a OAUTH2 username/password each time we want to process.  Surely that is not intended, does anyone have a work around? I believe the issue the connection ot the Microsoft Fabric Warehouse requiring an OAUTH2 but then how would you go about automating it without embedding usernames and passwords?

[v-shex-msft]

HI @danjo914,

Have you tied to use sql endpoint to getting data? Here is a link about notebook with pyodbc and azure keyvault and SPN to getting data from sql endpoint, you can check it if helps: 

Load data to MS Fabric Warehouse from notebook - Stack Overflow

Regards,

Xiaoxin Sheng

[danjo914]

Hi Xiaoxin,

Thank you for your response. My question is specifically about the best authentication method to use when connecting to the SQL endpoint from ASAzure. From my testing, it appears that both Service Principal and Managed Instance authentication methods are not supported, or at least that's the error I’m encountering.

For context, we have scripts that successfully use Service Principal credentials to pull data via the SQL endpoint without any issues. This leads me to believe the problem lies with ASAzure, which seems to only accept OAuth2 authentication. Could you confirm if this is the case or suggest a better approach?

[v-shex-msft]

Hi @danjo914,

AFAIK, Azure AS can support to use service principal, have you configuration this on your Azure AS side before you test?

Learn how to add a service principal to Azure Analysis Services admin role | Microsoft Learn

Regards,

Xiaoxin Sheng

[danjo914]

There are two authentications required, one the authentication to the ASAZURE, which yes, we are using the service principal already, but the second is the authentication to the data source located inside the .bim file. That does not seem to support managed identities nor service principals. We get "This authentication type is not supported".  It appears that only OAUTH2 can be used inside the .bim to connect to the sql endpoint inside a Microsoft Fabric Workspace with a token expiry of a single day. It would be great if I am incorrect. Happy to hear of other ways to set up authentication to the datasource.

[v-shex-msft]

HI @danjo914,

Did you means the multi-fact authorization? AFAIK, if you enable this on Azure AD, it will require you to provide additional trusted authorization to verify and confirm the security of these operations.

Enable Microsoft Entra multifactor authentication - Microsoft Entra ID | Microsoft Learn

Regards,

Xiaoxin Sheng