Dataflow connection using power bi gateway

[Ledi]

I am getting this connection error: "object reference not set to an instance of an object" when connecting to sql database on prem using power bi gateway in dataflow gen2

My user has the connection creator role on the gateway and admin role on the data source itself. 

I have other reports using the data source without issues. This is my first dataflow.

[v-pnaroju-msft]

Hi Ledi,

We are delighted to know that the issue has been resolved on your end by designing a REST API. Kindly mark the response that resolved your query as the accepted solution, as it will assist other community members facing similar challenges in finding solutions more effectively.

Please continue to utilise the Fabric Community for any assistance regarding your queries.

Thank you.

[Ledi · Accepted solution]

I tried the solution proposed above and it didn't resolve my issue. I was also testing the connection using get data in power bi report builder and that did eventually work but hit a blocker that power bi gateway connection is not supported yet. Couldn't get dataflow to connect at all but I believe I would ran into the same issue on tool limitation so we decided to design a rest api for the dataset and connect via web api so we could take advantage of the transformations offered by power bi which was always the goal.

Thanks for all the response.

[v-pnaroju-msft]

Hi @Ledi,

We would like to inquire if the solution offered by @rohit1991has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[Ledi]

No, it did not resolve it. We've also moved on using other options. I was exploring this because I wanted to use it in power bi report builder. Turns out power bi gateway connection is not supported so moved to other options by creating endpoint on the data and calling an api instead and take full advantage of the power bi transform available which is what I needed in going this route in the first place.

[rohit1991]

hi @Ledi

The "object reference" error likely stems from a gateway configuration or compatibility issue.

Steps to resolve:

1. Verify the SQL data source in Manage Gateways (correct server, DB, credentials).
2. Test the connection using Power BI Desktop via the same gateway.
3. Ensure your SQL data source is compatible with Dataflow Gen2.
4. Update the gateway to the latest version and confirm your user roles match Dataflow Gen2 requirements.