Connection to a PowerBI datamart in Azure Data

[mayankdhami]

Hi,

I am simply looking to connect an existing PowerBI datamart as an Azure SQL database in SQL Server Management Studio, which I can since there is an option for authenticating with Microsoft Entra with MFA. But when I try to connect to this datamart in Azure Data Factory as an Azure SQL data source, I am very confused as to how to connect to it.

There is no SQL username/password to use with SQL Authentication method (since Azure SQL database for a datamart is automatically created) and rest of the methods are quite complex to understand and I'm not sure how they work.

Any help appreciated.

@amitchandak @lbendlin 

[v-kaiyue-msft]

Hi @mayankdhami ,

Azure Data Factory is a Microsoft cloud ETL service for serverless data integration and business processes. It supports many source and target connectors, including Azure SQL Database. You can use managed identity authentication to connect to Azure SQL from Data Factory.

1. Enable managed identity on Azure Data Factory.

2. Grant the Azure Data Factory managed identity access to Azure SQL Database.

3. Grant Azure Data Factory managed identity access to Azure SQL Database.

4. Create a linked service to Azure SQL Database in Azure Data Factory.

For more details, please refer to the link: Connect Azure SQL from Data Factory using Managed Identity - AzureOps

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mayankdhami]

Hi @v-kaiyue-msft 

Thanks for explaining the process of managed identity. I am pretty sure this will work perfectly fine with an Azure SQL database created on the Azure portal. My issue is I am trying to connect to the automatically created Azure SQL database which is created as a SQL endpoint of a PowerBI datamart. There is nowhere to do Step 2 on that database.