topic Re: PowerBI Server (October) DirectQuery to SQL Server table - bug in Report Server

PowerBI Server (October) DirectQuery to SQL Server table - bug

Anonymous — Fri, 03 Nov 2017 01:02:17 GMT

I have created a PowerBI Report on PowerBI Desktop (October) with a DirectQuery connection to a table on a SQL Server. The report works in PowerBI Desktop (October). When I save it to PowerBI Server (October) and open it I get this error:

"Something went wrong"

"We couldn't connect to the Analasys Services server.................."

I did not connect to Analasys Services, I connect to SQL Server.

here is the full error:

Power BI

Something went wrong

We couldn't run the report because we couldn't connect to its data source. The report or data source might not be configured correctly.
Please try again later or contact support. If you contact support, please provide these details.

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.

Request IDf6276424-265e-f0d5-5053-f86d0c599a14

TimeFri Nov 03 2017 13:44:07 GMT+1300 (New Zealand Daylight Time)

Version14.0.600.434

From the log file on the server:

2017-11-03 13:44:07.8153|ERROR|27|PBI.Win.ProcessingError|ExceptionType:Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException;stackTrace: at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Discover(String requestType, String requestNamespace, IDictionary restrictions, InlineErrorHandlingType inlineErrorHandling, IDictionary requestProperties)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, IDictionary adomdRestrictions, Boolean throwOnInlineErrors, IDictionary requestProperties)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, AdomdRestrictionCollection restrictions, Boolean throwOnInlineErrors, AdomdPropertyCollection requestProperties)
at Microsoft.ReportingServices.DataExtensions.DefaultAdoMdConnectionFactory.GetSchemaDataSet(IDbConnection connection, String schemaName, IList`1 restrictionList)
at Microsoft.ReportingServices.DataExtensions.AdoMdDaxConnection.InternalGetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
at Microsoft.ReportingServices.DataExtensions.AdomdExceptionHandler.Execute[T](String connectionString, Func`1 func)
at Microsoft.ReportingServices.DataExtensions.AdoMdDaxConnection.GetModelMetadata(String perspectiveName, String supportedVersion)
at Microsoft.PowerBI.ReportingServicesHost.LocalReportProcessingHost.GetModelMetadata(DataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, String modelMetadataVersion, IConnectionUserImpersonator connectionUserImpersonato

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Jon-Heide — Fri, 03 Nov 2017 20:57:58 GMT

Try storing your credentials (windows or sql) in the server after you publish. You can do this under the data sources tab. The report server now hosts Analysis Services under the covers and that AS server could not connect to your DQ data source.

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

v-qiuyu-msft — Sat, 04 Nov 2017 08:54:31 GMT

Hi @Anonymous,

Please go to Manage pane of the report, click Test Connection to verify the used credential is valid.

Best Regards,
Qiuyun Yu

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

su2pbi — Sat, 27 Jan 2018 03:27:13 GMT

Hi Qiuyun Yu,

I am received the similar error.

We couldn't run the report because we couldn't connect to its data source. The report or data source might not be configured correctly.
Please try again later or contact support. If you contact support, please provide these details.

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.

Request ID1d123dfe-bd75-68f9-cb8c-f20e2714b9fc

TimeFri Jan 26 2018 22:02:00 GMT-0500 (Eastern Standard Time)

Version14.0.600.434

I had

Confirmed via SSMS user has correct access (role security was defined) in SSAS cube
Confirmed Kerberos is setup correctly by testing the same user same cube with Paginated Report and Excel Online
Confirmed via Manage pane of the report, clicked Test Connection with "as the user viewing the report" and received a green check mark.

Is this really a bug on Oct release? Or there is something else I need to look into more?

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

su2pbi — Sat, 27 Jan 2018 03:28:31 GMT

Hi Qiuyun Yu,

I am received the similar error.

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.

Request ID1d123dfe-bd75-68f9-cb8c-f20e2714b9fc

TimeFri Jan 26 2018 22:02:00 GMT-0500 (Eastern Standard Time)

Version14.0.600.434

I had

Confirmed via SSMS user has correct access (role security was defined) in SSAS cube
Confirmed Kerberos is setup correctly by testing the same user same cube with Paginated Report and Excel Online
Confirmed via Manage pane of the report, clicked Test Connection with "as the user viewing the report" and received a green check mark.

Is this really a bug on Oct release? Or there is something else I need to look into more?

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Arez2017 — Thu, 22 Feb 2018 13:45:06 GMT

Hello

I am facing the same issue.

Did you find a workrround please?

Regads

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Anonymous — Thu, 22 Feb 2018 20:15:53 GMT

The workaround is to go to "Data Sources" of the report after you published it to server.And then manually enter the username and password.

A permanant solution was to configure Kerberos for the server.

Jaco

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

sblakeb — Tue, 17 Apr 2018 19:31:36 GMT

My group releases reports for other people we don't want to use our own credentials when the report runs. (or a service account for that matter) We want the person who runs the report to have their AD credentials passed through to the SQL server so we know which user is taking the SQL resources. Also its a second layer of security. One to the report itself, and second to the database. Anyone able to get pass though credentials working for direct query data sources?

~Blake

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Anonymous — Fri, 16 Nov 2018 22:02:02 GMT

I was struggling with this error till I found that Report Server DOES NOT SAVE credential in order to have an access to SQL Server after Create the Graphic/Report in Report Server Select it and Select the Option Manage as depiced in Follow

Go to Data Source Tab and make your coonection string and Credential info was input correctly

Click on Test connection if it works that means you are good to go

I hope it helps

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Anonymous — Tue, 19 Mar 2019 17:12:22 GMT

Hello,

we are still facing this issue with Power BI Desktop January 2019 version.

1. We created a new report using DirectQuery

2. Get Data -> SQL Server Database -> Enter Server IP (or Name, we tried both) -> Select View -> DirectQuery

3. Database Username + Passwort

4. We can see data

5. Publish the report to the (OnPremise) Power-BI Report Server

6. Set Username + Password again inside the "Manage" Tab.

7. Check connection: Connection is successful

8. Open report in the browser -> The error msg is shown instead of data 😉

Power BI - Report Server: 1.4.6969.7395 (Januar 2019)

Power BI - Desktop: 2.65.5313.1562 32-bit (Januar 2019)

Any ideas?

Re: PowerBI Server (October) DirectQuery to SQL Server table - bug

Anonymous — Fri, 25 Oct 2019 06:14:16 GMT

@Anonymous wrote:
Hello,

we are still facing this issue with Power BI Desktop January 2019 version.

1. We created a new report using DirectQuery
2. Get Data -> SQL Server Database -> Enter Server IP (or Name, we tried both) -> Select View -> DirectQuery
3. Database Username + Passwort
4. We can see data
5. Publish the report to the (OnPremise) Power-BI Report Server
6. Set Username + Password again inside the "Manage" Tab.
7. Check connection: Connection is successful
8. Open report in the browser -> The error msg is shown instead of data 😉

Power BI - Report Server: 1.4.6969.7395 (Januar 2019)
Power BI - Desktop: 2.65.5313.1562 32-bit (Januar 2019)

Any ideas?

Hi there,

i am running into the same issue with the September 2019 setup. Does sombody knowing a solution/workaround?

Besides SQL we are trying to connect to Oracle as well. I am wondering if this has something to do with Kerberos delegation, becaue source servers and report server are not on the same machine?

Cheers,

Mogry