Directly Query for SQL Server not working on Power BI Report Server

geoffballard — Sun, 17 Sep 2017 08:15:51 GMT

I having an issue getting direct query mode working for SQL Server on Power BI Report Server (August 2017 Preview) My query is really simple "SELECT data, u from GBT3". The table has 2 rows If I create a report using import mode it works in Power BI Desktop, Power BI Service (via the gateway) and Power BI Report server If I create a report using direct query it works in Power BI Desktop, Power BI Service (via the gateway), but NOT on Power BI Report server On Power BI Report server I get: Something went wrong Unable to load the model Please try again later or contact support. If you contact support, please provide these details. Request ID24a26424-df5e-c146-1a17-c77ded8c3e7e TimeSun Sep 17 2017 08:59:30 GMT+0100 (GMT Daylight Time) Version14.0.600.379 In the log file (RSPowerBI_2017_09_17_08_58_57.log) around the time of the report request I get the: 2017-09-17 08:59:30.6464|ERROR|13|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 connectionUserImpersonator, ITelemetryService telemetryService);message:GetModelMetadata;| RequestID = 24a26424-df5e-c146-1a17-c77ded8c3e7e ClientSessionID = 86200c01-b890-2ad1-af80-98cab6b20a5c 2017-09-17 08:59:30.6464|ERROR|13|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 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: A connection could not be made to the data source with the Name of 'aea54b9b-2eaa-4dd7-b926-3cd498699bc2'. Login failed for user ''.. The exception was raised by the IDbConnection interface. 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 connectionUserImpersonator, ITelemetryService telemetryService) --- End of inner exception stack trace ---;;| RequestID = 24a26424-df5e-c146-1a17-c77ded8c3e7e ClientSessionID = 86200c01-b890-2ad1-af80-98cab6b20a5c 2017-09-17 08:59:30.6464|ERROR|15|Failure in conceptualschema| RequestID = 24a26424-df5e-c146-1a17-c77ded8c3e7e ClientSessionID = 86200c01-b890-2ad1-af80-98cab6b20a5c Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: A connection could not be made to the data source with the Name of 'aea54b9b-2eaa-4dd7-b926-3cd498699bc2'. Login failed for user ''.. The exception was raised by the IDbConnection interface.

Re: Directly Query for SQL Server not working on Power BI Report Server

v-qiuyu-msft — Wed, 20 Sep 2017 05:48:53 GMT

Hi geoffballard,

It's a limitation now, you can see here:

DirectQuery data connections are not currently supported.

Best Regards,

Qiuyun Yu

Re: Directly Query for SQL Server not working on Power BI Report Server

Anonymous — Thu, 30 Jul 2020 19:28:51 GMT

Hello

I have the same issue.

What is the solution please?

topic Re: Directly Query for SQL Server not working on Power BI Report Server in Report Server

Directly Query for SQL Server not working on Power BI Report Server

Re: Directly Query for SQL Server not working on Power BI Report Server

Re: Directly Query for SQL Server not working on Power BI Report Server