Understanding (or not) on-premise gateways

[Vantage111]

I have a gateway setup primarily to allow me to share SQL Server Views on an Azure SQL server with other PowerBI users.

The resulting Semantic Model has been shared with the end user and they have been added as a user to the gateway.

The user can quite happily add my data model to a new PowerBI project, create and publish reports.

The problems arise when she adds in an XLSX worksheet which is located in sharepoint as a second connection. Within PowerBi desktop both connections live together happily but the moment it is published to a Workspace there is an error with the SQL data:

Definately not got my head into gateways.....

[Anonymous]

Hi @Vantage111 ,

For a connection to Azure SQL DB to use Basic authentication you need to add it to the (Enterprise, or now Standard) Gateway. That's also how we set it up. After that you can select the gateway in the settings of the dataset and it works fine.

To learn more about the settings, you can refer to the following documentation: Securing the connection between Power BI and Azure SQL Database

If the problem persists, click see details to view the detailed error message.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vantage111]

Thanks for your response AdamK.

My user doesn't have an issue trying to connect to either my SQL dataset or the Sharepoint XLSX file, it's only when she uses these together in the same report.

Looking at the errors it appears to be that when you have connected to the PowerBI Semantic Model this is a direct query but the moment you add a second dataset PowerBI asks you to convert to an imported dataset.