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

[Anonymous]

Hi

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

[Anonymous]

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?

[Mogry]

---

@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

[Anonymous]

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

[v-qiuyu-msft]

Hi @Anonymous,

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

Best Regards,
Qiuyun Yu

[su2pbi]

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 

1. Confirmed via SSMS user has correct access (role security was defined) in SSAS cube 
2. Confirmed Kerberos is setup correctly by testing the same user same cube with Paginated Report and Excel Online
3. 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?

[su2pbi]

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 

1. Confirmed via SSMS user has correct access (role security was defined) in SSAS cube 
2. Confirmed Kerberos is setup correctly by testing the same user same cube with Paginated Report and Excel Online
3. 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?

[Arez2017]

Hello 

I am facing the same issue.

Did you find a workrround please?

Regads

[Anonymous]

Hi

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

[Jon-Heide · Accepted solution]

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. 

[sblakeb]

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