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.