Forum Discussion
Setting up a connection using a Virtual Network Data Gateway to an Azure SQL Database
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.
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..
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.
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.
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.
