Setting up a connection using a Virtual Network Data Gateway to an Azure SQL Database

[RosG]

I am trying to connect our PowerBI Service to an Azure SQL Server database.

I can connect the semantic model for a Report using Cloud connections and I have managed to set up a virtual network data gateway and connect using a basic sql user.

However, reading best practice, I think I should be able to set up a Service Principal for authentication for the connection.  Is this possible now?

I have created an Enterprise application and given it PowerBI Tenant ReadWrite.

I've made it a user for the SQL Server and database and given it data reader permissions.

I have given it admin rights on the Workspace containing the report and added it as a user to the Connection I'm trying to configure.

I've successfully connected to the PowerBI service using Powershell with the Service Principal Account and Get-PowerBIWorkspace brings back the PowerBI workspace it is admin for.  If I try and run Get-PowerBIDataset I get a 'forbidden' error - but I'm not sure that there is a PowerBIDataset when you are trying to use an AzureSQLDatabase?

When I try and input the Service Principal information into the authentiation method for the Connection I'm trying to create for the Virtual network data gateway, I get back the error DM_GWPipeline_Client_ServicePrincipalOAuthTokenGenerateFailedError with Status Code 400

I feel very close, but Copilot seems to have run out of suggestions on what to do next!

[v-sshirivolu]

Hi @RosG ,

Thanks for the detailed post. The error DM_GWPipeline_Client_ServicePrincipalOAuthTokenGenerateFailedError (400) indicates that Azure AD couldn’t generate a token for your service principal. This usually happens if the app registration is missing the Power BI Service permission Dataset.ReadWrite.All (Application permission) with admin consent granted, or if the service principal hasn’t been added as an admin or contributor on the VNet data gateway in the Power BI admin portal. Please also make sure that your Azure SQL Server has an Azure AD admin configured, and that the service principal has been created in the database using the statement:
CREATE USER [AppName] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [AppName];

When setting up the connection through the VNet data gateway, use OAuth2 as the authentication method and provide the correct Client ID, Client Secret, and Tenant ID. Once these items are configured, try connecting again..

[RosG]

Hi v-sshirivolu,

I've done all of the above. 

Not only can I not get the connection working with a service principal, I also can't get it to work using OAuth with my own Azure credentials.  So, from SQL management studio, I can create a connection using 'Microsoft Entra MFA', but I can't use the same credentials via the VNet data gateway selecting OAuth 2.0 authentication.

I might have misunderstood, but it seems to me that I can authenticate with the SQL server via the gateway using a SQL server account, but not using Entra and I can authenticate using Entra but not via the gateway, so I'm not sure where to look next.

[v-sshirivolu · Accepted solution]

Hi @RosG ,
The behavior you’re seeing is expected with the current VNet Data Gateway. The gateway doesn’t support Azure AD / Entra (OAuth) authentication for SQL Server, which is why your Entra MFA login works fine in SSMS but not through the gateway. SSMS supports modern authentication, whereas the VNet gateway can only connect to SQL Server using a SQL username and password. Because of that, Entra MFA succeeds in SSMS, fails through the gateway, and SQL authentication is the only method that works when using the VNet gateway.

For reference, here are the Microsoft docs:

VNet Data Gateway
Use virtual network data gateway and data sources in Power BI | Microsoft Learn
Azure SQL – Entra Authentication Overview (explains why SSMS works)
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview

If you require Entra-based authentication, the supported alternative is to use Managed VNet / Dataflows Gen2, which do support Entra ID and Managed Identity, unlike the VNet Data Gateway.

[v-sshirivolu]

Hi @RosG ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @RosG ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[RosG]

An update.

Having done the things recommended below, without success, I raised a Support ticket with Microsoft (always the last desparate attempt).

They advised adding MicrosoftAzureActiveDirectory as a Service Endpoint for the Subnet despite this not being mentioned in the Learn documentation (Create virtual network (VNet) data gateways | Microsoft Learn).

But it worked!!!!!!

For a bit.... 😞

Now I can see the Gateway in PowerBI and I have a connection to it - but when I try and use it for a report, I'm back to a spinning circle of death.

This seems classic Microsoft - put something out there which suggests it is possible but then when it comes down to it, it just doesn't work.

We cannot be the only people who are trying to generate PowerBI reports from a database hosted on an Azure SQL Database - I would have thought this would be Microsoft bread and butter - so why do we have to spend hours and days trying to get it to work only to conclude that it just doesn't despite the fact it is meant to and all there documentation says that it will.

I could raise a ticket again, but I'll just be stuck in that horrendous process where despite saying that you want to only be contacted by email they insist on phoning you - can't solve the problem and move you around - and then refuse to let you off the phone unless you give them a 5 out of 5 ranking for their service. 

[v-sshirivolu]

Hi @RosG ,
Unfortunately, VNet Data Gateway support for Azure SQL with Entra/AAD authentication is still not fully stable, even though the documentation suggests it should work. Microsoft seems to be actively working on this area, so waiting for an upcoming official update or launch may be the realistic option. Until then, SQL authentication or Managed VNet / Dataflows Gen2 remain the only reliable workarounds.

[GilbertQ]

Hi @RosG 

Can you have a look at the log on the V net gateway to make sure that it is going to your Vnet gateway before going onto just to make sure that it is at least getting that far? If it is getting to the Vnet gateway then the next step is to have a look at your Azure SQL DB to make sure to see if it is getting there and if there is some kind of permissions issue.

[RosG]

Thanks GilbertQ - I've tried to turn on logging and looked for logs - but I'm struggling to work out where to turn them on and how to look for them.  Is the vnet data gateway an object in PowerBI or Azure - I can't find it in Azure so maybe I need to turn on logging in PowerBI?  On SQL, I've turned on logging in Azure but I don't seem to be getting any logs at all?  My initial question has now turned into a 'how do you collect logs for PowerBI and Azure SQL question, I'm afraid.