Power BI dataflow Gen1 needs Gateway to connect to Azure SQL DB

I’m in the progress of migrating some legacy stuff at a client, and in their Power BI environment there are still quite some Power BI dataflows Gen1. I had migrated an Azure Synapse Dedicated SQL Pool to an Azure SQL DB (much cheaper for their volume of data), and in the dev/test environment all dataflows were switched correctly to the new database.

However, in production, the dataflows only wanted to connect to the Azure SQL DB production database through a gateway. Weird, right? An Azure SQL DB is in the cloud, no gateway should be needed. When I tried to create a new connection that didn’t use a gateway (from within the dataflow itself), I got the following error message:

Connection could not be created. This may mean it is not accessible from this network or at this URI, or that a gateway is required to access this data source.

To get rid of this issue, I created a connection to the production Azure SQL DB (without gateway) in the Power BI connections menu.

The only difference (aside from the gateway config), was that the database name was in lowercase, while the old connection used uppercase. I’ll tell you why this matters in an instant. In the dataflow, I went to the options menu.

There, I went to the data load settings in the Project section.

In the dropdown menu, I changed the gateway to (none).

The connection itself – in the first step “Source” of the query – still doesn’t work, because it’s still referencing the old connection with a gateway.

I changed the database name to lowercase (because Power Query is case sensitive, while SQL Server usually is not).

Since this is a “new connection” for the dataflow, you have to configure it again. This time, you can select the connection without a gateway, which we created earlier.

The (none) indicates that no gateway is used, as it should be. The query is now fixed, and you can repeat the process of changing the database name to lowercase for the other queries. Since you already configured the new connection in the first query, that should be enough to fix those as well.

I had a similar issue with a connection to Azure Data Lake Storage as well, but there I couldn’t use the trick of changing to lowercase. When I changed the query options to not use a gateway, I couldn’t select a connection without a gateway from the dropdown, as the dataflow only presented me connections with a gateway (one of the many reasons why I hate dataflows). I also couldn’t create a new connection (without gateway) to Azure Data Lake Storage, as it would give me an error stating “cannot create more than one connection to the same cloud data source”. Quite frustrating. The only thing that did work was to remove all connections to that blog container, and create a brand new connection without a gateway from within the dataflow.