Skip to Content
Technical Articles
Author's profile photo Witalij Rudnicki

Allow connections to SAP HANA Cloud instance from selected IP addresses — using the command line

“Dance like nobody is watching.
Secure like everyone is.”

 

When you create a new instance of SAP HANA Cloud, a free trial instance for example, then a system suggests to “Deny all IP addresses (except SAP Cloud Platform)” by default.

But if you are like me and need to connect to your instance from clients, like Python code in Jupyter or local DBeaver IDE, then you need to allow external access to your instance: at least from your computer at the beginning. Allowing access from all IP addresses sounds tempting, but it is not the right path.

You may find an external IP address of your laptop, which other systems on the Internet see, using numerous services, like http://checkip.dyndns.org/ or https://whatismyipaddress.com/. My favorite is https://ifconfig.co/ip, especially for the fact that it can be used with command-line tools, as we will use later.

This IPv4 address shown is an IP address used by your Internet provider on the gateway to the public Internet.

Change allowed IP addresses from the command line

But then I switch to a corporate VPN and that changes my laptop’s external IP address as seen in https://ifconfig.co/ip. Or I deploy a VM in a cloud that hosts a client who needs to connect to my SAP HANA Cloud instance. Or I travel to an event, where I need to present something (ok, this is purely hypothetical under the current circumstances…) In cases like these, my client programs cannot connect to my SAP HANA Cloud instance anymore as their IP addresses are not on the list.

For the sake of time, I might not want to go to the SAP Cloud Platform website and to click through the UI to a place where I can add a new IP address. Or, I might not even be able to run a web browser from a VM in a cloud.

Changing allowed IP addresses from the command line is a convenient way to do it in such cases.

Step 1. Find the IP address

Notes: I am using macOS here. You might need to adjust some commands when running them on different operating systems, but the approach should be the same.

I have CloudFoundry CLI cf version 7 already installed. Please note this version is not fully compatible with version 6.

To display the external IP of a machine I use curl or wget (e.g. Jupyter Docker images have only wget pre-installed) running on that machine:

curl ifconfig.co/ip

or

wget -qO- ifconfig.co/ip

Here is the example of both commands returning IP addresses before and after switching to a VPN on my laptop. I use both just for demo purposes; you should pick the one you prefer and available.

And to illustrate hdbsql can connect to the database at first, but then a connection is refused with the error message -10709: Connection failed (RTE:[89008] Socket closed by peer, when I switched to VPN and my external IP address had changed to 193.16.xxx.xxx.

Step 2. Update the SAP HANA Cloud service

I have logged already to the region/subaccount/space, where my SAP HANA Cloud trial instance has been deployed and started with:

cf login -a https://api.cf.eu10.hana.ondemand.com

The name of my instance is vital-hc-hana, that I could see either in web UI or by running the command

cf s | grep "hana "

Finding%20the%20name%20of%20the%20service%20instance

Having the second IP address that I need to enable too, I can run the update-service command of the Cloud Foundry CLI program cf. We need to pass an array of IP addresses as keys in the payload {"data":{"whitelistIPs":[<IP addresses>]}} for a change -c of the service definition.

cf update-service vital-hc-hana \
 -c '{"data":{"whitelistIPs":["128.127.12.xxx", "193.16.224.xxx/24"]}}'

As the VPN address is usually selected from a range of addresses in a subnet, I am using a CIDR notation /24 for it in the command to match not just current address, but as well IP addresses assigned in the future when login to the same VPN region.

Please note, that the database instance is not available for a few minutes while the service instance is updated.

Step 3. Connect to the db instance

The goal is achieved and we can connect to the SAP HANA Cloud instance from a client on a machine with another IP address!

But that’s not the end. If you are like me, then you should have a lot of questions in your head about what just had happened, how did it work, and what this notation was. All are good questions and we go geekier and will dig into them in the next post.


Enjoy your weekend!
-Vitaliy (aka @Sygyzmundovych)

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Witalij,

      i think i have gotten to the query result: CURRENT_USER "DBADMIN" taking a somewhat different path.

      in any event, do you have any plans to go beyond "DBADMIN"?

      thx, gm

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Hi Greg. Thanks for trying this!

      Could you tell me what you mean by "plans to go beyond “DBADMIN”"? E.g. I do plan a series of posts using hana_ml Python library by a user different than DBAdmin. Is that a kind of what you are looking for?

      Best,
      -Witalij

      Author's profile photo Former Member
      Former Member

      Witalij,

      Sorry, had to renew my final trial account and set hdbsql path to serve as a kind of substitute for a remote IP address, but here is 'my' command:

      user: $ hdbsql -e -u dbadmin \
      > -n <my account>.hana.trial-us10.hanacloud.ondemand.com:443 \
      > -ssltruststore /etc/ssl/certs/trust.pem \
      > "SELECT CURRENT_USER FROM DUMMY"
      Password: <not a sudo one>
      CURRENT_USER
      "DBADMIN"
      1 row selected (overall time 14.263 msec; server time 378 usec)

      was wondering if there's a relatively uncomplicated way to run such queries for users other than "DBADMIN". i think Thomas Jung has wrapped hdbsql into hana-cli, so there could be additional work required to run it from within hana-cli, but since hdbsql is now 'allowed' in BAS, maybe we could also explore other hdbsql possibilities, whether in Python, nodejs, Go, or through any other library to execute HANA SQL queries as a non-admin user from the command line.

      i hope this makes sense and does not violate any open source or indirect access license agreements.

      Enjoy the rest of the weekend and thank you,

      greg

       

       

       

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Thanks for the additional explanation Former Member.

      The reason why I am using DBAdmin is that this user exists in every instance.

      Examples of how to set up an additional user and how to use it to query different clients can be found in the recently updated tutorials mission https://developers.sap.com/mission.hana-cloud-clients.html by Daniel Van Leeuwen

      Hope this helps. Have a good week ahead!

      Author's profile photo Former Member
      Former Member

      Hi Witalij,

      this was a good tip about switching the user and following the very good tutorial set by Daniel. i got it to work on HANA 4, which i think is a cloud version. however, when trying to execute HDBSQL commands against lower versions, that come with hdi, which i think is neither HXE nor HANA cloud, i'm kind of stuck with this error:

      hdbsql -n <id>.hana.trial-us10.hanacloud.ondemand.com:443 \
      > -u <schema_id>_<runtime_user_id>_RT \
      > -p <RT_user_password> \
      > -ssltruststore \s
      * -10709: Connection failed (RTE:[300015] SSL certificate validation failed: error code: 337047686, file:../ssl/statem/statem_clnt.c, line:1915 - error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed
      ( <id>.hana.trial-us10.hanacloud.ondemand.com:443))

      i don't want to open an issue on github as it may not fit into the HANA client tutorials and maybe it's going beyond the scope of your original blog post, but i can't think of a better place to ask.

      if there is such a better way (other than the confusing help.sap.com pages), just let me know but i don't think trial accounts are equipped with the standard SAP support.

      rgds,

      greg

      Author's profile photo Witalij Rudnicki
      Witalij Rudnicki
      Blog Post Author

      Hi Greg. Two issues I see is that in this hdbsql command:

      1. The flag -e ("encrypt communication") is missing
      2. -ssltruststore does not point to the actual store with the certificate (I am not sure what \s is here)

      Could you try after fixing these two?

      Author's profile photo Former Member
      Former Member

      Witalij,

      Both are good tips...and this seems to work for me at the moment:

      hdbsql -u <runtime_user>_RT \
      -p <runtime_password> \
      -n zeus.hana.prod.us-east-1.whitney.dbaas.ondemand.com:21022 \
      -e -ssltruststore \s

      ...

      hdbsql=> \s
      host : zeus.hana.prod.us-east-1.whitney.dbaas.ondemand.com:21022
      sid :
      user : <runtime_user>_RT
      kernel version: 2.00.0...

      hdbsql=> SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY
      CURRENT_USER,CURRENT_SCHEMA
      "<runtime_user>_RT","<runtime_user>_RT"

      ...looks a bit weird, but at least it works.

      thx again for the tips and keep those ssl blog posts coming.

      greg