Oct 2020 - Direct Query SQL problem

[Steffe]

Hi,

We have a problem with reports using DirectQuery against SQL Server. This seems to arrived after we upgraded to october 2020 version of Power BI Report Server and Desktop.

It works if you use May 2020 version of Desktop and upload to oct 2020 report server, but not if you use oct 2020 version of Desktop and upload to oct 2020 report server.

Steps to reproduce:

1. Create new Power BI Report using version ("Oct 2020 for Power BI Report Server").

2. Get Data->SQL Server->Type servername, databasename, choose DirectQuery, type a SQL query.

3. Add a table Visual to the report and drag a field from the source.

4. Upload to Report Server.

5. Start a webbrowser and go to report portal.

5. Click Manage on the report -> Click "Data sources". Under Credentials. Choose "Windows Authentication", tick "Using the following credentials" and type in user name and password. Save.

6. Browse the report and you get an error message. See sceenshot below (sorry for the error message beeing in swedish, but it looks like it tries to connect to analysis services which it shouldn´t cause this is an SQL source).

One big difference that I can see is that the "Connection string" that you can se in the report portal under "Manage"->"Data source" differs depending if you created and upload it with May 2020 or Oct 2020.

With Oct 2020 it looks like: "<servername>;<databasename>". Example: server1.domain.com;db1 if the servername is server1 and the database name is db1.

However in May 2020 the connectionstring looks like: data source=server1.domain.com;initial catalog=db1;persist security info=False

If you instead go through these steps using May 2020 and upload to report server with version 2020 oct, it works.

Is this a known issue?

[iamsordz]

@Steffe @Erikvan_D 

I've raised a similar post and PowerBI employee provided workaround here. Just FYI. (https://community.powerbi.com/t5/Report-Server/Error-connecting-to-Azure-Analysis-Services-after-Pow...)

Thanks.

[Erikvan_D]

Thanks, quickly tried it but so far no succes. But we don't work with Azure but fully on premise. Will test some more later this week when I have more time.

[Erikvan_D]

Just installed the January release and ran a quick test. The bug still seems to be present. I just cannot get my reports to work with Basic Authentication. Which worked absolutely fine before the October release, so before the connection strings changed! No matter if I do or do not tick the box for the "enhanced metadata format". Luckily Windows Authentication still works so our production dashboards are not affected, but we also need Basic Authentication to work for development and testing.

I plan to do some more testing in de next few weeks as my company wants to update quickly to the new version.

[iamsordz]

Got similar problem.

After upgrade of PowerBI report server from May 2020 to October 2020, PowerBI reports with Azure Analysis Services connections are now not working and showign error below:

[Steffe]

Hi @mgmeyer 

Thanks for looking into this.

I just sent you a PM with attional info. 

[Steffe]

Hi again.

Today I upgraded our environment with the latest build of Power BI Report Server. The issue still remains.

Is this an issue that someone at Microsoft is looking at? 

[josef78]

Stefee, upgraded also you Power BI Desktop to latest version?

I'm not able reproduce this issue, I make DQ report connected to another MS SQL server, using basic auth (connection string looks like servername;databasename) and it works well.

[Steffe]

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?

[mgmeyer]

@Steffe i've tried a couple of the scenarios you've mentioned above but have not been able to reproduce the issue. Couple questions:
1. When you go to the PQ editor (Transform Data) how many queries to do you see there?

2. Would you be willing to private message me with a simple repro report that I can try?

3. Also can you attach or send errors or pertinent log entries from the latest RSPowerBI* and ASManagedRoot* logs in the LogFiles folder?

Thanks!

[kiwipeet]

We've experienced something similar. Reports deployed using October IDE don't have full or complete connection strings. We've reverted to previous IDE and it works fine.

[Steffe]

Some additional info.

There is info written in the log-file ASManagaged*.log when we get this error. In May version where it works nothing is written in ASManagedRoot.

ASManagedRoot looks like below (I´ve masked and changed some sensitive stuff). There are two things that I´m a bit currious about.
1. Provider Type: ProviderTypeCloudSQLAzureManaged. We are using on-preem SQL Server.
2. Username: ???????????????????????o???????? . Can this be the problem?

2020-11-24 15:25:16.8938|INFO|153|Getting data sources in the model for datasourceId = 123 and databaseName = 456
2020-11-24 15:25:16.8938|INFO|153|DetermineProviderAndConnectionString - Connection Type: DQNative; User: my_serviceaccount@domain.com; Root activity ID: 789.
2020-11-24 15:25:16.8938|INFO|153|BuildNativeConnectionString - Auth type: Windows
2020-11-24 15:25:16.8938|INFO|153|GetV3ConnectionString - Engine connection string: Data Source=server1.domain.com;Initial Catalog=db1; User principal name: my_serviceaccount@domain.com; Provider type: ProviderTypeCloudSQLAzureManaged; Root activity ID: 789.
2020-11-24 15:25:16.8938|INFO|153|Getting data sources in the model for datasourceId = 123 and databaseName = 456
2020-11-24 15:25:16.8938|INFO|153|GetV3ImpersonationInfo - Datasource: Microsoft.PowerBI.ReportServer.PbixLib.Parsing.PbixDataSource; Auth Type: Windows; Username: ???????????????????????o????????; Needs Impersonation: True; Root activity ID: 789.

In the log file RSPowerBI*.log we get below error (also a bit masked):

2020-11-24 15:25:16.9407|ERROR|54|PBI.Win.DataExtensionException|ExceptionType:Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException;DataExtensionErrorDetails:Details: Message=Failed to get CSDL., HResult=0x80004005, ErrorCode=QuerySystemError, Language=en-US, ProviderErrorCode=0xC1010000, ProviderErrorMessage=[58EBD76BCF5D11E62632F81F91552FC4B4D7DAFC062F8213FCC33F3E84F463A5], ProviderGenericMessage=[The following system error occurred: ], ErrorSource=PowerBI, ErrorSourceOrigin=MsolapWrapper, OnPremErrorCode=, InnerErrorDetails=[Type=MsolapWrapper.MsolapWrapperException, Message=Failure encountered while getting schema];stackTrace: at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
at Microsoft.PowerBI.ReportingServicesHost.LocalReportProcessingHost.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent);message:GetModelMetadata;| RequestID = 999 ClientSessionID = 888
2020-11-24 15:25:16.9407|ERROR|54|PBI.Win.ReportingServicesHostTrace|type:Error;message:library:Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: , Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 456. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
at MsolapWrapper.Utils.ThrowErrorIfHrFailed(Int32 hr, String message)
at MsolapWrapper.CDbSchemaRowsetWrapper.ExecuteSchemaRowset(Guid schema, Object[] restrictions, PropertySetCollection propSets)
at MsolapWrapper.SchemaCommand.ExecuteReader(Guid schema, Object[] restrictions)
at MsolapWrapper.SchemaCommand.GetModelMetadata(Object[] restrictions)
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
--- End of inner exception stack trace ---
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
at Microsoft.PowerBI.ReportingServicesHost.LocalReportProcessingHost.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
--- End of inner exception stack trace ---;;| RequestID = 999 ClientSessionID = 888
2020-11-24 15:25:16.9407|ERROR|68|Failure in conceptualschema| RequestID = 999 ClientSessionID = 888 Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source '71b5c356-58bd-46d1-be92-23f75d65edf8_-463211041'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema

[Erikvan_D]

We have exactly the same bug except that we use basic authentication. The exact same error occurs while the connection itself tests succesful under "Manage data sources". I also noticed the difference in the connection string but no way to alter it on the server side. The strange thing is that sometimes it works, but the next day the same, untouched, report does not work anymore. In the past this worked flawlessly.

Downgrading to May is not an option for our organisation. 

[Steffe]

Thanks for the reply @Erikvan_D .

"Good" to know that this isn´t something that only appears at our server.

Would be nice to know if Micorsoft is working on this bug and if we can expect a fix soon?

[v-xuding-msft]

Hi @Steffe ,

This issue is caused by a known issue in September version of PBI Desktop. 

We usually test wihin the last version of Desktop for Service when we can reproduce issues in Desktop for RS.  for example,  for Desktop for PBI RS Oct. 2020 version, we use the Sep. 2020 PBI Desktop for Service.  Please see the image for more details. 

Bug ID and Current ICM/CRI are for internal engineers. If there is any new message, I will post here.

[Steffe]

Hi Xue,
Thank you for the reply. Are you sure this is the same bug?
* I´m not using any special characters. Using simple query like "SELECT col1 FROM table1" in my test-report.
* The bug says that this will not impact users running SQL Server. We are quering SQL server.

Regards
Steffe