SQL Server - Power BI Gateway issue using external IP or hostname to access database

Views: 695
Reading Time: 6 minutes

Hey guys!
In this post, I would like to share with you a situation that I have witnessed in some 4 clients in the last 3 months and always have to explain the same thing when users access the bank using an external IP or hostname, created so that users can connect to these data sources are outside the corporate network and without the need for a VPN, but the server only recognizes internal addresses (IP / Hostname) and it cannot configure Power BI Gateway correctly because it gives error when it tries to add the source of data.

So I decided to create this article with two extremely simple solutions so that it can be useful to people in the community, not just my BI clients.

Simulating and explaining the problem

Click to view content
To understand this scenario, imagine the following:

  • Within the same server network or using a VPN, access to the instance is done using an internal hostname (dirceu-vm) or IP (192.168.0.27, for example)
  • Outside the network, there are some fixed IP virtual machines that have direct access to the corporate network and access the server through a public IP, port forwarding and release of these IP's in the firewall. That is, off-network access to this server is done using the public IP (Ex: 189.123.111.222) or public host (external access.dirceuresende.com)
  • Some people who develop reports on Power BI are within the network and others access via public IP.
  • Power BI Gateway is installed and configured on the database server, with the data source already created.

The external user can access the bank and create the report normally within Power BI Desktop:

When a user from within the network publishes the report, it works normally. But when a user doing external access publishes a report, this error message happens:

There is no gateway to access the DW data source.

And then when you click on “show details” you will see this error message:

Let's try adding the data source to Gateway then:

I configure the connection data normally:

But when I try to add the data source, we come across a very common message, which is shown when the requested instance cannot be found:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server / Instance Specified)

And now ?

Resolving the connection issue

In this case, the first test I do to understand exactly what is going on before blaming Gateway is to try to access this logged in address on the server where Gateway is installed:

Not surprisingly, the server is not recognizing this external address that these users are accessing, so the connection to the Gateway is not working. The same thing would happen if external access were to 189.123.123.123 IP, for example, and the bank server internal IP was 192.168.0.27, for example.

In order to make the gateway server can recognize this hostname or external IP, we can use these 2 solutions that I will demonstrate below (if you know others, leave them in the comments):

1 Solution - Alias ​​in SQL Server Configuration Manager

Click to view content
One way to “do X to” on SQL Server IPs or hostnames is by using the SQL Server Configuration Manager Alias ​​feature:

Don't forget to create the alias in x64 settings as well:

It is important to perform this Native Client driver configuration on both 32 bits (x86) and 64 bits (x64) versions, as some database access software only exists on 32 bits (eg SSMS) and others are often used. in 64 bit version (Ex: Power BI Desktop).
Attention: Pay attention to the protocol and port of the SQL Server instance where the connection will be pointed. If this information is wrong, the connection will not be made. An advantage of this approach is precisely what will be affected by this annotation and works with both external hostname and external IP. The downside of this solution is that it is SQL Server specific

After this note, the connection now functions normally within the server where the gateway is located (and in this case, the bank as well):

A simple test assures me that this note affects only SQL Server:

2 Solution - Hosts File

Click to view content
This second alternative is an operating system-level solution and involves changing the Windows hosts file so that it understands that a given hostname responds through an IP of your choice. On Windows, this file is located at C: \ Windows \ System32 \ drivers \ etc while on Linux the way is / Etc / hosts.

For this annotation to work, you will need to enter the IP where the chosen host should point, leave a blank space, and then enter the hostname that will be affected by this manual annotation:

And in doing so, any software that tries to use this hostname, for any protocol or port, will try to connect to the IP given in the hosts file (192.168.31.230, in this case).

As well as SQL Server Management Studio itself (SSMS), Power BI and many others:

Attention: The hosts file only allows pointing a hostname to an IP. If you access through a public IP only, you will need to use the Alias ​​solution in SQL Server Configuration Manager, start accessing from Hostname instead of IP, or use another pointing solution.

Testing the solution on Power BI Gateway

Now that I've demonstrated 2 ways to do this, let's test if they really work for my original goal of using this data source in my cloud-published Power BI report.

Successful connection:

Running gateway and report dataset associated with data source:

And as a result, reporting working for both the accessor using the internal network address and the external address 🙂

I hope you enjoyed this tip, a big hug and see you in the next article!