Hello and welcome to the SAP HANA Academy.
The topic of this video tutorial series is SAP HANA Service and in this video we will
take a look at Secure Client Connections, a security topic for the Cloud Foundry environment.
Big topic, this is part one, where we cover default built-in cryptographic providers.
In the next video, part II, we will cover a more secure alternative, with SAP CommonCryptoLib.
This video has been recorded in January 2019.
For earlier or later releases, please check the playlist on the SAP HANA Academy.
Hi, I am Denys van Kempen.
You can only connect to the SAP HANA Service with secure connections, that is, with TLS/SSL.
This guarantees that all network traffic between the cloud service and your local client is
encrypted.
In this video, we will take a look at how this works using the default SSL provider
for Microsoft Windows, macOS, and Linux, and also the Java VM.
Both macOS and Linux use openSSL, which is open source, hence the name.
Microsoft uses proprietary software for encryption.
Let’s start with Windows.
Command prompt.
First, we are going to connect with the hdbsql command line tool.
This is included with any SAP HANA client.
hdbsql -n (for node)
the name of the node, or host we should say, we can get this from the SAP HANA Service
Dashboard
Endpoints
Direct SQL Connectivity
zeus.hana.prod then the region, in my case eu-central-1 then whitney.dbaas.ondemand.com
with the port number.
Make sure to check your IP whitelist.
I have set it to Allow all, for testing purposes.
If you set this to Deny all, all your client tests are going to fail, of course.
For production, you probably want to use a whitelist with the IP of the clients you want
to connect from.
OK
-u user -p password
Not a good idea, of course, to put this out on the command line
If you omit it, you will be prompted to enter the password, a better option for interactive
input
And then -e for encryption —here we are telling hdbsql to encrypt the connection,
this is required for the HANA Service
-ssltrustcert —this indicates to skip ssl validation, you would use this for test purposes
only
And finally, your query: ”SELECT SYSTEM_ID, DATABASE_NAME, VERSION FROM M_DATABASE"
And hana returns back H00 for both SID and database name with version 2.0 (HANA 2.)
040 revision 40 (SPS 04).
Very well.
Another example.
Let’s use the ODBC Data Source Administrator (64-bit).
Remember, on Windows you have 32 and 64-bit ODBC.
I have installed the 64-bit HANA Client.
If we click Add, we see the available drivers
HDBODBC
version 2.03 (HANA 2.0 SPS 03)
LIB ODBC HDB .dll
Here I have created a User DSN, so for my user account only.
Give your source a name and, optionally, a description.
Server:port is the same as before, from the SAP HANA Service Dashboard.
Click Settings.
Make sure to check Connect using SSL.
For production, you would also want to validate the SSL certificate, if you leave it unchecked
this does the same as the -ssltrustcert parameter we just passed on the command line to hdbsql.
OK.
Connect,
Enter user and password
Connect successful.
Another example, using JDBC on the command line.
The command is Java, so you will have to install a Java runtime, RTE, or SDK, developer kit.
-jar and then the path to the SAP HANA Client ngdbc.jar. that’s the next-generation database
client
-u username, password
-n the hostname with port -o options encrypt=true
-o validateCertificate=true, we can pass that one here
-c command
And we get the version back from our HANA service.
Now, this should work, pretty much out of the box.
Just a regular SAP HANA client: hdbsql, ODBC, jdbc.
Now, we are going to make it a little bit more complicated.
I am connect to linux server, this happens to be a HANA system but that’s not important
here.
What is relevant is that this system is behind a firewall.
It cannot go out onto the internet on any port, so what we are going to do is use the
proxyhost and proxyport parameters
These will be specific to your environment.
The ones used here are valid only for the SAP environment
And them we are going to use the web service proxy wsproxy, so this is a different hostname,
and we are also using a different port.
Port 80, the standard HTTP port, if there is one port open in the firewall, it is probably
going to be port 80, so that’s why this port is used here by default.
If we go back to our SAP HANA Service Dashboard, we can see that here: Web Socket Connectivity.
So, we are using this URL.
Exactly like this.
Then, we need to specify the web service url with the flag -wsurl and then /service/identifier
and the identifier, again, we get from the SAP HANA Service Dashboard.
This one.
username, password
-e to encrypt
-sslprovider openssl
This is the default provider on Linux and when we specify the sslprovider, we also need
to specify the ssltruststore, that is the store, the container, the safe, if you will,
but here we are only going to point to a root certificate.
Before we look at that, let’s run the query first.
So, this is working.
The root certificate comes from Digicert.
This is documented in the SAP HANA Service Security Guide.
And we need the Global Root CA, certificate authority, so you need to download that one.
I will put the URL in the description of this video, below.
I have uploaded the certificate to a directory named .ssl in the home directory of my linux
user.
This is a convention.
Not required.
No specific file or directory permissions needed here.
However, the file does need to be in a specific format.
We have downloaded the certificate with a .crt extension and we need to change this
into a PEM extension.
You cannot just rename the extension, that is not going to work.
So, we need to use an openssl command x509 (the type of certificate) -inform der (Distinguished
Encoding Rules), that’s the format, file in, file out.
You can name the file as you like but I would recommend to stick to the defaults until you
are familiar with all this encryption business as it can get complicated.
OK - this is Linux.
Regular HANA Client
Default encryption provider (openssl).
All we need to do is download the certificate from a root certificate authority provider
and convert the certificate into PEM format.
A PEM file is readable, it is like a text file.
If you have worked with certificates before, you have probably seen this.
The CRT format is binary.
OK.
Let’s switch to a Mac.
Behind the Windows, Mac runs UNIX, so it is the same thing.
You need to have installed a HANA Client and then we can run the hdbsql command.
-sslprovider openssl
-ssltruststore the path to the PEM file.
So, exactly like we did on the Linux system.
Or the java command
Note that the path now points to /Applications/sap/hdbclient/ that’s where the HANA Client is installed
into by default.
The rest is the same.
Same on Linux, same on Windows.
It is Java, after all.
OK.
We can also do this in Eclipse
A bit of Java code
The relevant part here is the connectionString, which we have defined here as a variable.
“jdbc:sap:// hostname port /? And then you can pass your options, encrypt=true is required.
You can run this and we get the version back again.
Java, and here Eclipse, uses its own SSL provider.
We do not have to specific the sslprovider or the root certificate authority file; it
is using, like on Microsoft Windows, its own provider and this already includes a CA which
can validate the certificate of our HANA Service.
Only when you want to use the SAP cryptographic library, you will need to specify this.
We will look at this in the next video.
Then, we can also run ODBC on the Mac or on Linux, for that matter.
Again, works the same way.
Here, I have create an ODBC entry.
File name is .odbc.ini in my home directory.
So this is for User Data Sources, same as we did on Windows.
Name of the data source between brackets
Path to the ODBC driver file
/Applications/sap/hdbclient/ and then libodbcHDB.dylib
Now dylib is specific to macOS, dynamic library
On Windows, we saw this file was a DLL, dynamic link library
On Linux, this will be an SO, shared object file.
Different names, serves the same purpose.
Parameter serverNode points to the hostname with port.
Encrypt=Yes
And then for openssl, the parameters sslCryptoProvider and ssltruststore with the absolute path to
the PEM file.
For user data sources you might get away with $HOME but to be safe, best to use the full
path here.
CommonCrypto is commented here with the # sign.
We will look at that in the next video .
OK, and then with isql, the unixODBC standard query utility, we should be able to connect:
isql Data Source Name username password
Select version.
Works as advertised.
Quit
OK - we can also do this in Python.
Another example.
Here I am using a Jupyter Notebook.
If you are familiar with Python, you will know what this is.
We are going to import the dbapi from hdbcli, you will need to do a “pip install” for
the python driver included with the HANA client.
I will put a link to the Code Repository for this video tutorial in the description of
this video below.
Then we build our connection.
Address = the hostname, port the port, user, password, encrypt=true.
No surprises.
On the Mac and on Linux, you will need the specify the sslCryptoProvider=openssl with
sslTrustStore pointing to the root certificate PEM file.
On Windows, as we have already seen, you can skip this, just like Eclipse, as Microsoft
will use its build-in provider.
And when we run this, we get back our result set.
OK - That’s it.
Connecting with HANA Clients to the SAP HANA Service using built-in, default, SSL providers.
In the next video, we will repeat the exercise with the SAP Common Crypto Lib, to make our
connections even more secure.
Thanks for watching.
You can find more video tutorials on our YouTube channel.
If you would like to be informed about new video tutorials, please subscribe to our channel.
You can connect with us on LinkedIn or follow us on Twitter, as well for updates, and if
you are watching this video on YouTube, do not hesitate to leave your comments to the
video page and, if you like, give us your vote on this video.
Thank you for watching the SAP HANA Academy