Oct 2020 - Direct Query SQL problem

Hi Josef,

Did some more testing this morning.

For me it only works if the checkbox “Store datasets using enhanced metadata format.” is unticked.

Test 1 – With checkbox “Store datasets using enhanced metadata format.” ticked.

Using latest version of Power BI Desktop Report server (2.86.1321.0 64-bit (oktober 2020)).

Get Data -> SQL Server Database:

Type servername, database. Tick “DirectQuery”. Add following SQL statement. “SELECT Test=1”. Press OK.

Add table-visual. Drag column “Test” to Table.

Save to report server (version 15.0.1104.264).

Manage report -> Data sources. Add credentials. I’ve tested with both Basic Authentication and Windows Authentication. Same error.

Connectionstring looks like:

servername;databasename.

Get the same error like before. In Swedish, but something about trouble connecting to Analysis services.

Test 2 – With checkbox “Store datasets using enhanced metadata format.” unticked.

Using latest version of Power BI Desktop Report server (2.86.1321.0 64-bit (oktober 2020)).

Untick checkbox “Store datasets using enhanced metadata format.” Press Ok and then close and restart Power BI Desktop.

Get Data -> SQL Server Database:

Type servername, database. Tick “DirectQuery”. Add following SQL statement. “SELECT Test=1”. Press OK.

Add table-visual. Drag column “Test” to Table.

Save to report server (version 15.0.1104.264).

Manage report -> Data sources. Add credentials.

I’ve tested with both Basic Authentication and Windows Authentication. Both works fine.

Connectionstring looks like:

data source=servername;initial catalog=databasename;persist security info=False

I´ve tested with both version 2.86.961.0 64-bit and 2.86.1321.0 64-bit of Power BI Desktop Report server and both on version 15.0.1104.239 and 15.0.1104.264 of Power BI Report Server.

josef78, do you have the “store datasets using enhanced metadata format.” Ticked or unticked??

For me it seems like the connectionstring might be the problem?