Skip to Content
Technical Articles
Author's profile photo Andrew Lunde

Creating an Encrypted Connection to HANA as a Service and HANA Cloud Service Instances in Business Application Studio

Warning: This blog post describes a quick work-around for those developers wishing to minimally connect to cloud based HANA instances. These instances are required to use an encrypted connection while most on-premise installations of HANA by default don’t require an encrypted connection. Once this capability is made available in Business Application Studio we can remove this post as it will be completely redundant. Please avoid linking to it directly.

-Andrew

 

Assuming that you have subscribed to SAP Business Application Studio and have given the Business_Application_Studio_Developer roll collection to your authorized user, create a new Dev Space with a type of SAP Cloud Business Application. If you need help follow this blog post for details.

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

I have only tested this in the US10 Cloud Foundry landscape. Your landscape may behave differently.

 

 

Once your Dev Space is up and running, click on it’s name to open it.

Check the version of Business Application Studio by clicking on the Help About menu item.

If the version stated is greater than 1.100.0-20200325202049, first follow the SQL Tools new connection wizard to see if it will allow you to connect to a cloud based HANA instance requiring an encrypted connection.  If it doesn’t then continue below.

Open a New Terminal.

In the terminal window copy, paste and execute each of the following lines in order.

mkdir -p ~/.ssl 

cd ~/.ssl 

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

openssl x509 -inform der -in DigiCertGlobalRootCA.crt -out ~/.ssl/DigiCertGlobalRootCA.pem 

cd ~

 

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.

Before any sql connection has been created, the json object that contains the list of possible connections has not yet been created, so we will first use the connection wizard to create a placeholder connection that we will then subsequently replace with the correct version.

Follow these steps.

Select the SQL Tools icon, Click on No Connections, and select SAP Hana.

Fill in the required fields with some value, I used aaa, until you’re allowed to save the connection.

Now we’re going to edit the settings file to manually replace the just created connection.  If you have other connections already defined, just merge the following example into the existing ones.

Look for the section called “sqltools.connections” and replace its contents with the following.

Make sure to replace the values marked with <> with ones that match your HANA instance.

If you don’t know your <> values, use the “cf env” command on a module that is bound to your HDI container or create a service key for your HDI container.

"sqltools.connections": [
  {
     "dialect": "SAPHana",
     "port": <your HANA instance server port>,
     "server": "<your HANA instance server hostname>",
     "name": "<a name for the connection>",
     "database": "<your HANA instance server schema name>",
     "username": "<your HANA instance server user(not hdi_user)>",
     "password": "<your HANA instance server password(not hdi_password)>",
     "hanaOptions": {
         "encrypt": true,
         "sslValidateCertificate": true,
         "sslCryptoProvider": "openssl",
         "sslTrustStore": "/home/user/.ssl/DigiCertGlobalRootCA.pem"
      },
      "isActive": false,
      "isConnected": false
  }
]

Save the preference file with File -> Save.

You now see your connection in the connections menu.  If you don’t, try clicking around or create another connection with bogus values using the new connection wizard.  There seems to be a bug that prevents the regeneration of this connection list.  Once you see it, click on the connection icon.

If this is the first time you’ve created a HANA connection, you will be prompted to install the HANA drivers.

Once the drivers have been installed, click the button to connect to your HANA instance.

If all goes well, you should see a confirmation message.

You can now expand the connection details.

Open a new sql file and run any free form query.

You have to select your sql statement and right click to select the “Run Selected Query” context menu item.

At this point you should see the results of your query in the window.

 

Hopefully this blog post helps out for the time being.

 

-Andrew

 

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Narendra Verma
      Narendra Verma

      HI,

      I am Following your blog , I am using “https://api.cf.eu10.hana.ondemand.com”  Cloud foundry  with sap business application studio trial.

       

      while selecting SQL Tools icon , no Icons are displayed in SQL Tools Settings tab.

       

      Please, Help.

       

      Narendra Kumar

      Author's profile photo Andrew Lunde
      Andrew Lunde
      Blog Post Author

      Did you modify the settings.json file sqltools.connections as described above?

      You will need to deploy a HANA HDI container to have something to connect to.  I didn't describe how to do that for you.

      -Andrew

      Author's profile photo Narendra Verma
      Narendra Verma

      Hi,

      This issue is on Mozilla Firefox, On Chrome  it's working.

       

      Thanks.

       

       

      Author's profile photo Andrew Lunde
      Andrew Lunde
      Blog Post Author

      Must be a browser thing then,  I only tested on Chrome, but thanks for brining up the issue.  I'll relate it to the tools team next chance I get.

      -Andrew

      Author's profile photo Robert Schulz
      Robert Schulz

      Hi Andrew,

      unfortunately your steps only work for me until the HANA extensions are installed. When trying the next step – actually connecting to the HANA – I get this error:

      Error opening connection Connection failed (RTE:[89006] System call ‘connect’ failed, rc=111:Connection refused {***} (***))

      The popup also has buttons to “show details” and “help!”, but the first only links to an ouput console (?) for SQLTools, that only shows “INFO: Executing SQLTools.showOutputChannel” and the other one goes to https://vscode-sqltools.mteixeira.dev/driver/saphana#Error which does not really help me either.

      Could it be possible that there are some authorizations missing on the HANA Service?
      The company I am working for is only just starting with Cloud Foundry and is – understandably – reluctant with granting authorizations and opening connections.

      Do you have other suggestions?

       

      Kind Regards

      Robert

      Author's profile photo Anilkumar Vippagunta
      Anilkumar Vippagunta

      wget might give error saying "certificate is not trusted" , you can download from https://vscode-sqltools.mteixeira.dev/driver/saphana#Error  and convert it to truststore.

       

      Regards,

      Anil

       

      Author's profile photo Andrew Lunde
      Andrew Lunde
      Blog Post Author

      Added no-check to the wget command.

      Author's profile photo Ihar Panasiuk
      Ihar Panasiuk

      Hello Andrew Lunde,

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

      Do you help me?

      Author's profile photo Andrew Lunde
      Andrew Lunde
      Blog Post Author

      Hi Ihar,

      HANA Cloud and HANA as a Service instances require encryption to be used.  Did you try modifying the connection definition as stated above?

       

      -Andrew

      Author's profile photo Ihar Panasiuk
      Ihar Panasiuk

      Yes, I have downloaded certificate and I have converted it to the proper format. After this I have used cf env <> command on a module that is bound to my HDI container. In short I have done all steps described in the blog.

      Kind Regards,

      Ihar

      Author's profile photo Heise Brian Nicholas
      Heise Brian Nicholas

      Hey there Andrew, thanks for your post.

       

      I followed your instructions, but when I try to connect I get the following error:

       

      Error opening connection Connect failed (invalid SERVERNODE '')

       

      I input the details as you described using the connection information from an application that's bound to an HDI container instance. Any idea why this doesn't work?

      Author's profile photo Andrew Lunde
      Andrew Lunde
      Blog Post Author

      This post is over a year old and relates to a method of connecting using a DB extension that is no longer available.

      In recent versions of BAS, search for the DB Explorer command for similar functionality.

       

      -Andrew

      Author's profile photo Heise Brian Nicholas
      Heise Brian Nicholas

      Thank you!