cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Didac99 Frequent Visitor
Frequent Visitor

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 New Contributor
New Contributor

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

@Didac99 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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 79 members 1,435 guests
Please welcome our newest community members: