Forum Discussion
Connect from PowerBI Desktop to Azure SQL database using EntraID
I have seen some old posts saying it is not possible to connect from PowerBI Desktop to Azure SQL databases using EntraID. Is this still the case?
What is the best work around from a security perspective? We are only meant to use AD accounts for SQL database access for security reasons.
I've tried using a paginated report or creating a a report in the PowerBI service instead for what I need (a report based on a stored procedure that can be embedded in a Sharepoint Site that is a tab in Teams) but neither of these work (paginated reports won't embed without the user having to sign in and I can't connect from the PowerBI service directly to the SQL database so I can't run a stored procedure).
Can anyone help at all?
Hi RosG
Connecting from Power BI Desktop to Azure SQL databases using Entra ID (formerly Azure AD) is indeed possible. Here are the steps and best practices to ensure a secure connection using AD accounts:
Connecting Power BI Desktop to Azure SQL Database Using Entra ID
Open Power BI Desktop:
- Go to Home > Get Data > Azure > Azure SQL Database.
Enter Server and Database Information:
- Provide the Server name and Database name.
Authentication Method:
- Select Microsoft Entra ID (formerly Azure AD) for authentication.
- Ensure you are signed in with the appropriate AD account.
Firewall Configuration:
- Ensure that your Azure SQL Database firewall settings allow access from Power BI Desktop. You might need to add your client IP address to the allowed list.
Best Practices for Security
Use Managed Identities:
- If possible, use managed identities for Azure resources to authenticate to Azure SQL Database. This approach eliminates the need to manage credentials manually.
Enable Single Sign-On (SSO):
- After publishing your report to the Power BI service, enable SSO for the data source. This ensures that the user’s AD credentials are used for database access1.
Use Private Endpoints:
- Configure private endpoints for your Azure SQL Database to ensure that traffic between your Power BI service and the database remains within the Azure network.
Alternative Approaches
Paginated Reports:
- While paginated reports require users to sign in, you can use Power BI Embedded to embed these reports in SharePoint or Teams. This approach provides a seamless experience without requiring additional sign-ins.
Power BI Service:
- If direct connection from Power BI service to Azure SQL Database is not feasible, consider using an On-premises Data Gateway. This allows you to connect securely to your database and run stored procedures.
If you need additional information pls go through below links
https://community.fabric.microsoft.com/t5/Desktop/Connect-to-Entra-ID-data/td-p/3516002
Thanks!
4 Replies
Thank you suparnababu8 - I'm in.
As potential help for others, step 3. Authentication Method wasn't appearing for me.
However, I fixed this by going to File/Options and settings/Data source settings and using 'Clear Permissions' to clearout the old connection attempts that hadn't worked.
Hi RosG ,
Glad to hear that you found a solution yourself, please mark your reply as solution so that more users facing the same or similar problem can find a solution faster, thanks!
Best Regards,
Dino Tao
Hi RosG
Connecting from Power BI Desktop to Azure SQL databases using Entra ID (formerly Azure AD) is indeed possible. Here are the steps and best practices to ensure a secure connection using AD accounts:
Connecting Power BI Desktop to Azure SQL Database Using Entra ID
Open Power BI Desktop:
- Go to Home > Get Data > Azure > Azure SQL Database.
Enter Server and Database Information:
- Provide the Server name and Database name.
Authentication Method:
- Select Microsoft Entra ID (formerly Azure AD) for authentication.
- Ensure you are signed in with the appropriate AD account.
Firewall Configuration:
- Ensure that your Azure SQL Database firewall settings allow access from Power BI Desktop. You might need to add your client IP address to the allowed list.
Best Practices for Security
Use Managed Identities:
- If possible, use managed identities for Azure resources to authenticate to Azure SQL Database. This approach eliminates the need to manage credentials manually.
Enable Single Sign-On (SSO):
- After publishing your report to the Power BI service, enable SSO for the data source. This ensures that the user’s AD credentials are used for database access1.
Use Private Endpoints:
- Configure private endpoints for your Azure SQL Database to ensure that traffic between your Power BI service and the database remains within the Azure network.
Alternative Approaches
Paginated Reports:
- While paginated reports require users to sign in, you can use Power BI Embedded to embed these reports in SharePoint or Teams. This approach provides a seamless experience without requiring additional sign-ins.
Power BI Service:
- If direct connection from Power BI service to Azure SQL Database is not feasible, consider using an On-premises Data Gateway. This allows you to connect securely to your database and run stored procedures.
If you need additional information pls go through below links
https://community.fabric.microsoft.com/t5/Desktop/Connect-to-Entra-ID-data/td-p/3516002
Thanks!
As a follow up, having got to the point of being able to create a PowerBI report connected to the data I needed using PowerBI Desktop, I still struggled to get a PowerBI report to work the way I wanted (I wanted to run a stored procedure with a default date of yesterday, but with the ability for the user to change the date parameter from the report without having to be trained on how to do this in a PowerBI report).
I have ended up embedding in to a PowerBI report, a PowerBI paginated report that is created off the stored procedure run with the default date of yesterday. Whilst the PowerBI paginated report won't embed in to a Sharepoint site viewed from Teams without requiring the user to open the report and sign-in, it is immediately viewable when embedded into the PowerBI report. The user has to open the report to change the variable, but this seems the best solution for my use case.
