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 email@example.com
Connection (username) to SQL Server as firstname.lastname@example.org
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.
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?
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:
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.
Btw,have you checked the error message in the gateway log?
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.
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: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source.
Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: AdomdErrorResponseException:<pi>Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Systemfehler:
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
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).
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: email@example.com
- Personal DB login: firstname.lastname@example.org
- 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)?
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!