topic Connect Power Bi Dataflow to a Microsoft SQL Server Database in Report Server

Connect Power Bi Dataflow to a Microsoft SQL Server Database

Christi — Thu, 09 Feb 2023 14:11:25 GMT

Hi all,

I am trying to connect my Power Bi service directly to a specific SQL Server Database. Precisely, I would like to be able to create a dataflow (in the Power Bi app) that can directly be connected to a SQL server database and thus, pull the data tables of the database into my dataflow. Apparently, even if I use the connection settings and credentials of the database, the connection can not be created ( based on my Session ID).

The process I am following is presented below:

Go to Power Bi app -> Choose a Workspace - > Crete a new dataflow -> Add new tables -> Choose data source -> SQL Server Database -> Use the credentials of my database -> Result: Unable to create an OAuth connection (for my Session ID) - Europe

Notice: I do not have access to the Azure SQL Server, I do only use the Microsoft SQL Server Management Studio

Any thoughts and suggestions on that would be appreciated.

Re: Connect Power Bi Dataflow to a Microsoft SQL Server Database

jaweher899 — Fri, 10 Feb 2023 10:28:16 GMT

Based on your description, it seems like you are trying to connect to a SQL Server database that is not hosted on Azure, which requires an OAuth connection. OAuth is a security protocol that enables you to authorize a third-party application to access your data without sharing your password.

To resolve this issue, you have a few options:

Host your SQL Server database on Azure: By hosting your database on Azure, you can use Azure Active Directory (AAD) to authenticate the Power BI dataflow, which will support OAuth connections.
Use a DirectQuery connection: Instead of using a dataflow, you can create a Power BI report that uses DirectQuery to connect to your on-premises SQL Server database. This will allow you to connect to your database without using OAuth, but it has limitations, such as a lower refresh frequency and a more complex setup process.
Use a data gateway: You can install a data gateway on a machine that has access to your SQL Server database and configure it to act as a bridge between Power BI and your database. This will allow you to use Power BI dataflows to connect to your database and refresh the data on a regular basis.

I hope these suggestions help you resolve your issue. If you need further assistance, please let me know.

Re: Connect Power Bi Dataflow to a Microsoft SQL Server Database

agonzalezc — Fri, 12 May 2023 17:58:04 GMT

I have the same problem. In my case I am trying the third option. I have a personal Gateway installed in a VM and when trying to connect to a SQL Server database it always asks me for connection credentials -> connection: "create new connection". and in data gateway: (none). And it doesn't allow me to change neither the credentials nor the gateway.

Re: Connect Power Bi Dataflow to a Microsoft SQL Server Database

DanMoniz — Wed, 07 Jun 2023 08:56:31 GMT

Hi Christi ,

I am having the same issue. I am also trying to create the Data FLow with tables using the connection to the Microsoft SQL Server.

Were you able to solve it in any different way to the suggested by jaweher899 ?

Thanks