Troubleshooting connection problems: HANA express from HANA Studio
After repeatedly seeing questions around failed connections to HANA Express, I’m compiling the basic troubleshooting steps I take when, for example, HANA Studio does not connect to my HXE instance. The most typical error is:
The system cannot be reached. logon data cannot be used.
The order in the steps is not random. If any of the tests fail, moving forward with the next step does not make any sense without fixing whatever is causing the test to fail.
Is the database running?
Sounds obvious but worth checking.
From the console in your server, you can use ./HDB info and make sure you can actually connect using hdbsql -i <<instance number>> -d <<database name> -u <<some user>> and run something like:
SELECT * FROM DUMMY;
Or check the tenant database is actually running if that is what you want to connect to (if it is not, the error will say “general error: database ‘hxe’ is unavailable SQLSTATE: HY000”):
select * from "SYS"."M_DATABASES";
You can start the tenant database with:
alter system start database <<tenant name>>;
If you encounter any errors when starting the database or in the logs ( /usr/sap/<SID>/HDBXX/<server name>/trace ), go do a search for them and best of luck fixing your database.
If you can effectively connect with hdbsql, let’s move to seeing if you can connect from outside.
Can only connect to SYSTEMDB but not the tenant
If your SQL client (HANA Studio) can connect to the SYSTEM database but not to the tenant database AND you could connect to the tenant in the previous step, you may need to take some additional steps like the ones described here:
Very old versions of HANA Studio could also be the problem.
Ping the external IP
In a cloud environment, you will find the external or public IP in the dashboard where you can manage your virtual machine.
On Google Cloud Platform:
On Amazon Web Services:
On MS Azure:
On SAP Cloud Appliance Library:
In a Virtual Machine, you will get it with command sudo ifconfig from inside the VM. If you do not get an IPv4 address (something in the shape of 188.8.131.529 ), it is not getting one assigned. This may be solved by changing the network configuration in the Virtual Machine but I’m not covering this here as it is already covered here..
On a command prompt in the same computer where you have the connection issues, ping the external IP:
If instead of a reply you get “Connection timeout.. Destination host not reachable“, your computer cannot reach the server. The IP may be wrong, you may be behind some corporate firewall or proxy or for some reason (not the case of the default configurations for HXE), there’s no reply sent back.
A firewall kind of problem could be ruled out by using an online ping tool, such as https://ping.eu/ or connecting from another place like your phone or home.
Most of the times, I’ve seen either the IP has changed because the server was restarted and the IP was set to ephemeral or the temptation to use the internal IP was too big.
Or whatever your hostname is. If you can reach the IP but pinging the host name gives back “Ping request could not find host ..”, time to go back to editing the hosts file (explained on step 4 in this tutorial).
Some coffee corner here
I have some weird anecdotes from people failing to edit the hosts file for the first time: some choose to create a new file like “hosts.txt” because they do not have the necessary administration rights while others just ignore the error message and don’t save the changes. However, the prize goes to a user that decided to copy the entire drivers folder into the Desktop so that the permissions would not be a problem.
Are the ports reachable?
The default pre-configured images on cloud providers come with the proper ports open. Make sure you can reach the ones needed by SQL clients (for example, 3xx13, 3xx15, 3xx17 – where “xx” stands for your instance number and 90 is the default for HXE).
I use Telnet for this. In my Windows computer, I had to enable it in the programs and features.
The connection succeeds pretty much if you do not get an error (I get a black screen for a short while). A closed port would say something like “Could not open connection to the host, on port 30013: Connect failed”.
Mac users can resort to nc -v hostname 3xx13 instead of telnet and should get a success message.
Note: The ports that are actually being used can be found using the SQL in this blog https://blogs.sap.com/2017/12/04/hey-sap-hana-express-edition-any-idea-whats-your-sql-port-number/ by Abdel DADOUCHE
If you cannot reach the ports but got so far, you probably need to ask your network admins to enable the ports needed to reach your HANA instance. Make sure you match the instance number and version/flavor of your server.
Trace the connection
You are not behind a firewall or proxy, all of the previous steps worked and still cannot connect from the SQL client? Enable the JDBC trace
In HANA Studio, Right click on the connection –> Properties–>JDBC trace:
Options for other clients can be found in the help.
Further help, as usual, can be found in the Q&A. Be sure to search for your question before you post it and add all the information you consider relevant (what error you are getting, what have you already tried and failed, some screenshots).