Power BI report Server issue with Direct Query to SQL Server database

[npvinhloc]

Hi everyone I have Power BI Report server, and power bi desktop for RS (May 2024) installed. I tried to create very first report using Direct Query to a SQL Server database. It works normally in Power BI Desktop, but when I open it from Power BI Report Server, it returned error related to Analysis Services

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

Request ID 46c3c998-4e49-568a-9440-538cddde9124

TimeTue Jun 25 2024 09:22:20 GMT-0400 (Eastern Daylight Time)

Service version/powerbi/libs

The detail log here:

2024-06-25 09:22:19.8493|INFO|21|Received request POST /api/explore/reports/3642b2a5-d385-4d38-a5af-415dcb07f16b/conceptualschema| RequestID = 46c3c998-4e49-568a-9440-538cddde9124 ClientSessionID = 3ac90abb-67c9-437b-4029-8875fd901c7e
2024-06-25 09:22:19.9074|INFO|21|Received request GET /libs/images/fluentui-icons.svg| RequestID = s_70ed60ee-bfe0-4b1a-94cc-e2fab7ae4f62
2024-06-25 09:22:19.9526|ERROR|47|PBI.Win.DataExtensionException|ExceptionType:Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException;DataExtensionErrorDetails:Details: ErrorCode=QuerySystemError, ProviderErrorCode=0xC112001B, ErrorSource=PowerBI, Message=Failed to get CSDL., HResult=0x80004005, Language=en-US, ProviderErrorMessage=[<ccon>[content removed]</ccon>], ProviderGenericMessage=[Errors in the high-level relational engine. . The exception was raised by the IDbConnection interface.], 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, TranslationsBehavior translationsBehavior)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_0.<GetModelMetadata>b__0(IDbSchemaCommand command)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.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 = 46c3c998-4e49-568a-9440-538cddde9124 ClientSessionID = 3ac90abb-67c9-437b-4029-8875fd901c7e
2024-06-25 09:22:19.9526|ERROR|80|Failure in conceptualschema catalogObjectId=3642b2a5-d385-4d38-a5af-415dcb07f16b| RequestID = 46c3c998-4e49-568a-9440-538cddde9124 ClientSessionID = 3ac90abb-67c9-437b-4029-8875fd901c7e Microsoft.PowerBI.ReportingServicesHost.CannotRetrieveModelException: An error occurred while loading the model. 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, TranslationsBehavior translationsBehavior)
--- End of inner exception stack trace ---
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, TranslationsBehavior translationsBehavior)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_0.<GetModelMetadata>b__0(IDbSchemaCommand command)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.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 ---
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_1.<GetModelMetadata>b__1(ExploreBaseEvent timedEvent)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.<>c__DisplayClass4_0`1.<RunInActivity>b__0(ExploreBaseEvent activity)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity(String activityName, Action`1 action)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity[T](String activityName, Func`2 action)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.GetModelMetadata(ModelMetadataRequest request)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.GetModelCSDL(String id, String maxModelMetadataVersion, IASConnectionInfo& asConnectionInfo)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.<>c__DisplayClass41_0.<GetOrAddModel>b__0()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at Microsoft.PowerBI.ReportServer.ExploreHost.RSModelCache.GetOrAddModel(ModelKey key, Func`1 modelInfoFunc)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.EnsureSession(String modelId)
at Microsoft.PowerBI.ExploreHost.ExploreClientHandlerBaseFlow.<RunAsync>b__9_0()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ExploreHost.ExploreClientHandlerBaseFlow.<RunAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ExploreHost.ExploreClient.<GetClientConceptualSchemaAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSExploreHost.<GetConceptualSchemaAsync>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<GetConceptualSchemaAsync>d__25.MoveNext()
2024-06-25 09:22:19.9526|INFO|41|Sending response. Response code AMERICAS\Loc_Nguyen 200, Elapsed time 0:00:00.1049723| RequestID = 46c3c998-4e49-568a-9440-538cddde9124 ClientSessionID = 3ac90abb-67c9-437b-4029-8875fd901c7e

I tried to search the whole day, but found nothing. Please help.

[liyongfeng0606]

20240812 更新：
1.如果Sql server 服务器启用了加密链接，在PQ链接数据源配置为加密；
2.如果Sql server服务器没有启用加密链接，那么按照大佬的建议两条路：
 a.修改链接字符串，添加“Encrypt=True;TrustServerCertificate=True” ，但是这个我尝试了很多方法，比如数据库底层直接修改数据源，PowerShell，前端post强制修改，都无法完成；因此这条路走不通；
--我个人猜测，这个字符串必须针对 sql server启用了加密链接的，按照方案1走，可以自动携带此参数；否则手工无法修改；

b. 添加环境变量的方案（终于走通了）：
需要在用户和系统两个级别添加环境变量：PBI_SQL_TRUSTED_SERVERS；
--用户（不是管理员账号，这个用户有特殊意义），应该为这个界面下对应的账号，所以你需要用这个账号登录Power BI Report server 服务器，然后在这个用户账号下配置环境变量(我这里是:*****e\co_bi)关键信息隐藏了，这是一个域账号；如果你用的是管理员，那就用管理员账号，根据真实情况定；

--系统用户环境变量用具有管理权限的账号配置即可；

配置如下（图仅供参考，我这里用的是管理员账号，但是真实环境请用上一步的用户账号登录进行配置用户变量)

-- 环境变量的值：对应的sql server 数据库的hostname；建议不要用IP；需要和你的报告的数据源配置保持一致吧；
不要这里用hostname，报告源配置为ip；

好了，这就是配置的关键点：

配置完后，记得重启电脑（服务器）；

然后再尝试访问报告；恭喜你，应该是通了。

如果有问题：
中国朋友请加微信：93636319，联系我。

[liyongfeng0606]

其实你们一直都没有声明一个前提，你们的SQL SERVER 是否配置了加密链接？
我想大部分出问题的用户，Sql server是没有启用加密链接的;

[Mariamasood]

Hey All,

I'm using PBIRS (on premise). I've used the direct query method; data is refreshing at the backend, but visuals are not refreshing automatically; all I have to do is click the refresh button to refresh visuals manually; is this a PBIRS bug in the Jan 2023 version, or do I need to adjust my query to refresh visuals automatically?

[dim_vas]

Do you have any update on how to solve this one? 
thanks 

[fabienChery · Accepted solution]

Subject already covered in the following post.. Solved: Re: Issues regarding power bi report server - Microsoft Fabric Community

I think it will help you ! 

Fabien 

[npvinhloc]

I just created a new system variable named PBI_SQL_TRUSTED_SERVERS and put the server as its value. I can do direct query to that SQL Server now.

[ZeyadHussein]

I can't find where specifically i can perfom this , thanks in advance...

[npvinhloc]

First find "Edit the system environment variables" from start menu

Then following steps below:

- Click on Environment Variables

- If there is nothing named PBI_SQL_TRUSTED_SERVERS , Click New and then put the name and servers

- If the variable is there, you can select it and hit click Edit...

Hope this help

[azaki]

You are right
and if you have multiable server seprate the names with COMMA (,)

[stanley_1913]

Thanks a lot for sharing how to solve the problem.