Error: We couldn't connect to your DirectQuery data source

[septagon77]

I am connecting to a PostGres database through a Transport connection.  I am able to connect to the server and see the tables in the database.  I am able to select a table and see a preview of the data.  I am using a DirectQuery connection.  Once I hit the load button to bring the table into PowerBI I receive the above error.  I am able to see the table in the Model View but not the Report View.  Any action that works with the model throws the same error again.  Any ideas as to the cause of the error?

[Anonymous]

Hi @septagon77 ,

Double-check that your server and database names are correct and that you have access to them.

1. Check that all data models have been imported into Power BI. If there is a Direct Query data model in the report, the local computer should have access to the data source.

2. Try opening a new powerbi desktop and clearing the data source permissions.

Then, reopen and reconfigure your pbix file. Ensure that your local machine has access to your data source.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[septagon77]

Clara,

Thank you for the reply, but perhaps you did not understand the problem.  The database credentials are correct and my desktop can access the data source.  I cannot interact with any tables imported into the model without throwing an error.  As a result no objects from the Model view are available to use in the Report view therefore I cannot build a report.  I have tried both options of Import or DirectQuery.  Both fail. 

[Anonymous]

Hi @septagon77 ,

the PostgresSQL connector doesn't work out of the box, you need to do quite a few things first

1. Install Npgsql which allows .NET access to PostgresSQL. It can be installed using the Pakagae Administrator Console in Visual Studio; detailed instructions are here: https://www.nuget.org/packages/Npgsql/

You need to connect over SSL to AWS which isn't setup by default (on my Windows 10 machine anway)

2. Download the AWS public key from here https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

3. Convert that to a certificate using https://www.sslshopper.com/ssl-converter.html (convert to PKCS#7/P7B)

4. Import that certificate to the Trusted Room Certificate described here http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm

Finally, you can try setting up an ODBC connection.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-Connect-to-an-ODBC-Data-Source-From-...

Best Regards,

Clara Gong

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

[septagon77]

Clara,

None of that information you have provided is useful.  I shall go through the issue in greater detail.

I am running Power BI on a Win10 Pro version 22H2.  I am connecting to a PostGres v 12.17 Database through a Teleport v 15.4.4 connection.  I am able to connect to this data source using tools like DBeaver run queries successfully.  This confims the connection to the data source is valid.

I can launch Power BI and create a connection to the same PostGres data source using the PostGres Database connector.  I am able to enter the server and database information which then brings me to the Navigator dialog box.  On the left hand side I am able to see the server and all the tables available to me.  If I mouse-click on a table I am able to see a data preview in the right hand side of the Navigator dialog box.  I select a table and hit the <Load> button in the lower right hand corner.

Once I hit the button I see the Create Connection Message box.  As soon as the Create Connection Box disappears another Message box appears stating:

Something went wrong 

We couldn't connect to your DirectQuery data source or internal model. Double-check that your server and database names are correct, and that you have permission to access them.

Inside PowerBI in the Report View the Data blade on the far right side contains no objects, but if you switch to the Model view the table that was selected is visible.  You can even do Edit Query and see the data in the table in the Power Query Editor.  You can modify the query but when you hit "Close & Apply" PowerBI throws the same error again.

In summary, PowerBI is able to make a connection pull the meta data for the available tables and show previews of the data in the tables.  There has to be a final handshake between PowerBI and the data source that would allow the Model to be made available in the Report View.  This is where the error occurs.

[Anonymous]

Hi @septagon77 ,

After reading your description carefully, I think there may be an unexpected situation due to a connector issue.

As of the December 2019 release, NpgSQL 4.0.10 is provided with Power BI Desktop, so no additional installation is required. The GAC installation will replace the version supplied with Power BI Desktop, which is the default. Therefore, please try to download the latest version of power bi desktop and if it is already the latest version, try to uninstall and reinstall.
More detailed information can be found in the documentation:
Power Query PostgreSQL connector - Power Query | Microsoft Learn

Alternatively, you can try connecting to the PostGres database using dataflow. You can add and edit tables in the dataflow directly from the workspace where you created the dataflow and manage the data refresh schedule.
More details can be found in the documentation: Creating a dataflow - Power BI | Microsoft Learn

Best Regards,

Clara Gong

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

[septagon77]

   Clara,

Maybe you did not read my explanation carefully enough.  Of course, it is some kind of "unexpected situation due to a connector issue".  That should have been obvious from my original post.  I would not have needed to post my issue here if it was something simple like a uninstall/reinstall which I already have done multiple times.

It is clear that PowerBI can establish a temporary connection while it retreives metadata from the database to provide me a list of tables as well as a data preview.  Quite quickly I might add.  It is when PowerBI goes to establish this connection  in the model that something goes wrong.  

There is some kind of code collision either with one of the dll that is installed with PowerBI or one that already exists in the Win10 platform.  I know this, because I have the same exact setup on a Win11 desktop, and it works.

Something happens after you hit the <Load> button and it goes to create the connection in the model.  It does not instatiate the connection details  properly.  I have reviewed the four pages of stack trace and found nothing of use.  I have turned on Enable Tracing and reviewed all the files in the Traces, Diagnostics, and Performance.  Nothing that seems to be of use.  

Dataflow is not an option that is available in the PowerBI version I am using.  Using an ODBC connection has not worked either even though it works for Excel using Microsoft Query.