Gateway connection to Analysis Services

[jcichansky18]

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
  • Set up as Effective user names
  • Replace: user@pbiservice.com
  • With: user@sql.com

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

[collinq]

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:

[jcichansky18]

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

[v-easonf-msft]

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

[collinq]

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.

[jcichansky18]

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>

[collinq]

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 

[jcichansky18]

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