Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

On-Premise Gateway doesn't work when appending tables from different sources

Hello guys,

 

I'm trying to pull data from Azure SQL Server and from MySQL (Amazon RDS) via On-Premise gateway.

Everything works fine if I do it separately but the moment I try to append those tables the gateway gives me that error:

Spoiler
"Cannot connect to the mashup data source."

  

Both tables have the same columns and same data types.

I have tried to play with privacy levels too as said in some other topics but didn't work.
We have the latest gateway version installed.


The Operation causing problems:

Spoiler
= Table.Combine({#"MySQL - Data", #"SQLServer - Data"})

 

Looking at the gatewayInfo logs we found that:

Spoiler
"[DM.Pipeline.Common] Failed to read GatewayCore property: EnableFastCombine, Ex: Object reference not set to an instance of an object."

 

On the other side, at the gatewayError logs:

Spoiler
GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=MashupValueException
InnerMessage=<pi>Microsoft SQL: Cannot open server 'server' requested by the login. Client with IP address 'ip address' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.</pi>
InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): Microsoft SQL: Cannot open server 'server' requested by the login. Client with IP address 'ip address' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

 

If I Append only the tables from Azure it works fine too.

I am missing something? Is there anything we can do to solve the problem from the PowerBI before start playing with IP permissions and stuff like that?

 

I think I can solve it with UNION in DAX but those tables are both pretty large and I don't think it is my best option.

 

Thank you!

1 REPLY 1
edhans
Super User
Super User

@Anonymous A few things:

  1. Ensure that the gateway is configured to allow cloud refreshes. It will be in the gateway settings near the bottom. Click on the Gear in the Power BI portal, Manage Gateways, then "Allow user's cloud data sources...." I'm not exactly sure why you are using a gateway here as both of those are cloud resources, but I've not connected to Azure SQL yet, so that might require a gateway. 
  2. Make sure your queries are all pointing DIRECTLY to the source. Don't create a single source to Azure SQL for a list of tables, then create references to that to get to mulitple tables. That works unless you are mashing updata to different sources, then it breaks in the portal. It works fine in the desktop, but not online.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors