I have four tips for troubleshooting connectivity with the On-Premises Data Gateway on Power BI. These tips apply to any client application.

For this post, we will use an example of getting an error when trying to connect to a SQL Server named instance. In this case, we get an error when trying to set up a data source within an On-Premises Data Gateway from Power BI.

The error message we see is the following.

Cannot connect to the server.

When we look at the details of the error, we see the following.

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)

Pro Tip: error: 26 usually means we cannot talk to the SQL Browser service to convert the named instance name to the port.

If you have something that is remote to the data source, you are going to want to try it locally first. This helps to rule out a lot of things. In our example, the gateway is on a different machine from the SQL Server, so it is remote.

There are different ways that you can test local connectivity. You could use a Universal Data Link (UDL) file. You can also use the tools that ship with the data source. For Oracle, that would be SQL*Plus. For SQL Server, that would be Management Studio. You could also use the ODBC manager if you have an ODBC Driver installed on the machine. You can create a DSN  and test the connection as part of that DSN. Just make sure you do it on the actual machine the data source resides on.

If the connection works, then we know the data source, SQL Server in our example, is online and can accept at least local connections. This also means there is something stopping remote connections.

In our example, we are going to say that local connectivity is successful. This means that had we installed the gateway locally there probably wouldn’t be any issue and it would just work.

My gut is telling me that this is a Windows firewall issue. That comes from 10+ years of experience troubleshooting connectivity problems. It could be other issues as well, such as TCP and Named Pipes being disabled for the SQL Server and only allowing Shared Memory. This would only allow local connectivity. Maybe the machine is multi-homed (multiple NICs) and the remote machine is on a network that isn’t bound to the SQL Server. Meaning that SQL Server isn’t listening on that network. I can’t list all of the possible problems here, just know that there are usually more than one reason that something could be not working.

We know that the problem is remote applications being unable to connect to SQL Server. I want to simplify this and get the gateway out of the picture.

A lot of times we can become fixated on the app we are looking at and ignore other possibilities. You may know that locally works and remote does and start looking at why the gateway specifically isn’t working. Maybe you start to think something on the Power BI side is broken, or maybe we can’t talk to the Azure Service Bus. This is why I like to remove the gateway from the picture. Or, if you had a custom application, let’s remove that from the picture. Simplify as best you can. This helps to exclude a lot of potential complexity.

How do we do that? You can use the same test that you used locally! Use something like a UDL file or another tool to see if it can connect. Because it is SQL Server, I’d really like to try Management Studio. Management Studio uses .NET as does the gateway. To start, it doesn’t necessarily have to be the same machine as the gateway. If you have a different machine that has Management Studio installed, use that! If that works, then we may want to do the test specifically from the gateway machine.

In our case, Management Studio from any machine is producing the same error that the gateway was showing.

This means that all remote connectivity to SQL Server is failing. This has nothing to do with the gateway. By using a basic connectivity tool, we are able to exclude the gateway and all of the complexity that brings to the table. Outstanding! Now we can focus on the direct connectivity to SQL Server and not go down the rathole that is a more complex situation.

We are back to my gut feeling! Windows Firewall is enabled by default unless you have some corporate policy disabling it. Usually, it is enabled. When you install SQL Server, it does not place a firewall rule by default to let it receive outbound connections.

SQL Server can be a default instance or a named instance. The default instance uses TCP 1433 for its listening port. A named instance is more complicated. The TCP port for a named instance will be dynamic by default. Also, we have to be aware of SQL Browser.

SQL Browser is a windows service that helps to translate the named part of the instance to a TCP Port or pipe. In our example, the server we are connecting to is the following.

GUYINACUBESQL\STARWARS

SQL Browser will take STARWARS and return back the port. Then the client will connect to the TCP port for the instance. SQL Browser listens on UDP 1434. I mentioned a pro tip earlier that error 26 means we couldn’t talk to SQL Browser. It could also mean that something is just wrong with SQL Browser.

In all the years I’ve worked on connectivity issues, that only happened one time! Every other time, it was something blocking UDP 1434.

Which brings us to the Windows firewall. Depending on your network topology, it could be some device in between the client and server and not the Windows firewall. Start with the Windows firewall, though. If that doesn’t pan out, then find out what else is in between them.

You will need to add specific port rules for the Windows firewall. We know that we need to add UDP 1434 for SQL Browser but you also need to add the TCP port that the named instance is listening on. To do that, you need to find what that port is. You can use SQL Server Configuration Manager to discover it.

Once you have that port, you can add it as well.

You added the dynamic port for your SQL Instance to the firewall. What happens if that port changes? Your rule will no longer work. What if there was a port that you weren’t expecting? An example of this would be if you were using AlwaysOn and had a particular listener port for that versus the actual instance port.

Allowing an application through the firewall reduces the churn of managing all of those possibilities.

From a security perspective, creating rules for specific ports is more secure as you have tighter control.

You will want to verify if there are any specific guidelines for your organization for security. If it is allowed, though, this may make it easier to manage for you.

If you do allow an application through the firewall, you will not only need to let the SQL Server executable though but also SQL Browser. SQL Browser is a 32bit application. If you are on a 64bit machine, it will be under c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe.