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