3 Ways to 'Direct Query' Azure SQL and Synapse using Azure Active Directory SSO within Power BI
When I first started using Power BI, one of the things I never really needed to think about much was user management/identity stores. Azure Active Directory is just 'there' in terms of logging in and out of Power BI which also instantly logs you into other Microsoft Apps at the same time! With this convenience, Database level Security within Power BI works like a charm!
Direct Query with the help Azure Integrated login is really useful because it passes all the security features implemented within the database to the report without having to recreate them in Power BI.
So how can we make use of this? Well I have looked at 3 options:
The first thing you will need to think about is whether your SQL /Synapse instance has a public endpoint or not. If your instance is public, the process is very simple.
Public End Point - Very Simple!
When you publish the dataset you need to choose the Authentication Method as OAuth2 and ensure that the only check box in the dialogue is checked:
And that's it, when a user logs into Power BI, their credentials will also be passed to the database via SSO.
Private Endpoint - 2 Options
So the next scenario is if the endpoint is private. In other words, the database has no exposure to the internet. For this scenario there are 2 options:
Option 1 - Use an On Prem Gateway
Within Azure, create a VM within the same region and VNET as your datasource. Install the standard Power BI Gateway on the VM.
The gateway is used to connect to data sources on prem as well as many data sources on the cloud inside a virtual private network such as SQL Managed Instance or Synapse.
Once installed, follow the simple on screen configuration to connect the gateway to your Power BI tenant.
To benefit from Azure SSO you will need to activate it within the Power BI Tenant admin settings:
Within the Power BI Tenant, go to the admin Portal
Find the switch below and enable it
This may take a few minutes before you see any changes to the gateway options.
Once activated, navigate to the published direct query Dataset settings and switch on the Use Gateway option
Once this is switched on, you will need to create a new gateway datasource. When creating the data source, ensure you select the Datasource Type 'SQL Server'. You will select this for all SQL Server types including Azure SQL Managed instance and Azure Synapse. After you have typed in the server details, select the Authentication Method as OAuth and in Advanced settings, select the option User SSO via Azure AD for DirectQuery queries. This option will only be available if you enabled it within the tenant admin settings.
Now the data source is configured you will need to map it to your dataset Datasource:
Once this is complete, users login details will now be passed to the Azure SQL database via the gateway and into the private endpoint. This is a great option if the dataset is not published inside a Premium Workspace.
Option 2 - Use the new VNET support currently in Public Preview (Premium Only) If your datasets are on a premium workspace, I strongly recommend you try this option because you will not require the provision of a VM to install the gateway.
Liping Huang as produced an easy to follow tutorial on how to set this up:
For a quick summary, the config involves the following steps:
Registering the Microsoft.PowerPlatform resource provider to your subscription
Creating a new subnet to the VNET you want supported and delegate it to the Microsoft.PowerPlatform/vnetaccesslinks service
Add the additional Virtual Network Data Gateway within the Power Platform admin centre (microsoft.com)
Configure the dataset connection properties using the additional "vnet" option
Ensure you check the box Use SSO via Azure AD for Direct Query as before.
So there you go, really simple to achieve! I am really starting to like the convenience of Azure AD SSO!