Enterprise Gateway can't connect to on-premise Analysis Services

[bovin_a]

Hi,

I try to use the Enterprise Gateway to connect to my on-premise Analysis Services database.

I have installed the gateway on the same server as where my Analysis Service database is hosted. I can see my gateway and it's online.

Then i try to add datasource and get the error:

This is datasource properties:

User "WIN10-HOME\bovin" has administrator privileges in SSAS and database:

While i trying to add datasource in SQL Profiler appear following records:

And i suggest that this records from Enterprise Gateway logs may have relation to the issue:

DM.EnterpriseGateway Information: 0 : 2016-02-14T22:06:56.2741603Z DM.EnterpriseGateway	f325b270-a65b-48f4-8e29-6863d67e4e01	40c9d759-5bad-c105-e727-7293347ab846	MGPP	ac4cb381-69a0-4b27-937e-b733c0428780	7683252B [DM.GatewayCore] Deserialized EncryptCredentialsWithTestConnectionRequest, executing
DM.EnterpriseGateway Error: 0 : 2016-02-14T22:06:56.3397028Z DM.EnterpriseGateway	d9f241f1-4a18-425f-b278-2f4214956fa5	40c9d759-5bad-c105-e727-7293347ab846	MGTD	ac4cb381-69a0-4b27-937e-b733c0428780	CA25B982 [DM.Pipeline.Diagnostics] Exception object created: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source.(wrapped: GatewayPipelineWrapperException/<pi>Произошла следующая системная ошибка: </pi>)
DM.EnterpriseGateway Error: 0 : 2016-02-14T22:06:56.3407032Z DM.EnterpriseGateway	d9f241f1-4a18-425f-b278-2f4214956fa5	40c9d759-5bad-c105-e727-7293347ab846	MGTD	ac4cb381-69a0-4b27-937e-b733c0428780	BA77378D [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=61, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-02-14T22:06:56.3407032Z DM.EnterpriseGateway	d55e030d-8eca-4357-b491-ea674ce5b3ab	40c9d759-5bad-c105-e727-7293347ab846	MGEC	ac4cb381-69a0-4b27-937e-b733c0428780	1434F9F9 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=65, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-02-14T22:06:56.3407032Z DM.EnterpriseGateway	f325b270-a65b-48f4-8e29-6863d67e4e01	40c9d759-5bad-c105-e727-7293347ab846	MGPP	ac4cb381-69a0-4b27-937e-b733c0428780	A5E797CA [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=66, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-02-14T22:06:56.3407032Z DM.EnterpriseGateway	e76deb2f-f453-4d60-9f68-212db867e6c6	40c9d759-5bad-c105-e727-7293347ab846	MDSR	ac4cb381-69a0-4b27-937e-b733c0428780	88B4C709 [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
ErrorCode=-1056899072 --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: AdomdErrorResponseException:Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Произошла следующая системная ошибка: 
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
InnerType=AdomdErrorResponseException
InnerMessage=<pi>Произошла следующая системная ошибка: </pi>
InnerToString=Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Произошла следующая системная ошибка: 
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()
InnerCallStack=   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()
   --- End of inner exception stack trace ---
  ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException.StackTrace:)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.OpenConnectionAsync()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.AdomdConnectionProvider.<TestConnectionAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.<TestConnectionAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.DbFullConnectionString.<TestConnectionAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass28.<<TestDataSourceConnection>b__27>d__2a.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__a`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<TestDataSourceConnection>d__2e.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass34.<<EncryptCredentialsWithTestDataSourceConnection>b__33>d__36.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__a`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<EncryptCredentialsWithTestDataSourceConnection>d__42.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<DispatchImpl>d__e.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<Dispatch>d__b.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<DeserializeImpl>d__e.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<>c__DisplayClass4.<<Deserialize>b__2>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__a`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<Deserialize>d__9.MoveNext()

Can anyone help me?

Thanks in advance,

Aleksandr Bovin

[shinjie]

https://community.powerbi.com/t5/Service/Gateway-reports-having-a-network-connectivity-issue-with-la...

[AnniePower]

I'm having a similar issue.  SSAS is using the default account.  PBIX files connected to SSAS will refresh (after mapping user names).  XLSX files (yes, with a model), connected to SSAS sources will not refresh through the Enterprise gateway.  But they will refresh through a personal gateway.  Why would the personal gateway work better than an enterprise gateway??

[vitorpn]

Did you tried change SSAS service logon to Local System ?

[ppphang]

hi

I have same mistake, i using SQL 2016 standard version. no problem to connect using power bi desktop live connection to ssas, but it not able to read the report on power bi service.

error: cannot load the data for this visual

DM_GWpipeline_Gateway_DataSourceAccessError

Updated latest version of on-premises gateway and also power BI desktop

[pquezada]

hi, 

i have same error!!

somebody repair this please?

my original post: https://community.powerbi.com/t5/Service/On-Premise-Data-Gateway-Data-Source-cannot-connect-with-Ana...

[vitorpn]

Did you tried change SSAS service logon to Local System ?

[jpdp101]

Hi,

I'm experiencing the same thing. Anyone able to resolve this ? 

Thanks in advance.

[menachov]

Hi

I'm experiencing this problem, 

Please help with this issue

[lopezd]

Hi,

I have the same mistake, can you please help us?

Thanks

[bovin_a]

Hi, lopezd

It seems that no one has solved this problem yet. As i wrote before, we started to use free solution called Datazen. And we stopped our attempts to solve this issue since then.

[dart]

Hi,

I got the same issue.

I got it working when I configured SQL Server Analysis Services service to use defult account

NT Service\MSSQLServerOLAPService

[bovin_a]

However I can add SQL datasource with same account and on the same server:

 

Records from SQL Profiler:

Where 9192 is process id of the Power BI Enterprise Gateway Service.

[HarrisMalik]

@bovin_a can you restart your server and see if it works or not?

[bovin_a]

Hi, @HarrisMalik.

Yes, I tried to restart all SQL services and machine which hosts SQL server. But without success for SSAS 😞

[HarrisMalik]

@bovin_a What is the version and edition of your SQL Server? Are all the services i.e. database, SSAS, SSRS on same version?

[bovin_a]

@HarrisMalik All the services on same version of SQL server:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun 9 2015 12:06:16
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

It's my test enviroment. But I also tried on production server:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
Jun 9 2015 12:06:16
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

[TealCanady]

Aleksandr,

This may be related to the service degredation that is occuring since yesterday (see last update below). Can you open power bi desktop and connect to the cube using that login?

Teal


 
Current Status: Engineers are redeploying certificates and restarting the affected publishing components in an effort to restore service.

User Impact: Affected users may be unable to publish workbooks.

Customer Impact: Analysis of incident scope indicates that many customers will likely have users experiencing impact related to this event. No customer reports of the issue have been identified at this time.

Incident Start Time: Sunday, February 14, 2016, at 10:27 PM UTC

Next Update by: Monday, February 15, 2016, at 5:00 PM UTC

[bovin_a]

@TealCanady,

Yes, I can connect to the SSAS database and cubes from Power BI Desktop using my login "WIN10-HOME\bovin".

So, I'll be trying to create datasource during next several days. Thanks you very much.

Can you point me where I can see status of Power BI service?

[Anonymous]

Hello Bovin_a,

I'm facing the exact same issue. Any luck since then?

Thank you,

Alex

[bovin_a]

Hello Alex,

I tried to set up a connection to the SSAS few days since then, but without success. After that we decided to abandon the use of Power BI.

We tried Datazen Server. It's free solution that you need to deploy on your own on-premise. It has native apps for all platforms. Can be integrated with Active Direcotory. So we are using Datazen Server now.