Henri
Reputation: 740
Power BI gateway issue with live SSAS Tabular connection
We are not able to connect from Powerbi.com live SSAS tabular connection using gateway. Here are some backgroud details:
- SQL Server 11.0.6020
- Gateway is the latest downloaded today (10/28/206)
- [email protected] is synced to Azure AD from on-premise AD
- [email protected] account is used to login powerbi.com and to connect to data model. Its SSAS admin and also with admin rights on data model. Gateway is also setup with that account.
Gateway looks fine. We got message "Online: You are good to go."
Data source looks fine: "Connection Successful"
But as soon as I go in Powerbi.com: "Get Data" -> "SQL Server SSAS" -> "Connect" and select the data source I get the error:
Something went wrong:
Technical details:
Activity ID: b839be39-e0ec-ed9e-9708-89b1afa14191
Request ID: 45f45676-68cc-7006-9018-8def3d9dbdc1
Date: 2016-10-28 06:09:41Z (UTC)
Error text: Ilmeni seuraava järjestelmävirhe:
Cluster URI: https://wabi-europe-north-b-redirect.analysis.windows.net
Error is in Finnish but it means "We got following system error"
In Gateway logs we can see following errors:
DM.EnterpriseGateway Information: 0 : 2016-10-28T06:09:41.3503946Z DM.EnterpriseGateway 0cdc41c9-8683-45d9-bdbc-ba11b294dfd0 45f45676-68cc-7006-9018-8def3d9dbdc1 MGPP b839be39-e0ec-ed9e-9708-89b1afa14191 E5E3865A [DM.GatewayCore] Deserialized OpenConnectionRequest, executing
DM.EnterpriseGateway Information: 0 : 2016-10-28T06:09:41.3503946Z DM.EnterpriseGateway 1bb454c2-e6a2-4026-a920-c53a59a78981 45f45676-68cc-7006-9018-8def3d9dbdc1 MGCC b839be39-e0ec-ed9e-9708-89b1afa14191 F5100A95 [DM.GatewayCore] Db pool opening raw database connection to [MSOLAP.5: <pi>MSOLAP.5rovider=MSOLAP.5;data source=sqlserver;initial catalog=Database;timeout=180;[email protected];sspropinitappname=PowerBI:[Windows] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway 1bb454c2-e6a2-4026-a920-c53a59a78981 45f45676-68cc-7006-9018-8def3d9dbdc1 MGCC b839be39-e0ec-ed9e-9708-89b1afa14191 BAA79098 [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.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway 1bb454c2-e6a2-4026-a920-c53a59a78981 45f45676-68cc-7006-9018-8def3d9dbdc1 MGCC b839be39-e0ec-ed9e-9708-89b1afa14191 2E87F8B5 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=69, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway fb150b2a-d618-4b7b-9abf-1da42d6c549c 45f45676-68cc-7006-9018-8def3d9dbdc1 MGGC b839be39-e0ec-ed9e-9708-89b1afa14191 96BF2BFF [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=72, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway e19083b1-8457-474d-9e60-dc2fb065197f 45f45676-68cc-7006-9018-8def3d9dbdc1 MGOC b839be39-e0ec-ed9e-9708-89b1afa14191 96BF2BFF [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=72, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway 0cdc41c9-8683-45d9-bdbc-ba11b294dfd0 45f45676-68cc-7006-9018-8def3d9dbdc1 MGPP b839be39-e0ec-ed9e-9708-89b1afa14191 655A6564 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedWithFailureEvent (duration=73, err=AdomdDataAccessErrorResponseException, rootcauseErrorEventId=0)
DM.EnterpriseGateway Error: 0 : 2016-10-28T06:09:41.4128883Z DM.EnterpriseGateway ac8ba607-b76f-494b-8f11-2e69ff096d5d 45f45676-68cc-7006-9018-8def3d9dbdc1 MDSR b839be39-e0ec-ed9e-9708-89b1afa14191 4CD88F16 [DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source.
This error is continued with:
GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError
ErrorCode=-1056899072 --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: AdomdErrorResponseException:<pi>Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Ilmeni seuraava järjestelmävirhe:
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()</pi>
And finally:
GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
InnerType=AdomdErrorResponseException
InnerMessage=<pi>Ilmeni seuraava järjestelmävirhe: </pi>
InnerToString=<pi>Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Ilmeni seuraava järjestelmävirhe:
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()</pi>
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()
([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace
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.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.<OpenConnectionAsync>d__5.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.AdoNetDbConnectionPool.<>c__DisplayClass1.<<CreateObjectInstance>b__0>d__3.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.AdoNetDbConnectionPool.<CreateObjectInstance>d__a.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.Pool.BasePool`3.<CreatePoolObject>d__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.Common.Pool.StatelessPool`2.<Get>d__2.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__DisplayClass5e.<<GetDatabaseConnection>b__5d>d__60.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.<GetDatabaseConnection>d__66.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__DisplayClass2.<<EnsureConnection>b__1>d__4.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.<EnsureConnection>d__8.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()
The cleartext error message is in English only "Following system error occurred". BTW times in logfiles looks like powerbi.com timezone times not the local server time.
Any ideas what we should try next? We have already tried reinstalled gateway few times.
Jens: currently SSAS is running with default NT Service\MSSQLServerOLAPService
Greg: When logged in to server with [email protected] whoami /upn will give me the correct one but when I use runas command to start command prompt with [email protected] it will give me [email protected] addess.
[email protected] is admin for the whole SSAS instance. (as well as admin in that specific datamodel)
Did you mean the standard Connect to Analysis Services dialog? When I put [email protected] to additional connection parameter it will always give system error.
Upvotes: 1
Views: 4353
Answers (1)
Henri
Reputation: 740
It was about effectiveusername= not working. We needed to give SSAS service account an access to read AD.
Br, Henri
Upvotes: 3
Related Questions
- Power BI: On-premise gateway issues while using Excel file and database as data sources
- Power BI Gateway on-prem server
- "EffectiveUserName' XML for Analysis property is not valid" error when refreshing from SSAS gateway
- How to have Power bi live connection with servers in different domain?
- Power BI live connection to PostgreSQL
- Power BI Service Analysis Services database connection string
- Power BI: Published report can´t connect to the gateway
- error connecting Power BI enterprise gateway on an azure machine
- Connecting to Tabular Server in "Analysis Services Connector"
- Power View Fails to Connect