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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
samhunter
Helper I
Helper I

Connection to Azure DB requesting Gateway for Power BI Service

 

Apologies if there is a thred answering this already. I could not find one.

 

We have several reports that connect to an Azure SQL DB without a data gateway (from what I have read, this is perfectly normal). We also have reports that use flat file data imported from our network via a Data Gateway. And other reports that use a combination of both. All of which are refreshed on a schedule.

 

All apart from one. This report is asking for a data gateway connection to the Azure DB (Note: The 'Data source credentials' are setup with no connection error).

 

When I refresh it, I get this connection error.

PBI refresh error1.PNG

The 'Gateway connection' appears to be asking me to connect to the Azure DB via a data gateway (below image) detailing the same server details as in the 'Data source credentials'

PBI refresh error2.PNG

The only unique thing I can see regarding this is that during the data import, I am merging data from the flat file into a table created from an Azure DB table. I can't see how that would effect the connection but thought it worth mentioning. 

 

Does anyone know what I am missing?

 
 
7 REPLIES 7
V-lianl-msft
Community Support
Community Support

Hi @samhunter ,

 

Based on your description, are you using mashup data sources? If so, you need to install and configure a gateway for this mashup data source.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Nimai123
Post Patron
Post Patron

Hey @samhunter 

 

Have your issue been resolved, if not you can create a support ticket for free if you are a pro licence user, they will help you to figure out the problem.

 

Regards,

 

IF YOU LIKE MY ANSWER PLEASE GIVE KUDOS AND IF MY ABOVE POST HELPS YOU TO SOLVE YOUR PROBLEM ACCEPT IT AS A SOLUTION. 👍

Nimai123
Post Patron
Post Patron

Hello @samhunter  

 

Use of set server firewall for the Azure SQL DB is for security purpose we need to do that as our IP Address changes as mentioned in the error.

 

open the azure portal click on the Database click on the set firewall and click on the Add client IP and save it.

 

Capture.PNG

 

IF YOU LIKE MY ANSWER PLEASE ACCEPT IT AS A SOLUTION AND GIVE KUDOS

 

Regards,

Nimai

Hi @Nimai123 

 

I tried this but still get the same error. I still don't fully understand the need for this. My client already has access to the DB. I can import data from DB and flat file without issue. The challenge is getting the PBI service to refresh from the cloud DB and a flat file from a local network via data gateway.

RenuKshirsagar
Advocate II
Advocate II

Hello @samhunter ,  

 

The first error image implies that your Client IP address doesn't have access to the master database and thus you need to have a firewall rule set up in the Azure Management Portal for your Client IP address. 

 

You may ignore the Gateway connection section as it only lists down the server details for Azure DB. Its credentials will be mapped in the Data sources settings section. So you can try to set up the firewall rule and confirm the refresh status.  

 

If this was helpful, then kindly hit LIKE and promote this information as a solution to help other end users reach the correct answer.

 

Thanks,  

Renu KS

 

 

Hi Renu

 

Thanks for your response

 

What is the firewall rule for? I have many reports getting data from the same DB without the need for this.

 

Thanks

Sam

Hello @samhunter ,

 

There are server-level firewall that blocks clients from connecting to the Azure SQL DB. Therefore we need to set up a firewall rule in Azure to enable your Client IP communicate with the server. You need to perform the following:

 

1) Open Azure Portal

2) Go to your Azure SQL DB section

3) On the left pane you should find an option for Firewalls and Virtual Network

 

Enter your information and try to refresh the dataset once again.  

 

--------------

Can you provide more details on the reports for which you were able to schedule the refreshes successfully? What is the difference between this one and the others? Is it that you are merging a file's data with this DB?

 

 

Azure SQLDB.PNG

 

 

If this reply was informative and helpful, I would appreciate a LIKE on the reply or promote this as a solution if it resolved your error.

 

Thanks,  

Renu KS

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors