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 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.
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 ,
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