Secure connection from DBeaver to SAP HANA Cloud
In my previous post Secure connection from HDBSQL to SAP HANA Cloud I worked with HDBSQL. Now let’s move to DBeaver CE — a free database manager that I have used in some previous posts already. Today it got the latest update 7.0.3, which I already installed on my machines.
SAP HANA Cloud…
… is the new cloud-native SAP HANA product, used in my previous post or in the recent live coding episode by Thomas Jung.
Two quick notes before we start:
- As of today, it is available only as a paid service in SAP Cloud Platform, but (usual disclaimer about forward-looking statement applies) the trial version should be available later this year.
- As a reminder, the IP address of a machine from which a DB client tries to connect to SAP HANA Cloud instance — should be whitelisted by your administrator.
Luckily, in my case, I am one of the admins of this SAP HANA Cloud instance. So, it is accessible from my IP addresses.
I need to grab the endpoint of my instance, which is:
Back to DBeaver…
…where you can notice two great things:
- SAP HANA is already included in the list available drivers and
- HANA JDBC driver
ngdbcis downloaded automatically when required or updated. If needed, you can still point to a driver you might have pre-install on your machine already.
Let’s create a new connection.
So, I need to put Host and Port accordingly to the endpoint copied from my HANA Cloud instance cockpit. This will automatically populate the JDBC URL. Plus populate Username and Password fields. Ok, let’s Test Connection….
Receive of connect failed.“? Why? Of course, you know, if you read the previous post 😉
Clients can establish only secure connections to SAP HANA Cloud!
Let’s add this
encrypt parameter to the connection. Unfortunately, this is where I found DBevaer UI somewhat unintuitive.
Switch to Driver properties and right-click on User Properties to add a new property.
Unfortunately, DBevar did not allow me to put
encrypt=true just right here. Instead, I added
encrypt property, and then (it took me half an hour to figure our) double-click next to my property in Value column to edit. Type
Ok, let’s Test Connection now…
Here’s another obvious disclaimer.
Never use admin user, like
DBAdmin, to do your work!
Create separate users for admin or for development.
We are on MacOS… Have we missed anything?
Again, if you read the previous post, then you might remember that “DigiCert Global Root CA” root certificate was required to authenticate SAP HANA host to establish secure communication. And you might remember that on MacOS or Linix we had to explicitly use that certificate with HDBSQL.
What is different with DBeaver? It is a Java app and it is using the JDBC driver. The complete JRE is included in the DBeaver installation.
And by default JRE comes with the
cacerts store of root certificates. Let’s check.
cd /Applications/DBeaver.app/Contents/Eclipse/jre/Contents/Home bin/keytool -exportcert -cacerts -v -alias "digicertglobalrootca [jdk]" -rfc -storepass changeit
Don’t you trust yet?
Personally, I certainly would like to double-check, if these assumptions are correct.
I have another Java installation on my Mac:
so let’s check if it can be used to provide the
trustStore property for our JDBC connection instead.
And what about Windows?
Well, not much different. You use the default JRE installed with DBeaver…
…or you can use
cacerts delivered in some other
%JAVA_HOME%, like with SAP JVM in my case.
Looking for more information about JDBC?
There is a tutorial from https://people.sap.com/daniel.vanleeuwen available: Connect Using the SAP HANA JDBC Driver as a part of the tutorials mission Use Clients to Query an SAP HANA Database.
Trust me, I am an engineer.
-Vitaliy (aka @Sygyzmundovych)
It seems that in the current release you don‘t have to manually add the encrypt parameter. It is already predefined amongst others. However, you still need to set the value.
With the current release of DBevaer, SAP HANA (Cloud/SP05), or SAP HANA Client?
Current release of DBeaver. I have 7.1.1 installed.
Very useful article! Thanks! Adding to that: If you want to access a service instance (e.g. you are not the owner of the HDB), you can do so by viewing the content of the service key (which you can create for that purpose):
Press "View" ...
Then copy the host and port as indicated. As for user and password use those indicated as well. Enjoy!
P.S.: Indeed the value setting in dbeaver isn't intuitive! ?
Gunter Albrecht these details saved me a headache. PERFECTLY WORKING. THANKYOU !!!
It works for me. Thank you so much
Since I was connecting to an instance with a self-signed certificate, I also had to set the property 'validateCertificate' to false.
As we were facing another issue recently with while connecting using SQL Workbench/J including the newest HANA JDBC drivers: We had to add also the property 'disablecloudredirect' to true.
I wanted to mention that the property 'disableCloudRedirect' is an internal undocumented connection parameter. This should not be used in production. If using this switch enables connectivity, the underlying issue may be corrected by a restart of the SAP HANA Cloud database or by opening a support ticket. The use of this parameter can affect performance.
Dan van Leeuwen
thanks, sounds even better. I have submitted it to our support ticket (683232 / 2021). Looking forward that the support colleagues are aware of this and can solve the issue with the HANA cloud instance (below DWC - so managed by SAP).