Client with IP address 'xx.xx.xxx.xxx' is not allowed to access the server | Gateway Issues?

[Anonymous]

Hi folks,

I created a Power BI Report in desktop, connected to SQL DB, etc. Within PBI Desktop, I created the incremental refresh. Everything was fine until I published to Power BI service. Once on Power BI service, I needed to set up a scheduled refresh once a day.

I installed the personal gateway so I could confirm the credentials. Once I entered my SQL Server crednetials, I recieved this message:

Failed to update data source credentials: Microsoft SQL: Cannot open server 'labs-dev-sql1' requested by the login. Client with IP address 'xx.xx.xxx.xxx' 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.

What am I missing here? Is it okat that I'm installing the gateway on my computer? The IP address shown in the error message is not the same as my internal or external IPv4 address.. not sure exactly what to do from here or what I'm supposed to ask of the DBA.

If anyone can provide some insights, I'd really appreciate it.

Thank you!

[arvindsingh802 · Accepted solution]

You need to add your Power BI Gateway server IP in your Azure SQL firewall rule, to do so please follow IP firewall rules - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

Use the IP address mentioned in the error message as that is the IP address that communicate to internet from your organization (I belive you are working in a secure network)

It might be more than one depending on which VPN you are connected to, if your organization use multiple conenctions, so you can connect with your IT team and get all IP address and get those added in Azure firewall to avoid future errors.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

To prevent this type of error you need to allow the IP address in the Azure server Firewall. Click on the set server firewall it pop upped a new window. On this popup window you need to do two things.

1. Allow azure services and resources to access the server as “Yes”
2. Add your IP address on the rule section as we did like below.

refer:

https://knowledge-junction.com/2021/02/13/azure-sql-resolving-error-cannot-open-server-server-name-requested-by-the-login-client-with-ip-address-is-not-allowed-to-access-the-server/ 

https://medium.com/@JayantTripathy/client-with-ip-address-is-not-allowed-to-access-the-server-azure-error-a71e69a0ff0c 

Wish it is helpful for you!

Best Regards

Lucien

[arvindsingh802 · Accepted solution]

You need to add your Power BI Gateway server IP in your Azure SQL firewall rule, to do so please follow IP firewall rules - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

Use the IP address mentioned in the error message as that is the IP address that communicate to internet from your organization (I belive you are working in a secure network)

It might be more than one depending on which VPN you are connected to, if your organization use multiple conenctions, so you can connect with your IT team and get all IP address and get those added in Azure firewall to avoid future errors.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!