Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NickGMead
New Member

Deploying a Report to Local PBIRS with a DirectQuery to Another Local SQL Server - Help :)

Hello!

 

Apologies if this is a silly fundamentals question around something I've overlooked,

 

I've created a report in a PBI Desktop which relies on a direct query to a local sql instance (On a seperate server to the PBIRS) to provide up-to-date register marks for a college. I'm happy with the functionality of the report in Desktop so i'm ready to deploy it to the report server for  general usage.

 

The data source in PBI Desktop is using stored SQL Server credentials, But when I deploy the report to the report server and open it i can see the report design but receive the following error: -

 

 

An unexpected error occurred
An unexpected error occurred. Please try again later.
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 ID: 6ee9a03a-2e74-23de-c83a-8751030935a4
Time: Wed Nov 09 2022 15:56:15 GMT+0000 (Greenwich Mean Time)
Service version: /powerbi/libs

 

Can confirm the data source settings in PowerBI Desktop lists the full server/database path for the desired database and all data is queried quickly and correctly there.

 

Have i missed something obvious? Do I need to create a data source on the PBIRS somehow to allow this connection?

 

Many thanks for any assistance, Getting to grips with PBIRS over the next couple months so will pay as much help forward as i can 🙂

 

1 ACCEPTED SOLUTION
NickGMead
New Member

Thanks for the response Henry,

 

I was just coming back myself to mention I had managed to fix this, As a new report server user I expected setting the permissions on the data source in PBI Desktop would carry over to the RS, And whilst the connection string did, the permissions recorded the very first credentials i had used in Desktop and don't update in subsequent saves of the report.

 

Fixed by going to the report server, Selecting the elipse menu on the individual report and going to "Manage". In there i could specify the user credentials for the sql user which in turn allowed the report to query the local sql server and work 👍

 

Cheers!

View solution in original post

2 REPLIES 2
NickGMead
New Member

Thanks for the response Henry,

 

I was just coming back myself to mention I had managed to fix this, As a new report server user I expected setting the permissions on the data source in PBI Desktop would carry over to the RS, And whilst the connection string did, the permissions recorded the very first credentials i had used in Desktop and don't update in subsequent saves of the report.

 

Fixed by going to the report server, Selecting the elipse menu on the individual report and going to "Manage". In there i could specify the user credentials for the sql user which in turn allowed the report to query the local sql server and work 👍

 

Cheers!

v-henryk-mstf
Community Support
Community Support

Hi @NickGMead ,

 

According to your description, the sql data source connected through dq mode in powerbi report server has restrictions in terms of permission validation.

vhenrykmstf_0-1668071387629.png


You can check the following related documentation links:

Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.