search

CreativeDataEU / PowerBITrinoConnector

A Micosoft Power BI Custom Connector allowing you to import Trino data into Power BI.
MIT License
38 stars 15 forks source link

Unable to Refresh from Gateway server #13

Closed jlriverayuso closed 4 months ago

jlriverayuso commented 5 months ago

although the data refreshes fine form othe Personal Mode Data Gateway, when published to the Power BI Service i just get the following error: 

{"error":{"code":"DM_GWPipeline_Gateway_InvalidMashupConnectionString","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidMashupConnectionString","parameters":{},"details":[],"exceptionCulprit":1}}} Table: -- Cluster URI: | usgovcloudapi.net
pichlerpa commented 5 months ago

Hey @jlriverayuso,

have you made sure to enable "Allow user’s custom data connectors to refresh through this gateway cluster" when setting up the gateway in the Power BI Service and did the connection test work when configuring the data source?

PS: Read more here https://learn.microsoft.com/en-nz/power-bi/connect-data/service-gateway-custom-connectors

Regards, Patrick

jlriverayuso commented 5 months ago

Hi! @pichlerpa , yes, this option has been enabled. that error comes up when it tries to refresh the data.

image

pichlerpa commented 5 months ago

Hi @jlriverayuso,

That's difficult to troubleshoot, but you could download some sort of HTTP sniffer such as Fiddler for checking what HTTP requests are being sent and what's going on behind the scenes. It worked on my end before uploading the .MEZ file using a non-personal gateway.

Regards, Patrick

jlriverayuso commented 5 months ago

What do you think?

DM.EnterpriseGateway Verbose: 0 :   
13B85533 [DM.GatewayCore] Attempting to create and add connection with ID: bea89397-6f89-4ad4-adec-0da49db2b94d
DM.EnterpriseGateway Information: 0 :   
2D2816FE [DM.GatewayCore] DbFullConnectionSecure complicated STRING: singleSignOnInformation != null: False
DM.EnterpriseGateway Information: 0 :   
3202516C [DataMovement.PipeLine.GatewayDataAccess] MashupOleDbConnectionProvider: SingleSignOnInformation is null
DM.EnterpriseGateway Information: 0 :   
D2B7FF2B [DataMovement.PipeLine.GatewayDataAccess] MashupOleDbConnectionProvider: Upn is 
DM.EnterpriseGateway Information: 0 :   
1E1FB278 [DataMovement.PipeLine.GatewayDataAccess] MashupOleDbConnectionProvider: originalConnectionSecure complicated STRING is <ccon>provider=Microsoft.Mashup.OleDb.1;location="DB schema";extended properties="LONG Secure complicated STRING";privacypartitiondatasources={};configurationproperties='{"HostContext":"PowerBIPremium-Import"}'</ccon>
DM.EnterpriseGateway Information: 0 :   
FFC77F3E [DataMovement.PipeLine.GatewayDataAccess] Found firewall setting in the mashup connection Secure complicated STRING: PrivacyPartitionDataSources=<ccon>{}</ccon>
DM.EnterpriseGateway Warning: 0 :   
4D566DB1 [DataMovement.PipeLine.GatewayDataAccess] Could not extract data source references by TryExtractDatasourceReferencesFromMashup because:<ccon>Query contains unsupported function. Function name: Trino.Contents;</ccon>
DM.EnterpriseGateway Warning: 0 :   
A1FB7297 [DataMovement.PipeLine.GatewayDataAccess] Could not extract data source references from M connection Secure complicated STRING: <ccon>provider=Microsoft.Mashup.OleDb.1;location="DB schema";extended properties="LONG Secure complicated STRING";privacypartitiondatasources={};configurationproperties='{"HostContext":"PowerBIPremium-Import"}'</ccon>

additionally with the help of the GatewayDataAccessMashupUtils:

StackTrace:    at Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.InvalidMConnectionStringException.TraceConstructor()
   at Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.InvalidMConnectionStringException..ctor(String message, PowerBIErrorDetail[] errorDetails)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.RetrieveDataSourceReferencesFromMConnectionString(String connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider..ctor(String originalConnectionString, DataSourceGatewayDetails[] dataSources, IGatewayConfigurationStore configurationStore, Int32 defaultConnectTimeout, IImpersonateHelperFactory impersonateHelperFactory, SingleSignOnInformation singleSignOnInformation)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProviderFactory.CreateWithEncryptedCredential(String originalConnectionString, DataSourceGatewayDetails[] dataSources, IGatewayConfigurationStore configurationStore, Int32 connectTimeout, SingleSignOnInformation singleSignOnInformation)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.CreateDbConnectionProvider()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.<OpenConnectionAsync>d__17.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.ConnectionStrings.AdoNetDbFullConnectionString.OpenConnectionAsync()
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.AdoNetDbConnectionUtils.<CreateConnectionAsync>d__4.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.AdoNetDbConnectionUtils.CreateConnectionAsync(AdoNetDbFullConnectionString connectionString)
   at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass1_0.<<EnsureConnectionAsync>b__0>d.MoveNext()
   at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
pichlerpa commented 5 months ago

Hey @jlriverayuso,

It seems to like the Power BI service finally can't access the custom connector, maybe somehow related to the first point here: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors#considerations-and-limitations

Patrick

jlriverayuso commented 4 months ago

@pichlerpa seems that you were right. we had to change the location of the Custom Connectors folder and allow authenticated users to execute anything in the folder. thank you so much!