Technical Articles
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
- 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).
- 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.
- 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.
- 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.
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:
And why is there a new tutorial based on WebIDE if WebIDE is soon to die?
Regards!
Andreas