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

[laszlo_kajan2]

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.