Technical Articles

shiromani soni

June 26, 2020 4 minute read

Connect HANA Cloud Service Instance with SAP Business Application Studio

SAP Business Application Studio is going to be the next version of SAP Web IDE, which is comparatively advanced in features and development efficiencies. It aims to significantly improve the development experience by providing us more control over the environment and code.

SAP Business Application Studio comes out of the box with predefined set of development environments – Dev-Spaces (virtual machine on the cloud where we can develop, build, test and run using run times and tools already installed) tailored for developing SAP business scenarios.

In order to connect SAP HANA Cloud with SAP Business Application Studio, it is assumed that you already have access to SAP Business Application Studio in DEV Space with a type of SAP Cloud Business Application, also you have been authorized with Business_Application_Studio_Developer role that you can find in Cloud Foundry Sub Account details and screenshot is attached for the same.

Once Role Collection is done, switch to Trust Configuration and SAP ID Service

Assign, Business_Application_Studio_Developer to the Developer Login ID

For additional details on Subscription and Assigning the roles, please refer link below:

https://blogs.sap.com/2020/03/06/sap-business-application-studio-trial-in-sap-cloud-platform-assigning-roles/

Once you login to SAP Business Application Studio, you see DEV space is currently stopped, please ignore if it’s already running.

Start the DEV space and wait until it’s up and running

So, DEV Space is up and running, we click on SAPCAPM and enter inside SAP Business Application Studio and we can move on to SQL Tools highlighted in the image to establish a connection with HANA Cloud Instance.

Before we move forward please open a new terminal as shown below

Pass the following commands in sequence in terminal:

1. mkdir -p ~/.ssl
2. cd ~/.ssl
3. npm install -g node-wget
4. wget https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
5. openssl x509 -inform der -in DigiCertGlobalRootCA.crt -out ~/.ssl/DigiCertGlobalRootCA.pem
6. cd ~

Why do we need these commands ? Answer is:

We need to have a trust-store with DigiCert, for connecting HANA as a Service (cloud service), the connection is required to be encrypted.This gets the proper certificate required for providing an encrypted connection, converts it to the proper format, and places it the the proper location so that the SQL connection configuration can find it.

Please refer link below for more information on this:

https://vscode-sqltools.mteixeira.dev/driver/saphana#Error

We need to choose appropriate Database Driver, in our case it is SAP HANA

Once chosen, following screen shows up

Pass all the required parameters, once passed SAVE CONNECTION and TEST CONNECTION buttons would be enabled.

In order to connect with HANA Cloud Service Instance, we need to make some changes in the settings, Open Preferences

SQLTools, Connections

Once clicked on connections, it asks us to ADD a value

Preference -> SQLTools->Connection->Add Value-> WorkSpace->SQL Connections

{
    "sqltools.connections": [
        {
            "dialect": "SAPHana",
            "port": -----,
            "server": "---------------------------------------",
            "name": "--------",
            "database": "DBADMIN",
            "username": "DBADMIN",
            "password": "-----------",
            "hanaOptions": {
                "encrypt": true,
                "sslValidateCertificate": true,
                "sslCryptoProvider": "openssl",
                "sslTrustStore": "/home/user/.ssl/DigiCertGlobalRootCA.pem"
            },
            "isActive": false,
            "isConnected": false
        }
    ]

As mentioned above, HANA Cloud Service Instance has to be Encrypted, along with Trust Store and DigiCert in it, same values have been passed to HANA Options with other required parameters.
Save and Click on Connect HANA Database Service Instance

Successfully, we are now connected with the HANA Cloud Service Instance.

Let’s check if it is really working, select HANA DB and click on SQL

Write SELECT Query on any exiting database table, in my case I am using DeviceMaster

Select the query and right click on it:

HANA Cloud Service Instance is connected and we are getting the result

I hope this blog would be useful for everyone looking forward to work on SAP Business Application Studio,please do share your views on this.

References:

Profile on SAP Community:

https://people.sap.com/shiromani.soni

Regards

Shiromani Soni

29 Comments

You must be Logged on to comment or reply to a post.

B.Sanjeev Naidu

June 28, 2020 at 3:15 am

Thanks shiormani soni for sharing the block with detailed steps. Let's try ?‍?

shiromani soni

Blog Post Author

June 28, 2020 at 3:23 am

Thank you Sanjeev 🙂

Liat Borenshtein

July 12, 2020 at 6:38 am

Hi,

Thanks for taking the time to contribute to the community of BAS and CAP 🙂

I think you can greatly improve your productivity, working with the tools that BAS has to offer.

You can create a new run configuration for a CAP Node application ( In the future we hope to add support for CAP Java apps as well) and use the bind option to connect to an SAP HANA instance, Then, the connection is created for you automatically and you can immediately move to the SQLTools view to check your data.

It will be great if you can give it a try and tell me what you think 🙂

Thanks,

Liat

shiromani soni

Blog Post Author

July 13, 2020 at 3:58 am

Hi Liat,

Thanks for writing and certainly it would add wings to BAS and CAP capabilities, looking forward to get hands on with the new and advanced features.

Regards,

Shiromani Soni

Sumit Kumar Kundu

October 19, 2020 at 8:43 pm

With the SQL connection created out of run configuration tools for my CAP node.js project, I am getting below error :

Deployment to container 62259D0F1AA849929E7C1DDFDD37E18F failed – error: Connection failed (RTE:[89006] System call ‘connect’ failed, rc=111:Connection refused {13.xxx.xxx.26:xxxxx} (<hana-instance-server>:<port>)) [Deployment ID: none].

Even after following this blog steps it is same.

Appreciate any help!

Best regards,

Sumit

Liat Borenshtein

October 20, 2020 at 5:28 am

Hi Sumit,

What are the steps you followed?

You created a run configuration and then have bind your db to a HANA instance?

Sumit Kumar Kundu

October 20, 2020 at 6:27 am

Yes per https://developers.sap.com/tutorials/appstudio-cap-deploy-app.html#7012a9b9-524d-48e0-8958-6246c0a34edd

I am using SAP Cloud Platform, SAP HANA Service though.

Best regards,

Sumit

Liat Borenshtein

October 21, 2020 at 5:49 am

Make sure HaaS is configured as 'open to all IPs'. I think this is the issue you're facing.

Thanks,

Liat

Sumit Kumar Kundu

October 21, 2020 at 6:55 pm

Spot on Liat! Thanks a lot for your help. It worked after alloed all IPs in HaaS instance.

Best regards,

Sumit

Pawan Kalyan K

July 27, 2020 at 9:23 am

Hi https://people.sap.com/shiromani.soni ,

May I know why I can’t see all the options that you see under SQLTools? What should I do to get those?

shiromani soni

Blog Post Author

July 27, 2020 at 5:18 pm

In SQL Tools, there is an option "Edit in settings.json", click on that and you would be able to see, please let me know if you are able to set up the connection.

Regards,

Shiromani Soni

Alfredo Semeco Blanco

August 6, 2020 at 2:12 am

Hi https://people.sap.com/shiromani.soni

I have the same error with connetcion : “Error opening connection only secure connections are allowed”

I applied all configs how you explain but keep the error: Error opening connection only secure connections are allowed.

Explain:

creating the connection, if i check this option: Save To Global Settings? then the connection only is created in file JSON where you config the "hanaOptions" with security access. If you dont check the option then the connection is only created in "connections bar IDE".

thanks

shiromani soni

Blog Post Author

August 6, 2020 at 5:16 am

Hi Alfredo,

Have you installed SSL Certificate as mentioned in the blog, if not please install it first and run the command in BAS terminal and carefully maintain settings.json and share the screenshot if you are facing the same error.

Regards,

Shiromani Soni

Ihar Panasiuk

August 25, 2020 at 4:53 pm

Hi https://people.sap.com/shiromani.soni

I have error when I try to connect my SAP HANA Database: "Error opening connection only secure connections are allowed".

Do you help me?

Shiromani Soni

August 27, 2020 at 4:11 am

Hi Ihar,

Please make sure you have installed SSL, execute following commands in Terminal of BAS:

1. mkdir -p ~/.ssl
2. cd ~/.ssl
3. npm install -g node-wget
4. wget –no-check-certificate https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
5. openssl x509 -inform der -in DigiCertGlobalRootCA.crt -out ~/.ssl/DigiCertGlobalRootCA.pem
6. cd ~

Ihar Panasiuk

September 3, 2020 at 11:20 am

Hi https://people.sap.com/shiromani.soni

Yes all steps are done, but I get the same error

Larry Duncan

September 15, 2020 at 8:43 pm

Hi Shiromani,

I think I missed a step. I am trying to run the commands but keep getting the

Error: Invalid URI "–no-check-certificate".

I don't have the certificate installed yet, but where am I supposed to install it?

Thanks

Larry

Denys van Kempen

September 16, 2020 at 11:55 am

wget --no-check-certificate https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt

Abhijit Patil

October 3, 2020 at 10:48 am

After this I am getting error as follows.. any hints? thanks!

user: .ssl $ wget --no-check-certificate https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt
Downloading...
--- error:
{ Error: unable to verify the first certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1088:34)
    at TLSSocket.emit (events.js:198:13)
    at TLSSocket._finishInit (_tls_wrap.js:666:8) code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE' }

shiromani soni

Blog Post Author

September 17, 2020 at 1:25 am

Hi Larry,

Could you please try with the given commands above in terminal, you should be able to install it, please follow the steps in sequence carefully in the connect Database terminal in BAS.

Regards,

Shiromani Soni

Abhijit Patil

October 3, 2020 at 10:55 am

I am facing same issue.. below is the terminal output.. any recommendations?.. thank you!

user: projects $ mkdir -p ~/.ssl
user: projects $ cd ~/.ssl
user: .ssl $ npm install -g node-wget
npm WARN deprecated request@2.85.0: request has been deprecated, see https://github.com/request/request/issues/3142
npm WARN deprecated har-validator@5.0.3: this library is no longer supported
npm WARN deprecated hawk@6.0.2: This module moved to @hapi/hawk. Please make sure to switch over as this distribution is no longer supported and may contain bugs and critical security issues.
npm WARN deprecated sntp@2.1.0: This module moved to @hapi/sntp. Please make sure to switch over as this distribution is no longer supported and may contain bugs and critical security issues.
npm WARN deprecated hoek@4.2.1: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
npm WARN deprecated boom@4.3.1: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
npm WARN deprecated cryptiles@3.1.4: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
npm WARN deprecated boom@5.2.0: This version has been deprecated in accordance with the hapi support policy (hapi.im/support). Please upgrade to the latest version to get the best features, bug fixes, and security patches. If you are unable to upgrade at this time, paid support is available for older versions (hapi.im/commercial).
/home/user/.node_modules_global/bin/wget -> /home/user/.node_modules_global/lib/node_modules/node-wget/cli.js
+ node-wget@0.4.3
updated 1 package in 1.152s
user: .ssl $ wget –no-check-certificate https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
Downloading...
--- error:
Error: Invalid URI "–no-check-certificate"
    at Request.init (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/request.js:274:31)
    at new Request (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/request.js:128:8)
    at request (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/index.js:53:10)
    at wget (/home/user/.node_modules_global/lib/node_modules/node-wget/wget.js:46:4)
    at Object.<anonymous> (/home/user/.node_modules_global/lib/node_modules/node-wget/cli.js:75:7)
    at Module._compile (internal/modules/cjs/loader.js:778:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)
user: .ssl $ wget –-no-check-certificate https://cacerts.digicert.com/DigiCertGlobalRootCA.crt
Downloading...
--- error:
Error: Invalid URI "–-no-check-certificate"
    at Request.init (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/request.js:274:31)
    at new Request (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/request.js:128:8)
    at request (/home/user/.node_modules_global/lib/node_modules/node-wget/node_modules/request/index.js:53:10)
    at wget (/home/user/.node_modules_global/lib/node_modules/node-wget/wget.js:46:4)
    at Object.<anonymous> (/home/user/.node_modules_global/lib/node_modules/node-wget/cli.js:75:7)
    at Module._compile (internal/modules/cjs/loader.js:778:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)
user: .ssl $

Jeffrey Pow Chun Kwok

October 7, 2020 at 4:46 am

Please retype --no-check-certificate. Apparently your first - (from --) has an ASCII value of 150 instead of 45.

megha vijayvergiya

September 30, 2020 at 5:50 am

Very nice and detailed explanation Shiromani. Keep up the good work.

Best Regards,

Megha

Former Member

October 20, 2020 at 12:47 pm

Hello Soni,

a lot has changed since this blog has been published.

are your steps still valid for SAP Cloud Platform cockpit 2.15.6 that has BAS subscription enabled in it?

Thank you,
greg

Palak Garg

October 22, 2020 at 4:07 pm

Shiromani Soni Thanks for the detailed blog. For the connection details: The url, server in sql.connections file and SQL tool settings will be the same as the details we get from service key?

If yes, then I'm getting Sockets closed by peer. I have completed the certificate download part successfully. Kindly help

shiromani soni

Blog Post Author

October 28, 2020 at 9:09 am

Please check in your HANA Connection, if Allow all IP address radio button is selected and SSL certificate is installed properly.

david huber

October 28, 2020 at 12:56 pm

Hi Soni,

Thank you for sharing this blog. I have one question.

I have a lot of experience working in SAP NEO environment but in this case all the tables, storeprocedures, schemas, etc. I create it from the web based workbench for HANA and after this i create a XSJS service which is call from my application using configured destination to make my SQL statement.

After explain this my question is what is the way to work in Cloud foundry i mean for creating the tables, the store procedure and after this call this from my application in SAP UI5 controller ?.

Thanks a lot for your help.

Best regards.

shiromani soni

Blog Post Author

October 28, 2020 at 1:09 pm

In Cloud Foundry environment, you need to familiarize yourself with SAP CAPM and I have provided links for the same in my blogs. Refer these links for BAS and you will get to know.

Currently we use WebIDE for creating calculation views, procedures and BAS (Business Application Studio) for database artifacts (tables, sequences etc.) however going forward BAS will have everything and you don't need to use separate tools but the integrated framework for everything required in order to create an end-to-end application.

Follow the link:

https://blogs.sap.com/2020/07/09/sap-cloud-application-programming-model-ep1/

Regards,

Shiromani Soni

david huber

October 28, 2020 at 1:20 pm

Hi Soni.

Thank you for your quick response.

I will check the blog you mention in your comment.

Best regards.

David.

Technical Articles

Connect HANA Cloud Service Instance with SAP Business Application Studio

Assigned Tags