Skip to Content
Technical Articles

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

 

10 Comments
You must be Logged on to comment or reply to a post.
  • 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

    /
    • 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

  • 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

  • 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?

    • 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

      • 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