cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcichansky18
Frequent Visitor

Gateway connection to Analysis Services

Hi,

 

I have a tabular cube, which is connected to SQL Server with admin role both in cube & SQL Server Analysis services engine. I also have technical user, with the same permissions as I have.

Then I connected Power BI Desktop to my tabular cube & deployed to PBI Service.

 

Technical SQL server account: domain\technicaluser

Connection (username) to PBI Service as user@pbiservice.com

Connection (username) to SQL Server as user@sql.com

 

Within PBI Service we have installed Power BI Gateway (standard mode), running in a cluster. Gateway is up & running.

 

Now I want to view my report in PBI Service. 

  • GW connection is configured to use technical account (domain\technicaluser or corresponding email address) -> connection test OK, connection working
  • User tab of the connection = me (user@pbiservice.com)
  • Map user names

 

Even though, when I open the report, I get following error message: 'This report couldn't access the data source. ...'

 

Gateway itself is working. I tested it with connection to SQL Server - import mode -> scheduled refresh worked just fine. Also for analysis services connection works scheduled refresh (used only for testing purposes).

 

Don't you know what am I missing?

 

Thanks

7 REPLIES 7
collinq
Super User
Super User

Hi @jcichansky18 ,

 

Within Desktop are using the same userid as you are using when you are in the service?  Also, are you using the same permission TYPE and PRIVACY LEVEL in your credentials?  This is in Desktop for the data source:

 

collinq_1-1621355396411.png

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Hello,

 

Thanks for reply. Yeah, I have same privacy level. Regarding user name:

PBI Desktop + Analysis Services engine + GW connection: user@sql.com or corresponding domain\username

PBI Service: user@pbiservice.com

 

Therefore I'm remapping in GW connection settings.

 

Thanks

Hi, @jcichansky18

If you have checked that there is no problem with the credentials on the service, the problem is most likely in the username mapping.

Please refer to these documents,try sql\user as the username.

https://community.powerbi.com/t5/Service/Data-source-access-error-Tabular-Live-connection-to-local-h... 

Btw,have you checked the error message in the gateway log?

 

Best Regards,
Community Support Team _ Eason

 

 

 

HI @jcichansky18 ,

 

When you open the dataset on the Service, does it give more of an error message?  I am hoping for something like "credentials on source not correct" or something like that.  I have seen that now and then where the gateway is fine but for some reason the report itself, on the credentials needs to be updated.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Hi,

When I open dataset settings, everythinig is fine (as I wrote before, also cached refresh is working).

 

Here are the logs from GW:

Info log file:

[DM.GatewayCore] DbFullConnectionString: singleSignOnInformation != null: False
[DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.8: <pi>MSOLAP.8:provider=MSOLAP.5;data source=SQLServer\DBName;initial catalog=CubeName;cube=Model;sspropinitappname=PowerBI;effectiveusername=domain\UserName;connect timeout=60:[Windows] Encrypted Credential information omitted</pi>]
[DM.Pipeline.Common] Either LookupPropertyName domain\UserName or TargetPropertyName is null or empty. Fall back to original value.
[DataMovement.PipeLine.GatewayDataAccess] Replace effective user name in adomd connection string from domain\UserName to domain\UserName.

 

Error log file:

[DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source.; ErrorShortName: AdomdDataAccessErrorResponseException[ErrorCode=-1056899072,HResult=-2146233088]/Wrapped(AdomdErrorResponseException)[ErrorCode=-1056899072,HResult=-2146233088]
[DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=68, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
[DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source.

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError
GatewayVersion=
ErrorCode=-1056899072 --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: AdomdErrorResponseException:<pi>Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Systemfehler:
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()</pi>

Hey @jcichansky18 ,

 

The error messages are indicating a problem with the sign on (Gateway_DataSourceAccessError) .  Since the userid's are not the thing, perhaps the gateway is in and of itself (even though it might appear to be working). 

If your gateway is current and installed to the latest version and it is still not working,  I suggest that you follow the instructions from this older thread (which I know is not exactly your issue but the solution looks promising to me).

Solved: Enterprise Gateway stops and start multiple times - Microsoft Power BI Community

 

Here is 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!
Private message me for consulting or training needs.




Hi,

Thanks for your support! I finally found the root cause, but wanted to ask additional question.

As I mentioned before:

- GW working

- PBI service login: user@pbiservice.com

- Personal DB login: user@sql.com

- Technical SQL server account: domain\technicaluser

- Analysis service engine on SQL Server: technical user as admin ; me in admin group

- Cube role: me as admin

As my PBI service login is different compared to my SQL login, I'm doing user mapping (now only manual remapping, later through AD). Based on GW logs, that works.

My personal user is belonging to AD group, which has appropiate permissions (cube connection via SSMS or PBI Desktop works fine).

 

Even though, report in PBI Service (live connection to AS) doesn't work. After testing many deifferent options, I manually remapped my PBI Service account to different user, which has only read permission on the cube & no admin permissions on AS engine. That WORKS!

The issue now is, that another different user - same as me - doesn't work too.

 

Is there some special element or setting within AD, which I need to have to be able to run reports in PBI Service (SQL AS - tabular model - live connection)?

Thanks

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors