Skip to Content
Technical Articles
Author's profile photo Laszlo Kajan

How to tunnel Business Application Studio SQLTools to shared HANA Cloud service instance?

Problem statement

  • You have a HANA Cloud service with ‘Deny all IP addresses (except SAP Cloud Platform)’ set,
  • you develop your MTA using the Business Application Studio (BAS),
  • you would like to deploy the database parts of the project to the HANA Cloud instance,
  • you would like to open the deployed schema in the BAS’s SQLTools.

Because of the ‘Deny all IP addresses (except SAP Cloud Platform)’ setting, –

  • the deployed MTA functions correctly, but –
  • you can’t use ‘cds deploy –to hana’ to deploy the database part of the MTA, you get “Connection failed (RTE:[89008] Socket closed by peer”,
  • you can’t use BAS’s SQLTools to connect to the deployed schema, you get “Connection failed (RTE:[89008] Socket closed by peer (0ceab516-b578-4cd6-a0e3-732911be7e6d.hana.prod-eu10.hanacloud.ondemand.com:443))” when adding a new connection.

Solution

  1. Open a tunnel to an app <your-cf-app> deployed in your Cloud Foundry environment:
    cf allow-space-ssh <your-cf-space>;
    cf enable-ssh <your-cf-app>;
    cf ssh -N -T <your-cf-app> -L 4443:<hana-host>:<hana-port>;
    • Get <hana-host>:<hana-port> from the binding of a deployed app that is bound to the database.
    • Keep the tunnel open while you use the connection (defined below).
  2. Deploy the database part with:
    cds deploy --to hana --tunnel-address 127.0.0.1:4443​
    • This command updates ‘default-env.json’. Observe how ‘hostname_in_certificate’ is added to the “VCAP_SERVICES”.”hana”.”credentials” section, which now contains the host and port of the BAS end of the tunnel.
  3. Add a connection to ‘~/.theia/settings.json’ like this:
        "sqltools.connections": [
            {
                "dialect": "SAPHana",
                "port": 4443,
                "server": "localhost",
                "name": "hana-via-tunnel",
                "database": "105A31...AFA8",
                "username": "105A31...Y_RT",
                "password": "***",
                "hanaOptions": {
                    "ENCRYPT": true,
                    "sslHostNameInCertificate": "<hana-host>"
                }
            }
        ],
    • For SSL trust to work, make sure you have the public key of the issuer of the certificate of the server in ‘~/.ssl/trust.pem’. You can get the issuer public key from the binding of a deployed app that is bound to the database.
  4. Connect to the database using the connection added above.

Further reading

CAPM Cookbook – Using Databases

Author and motivation

Laszlo Kajan is a full stack Fiori/SAPUI5 expert, present on the SAPUI5 field since 2015, diversifying into the area of SCP development.

The motivation behind this blog post is to provide a solution for developing with IP-restricted Cloud Foundry HANA services in the Business Application Studio.

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andreas Teufer
      Andreas Teufer

      Hi.

      I just working on this tutorial:

      https://developers.sap.com/tutorials/hana-trial-advanced-analytics.html

      If I try to build the repository - I got the mentioned issue:

      Connection failed (RTE:[89008] Socket closed by peer (15bc0e68-0bd3-44a0-9433-1d8791fdd9d1.hana.trial-eu10.hanacloud.ondemand.com:443)) [depId].

      In my point of view your description of the solution is more for experts.

      So I don't know how to solve following statements of yours:

      • Get <hana-host>:<hana-port> from the binding of a deployed app that is bound to the database.
      • Add a connection to ‘~/.theia/settings.json’ -> Where is this file?

      And why is there a new tutorial based on WebIDE if WebIDE is soon to die?

      Regards!

      Andreas