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 the Secure User Store, a security topic.
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.
Server-side we will be using the SAP HANA Service, but this video is relevant for any
SAP HANA client connection be in the cloud or on-premise, and with client I mean the
full client, could be ODBC, JDBC/Java, Python, Go, Ruby, all supported clients.
Works exactly the same.
In this video, I will show you how you can use a user store key as connection string.
The secure user store contains server name + port, with a user name and password.
So this will enable you to connect with your full client without specifying a password.
Now, hdbuserstore is a command line tool, so most of this video will be command line.
The good news is that, as it is command line, it works exactly the same on Windows, Linux
and Mac.
Here we have the macOS Terminal, for example.
hdbuserstore is included with the SAP HANA client, so you will need to install the client.
If you want to connect to the SAP HANA Service, and in particular to use SAP CommonCryptoLib
for encryption, you might want to install the SAP HANA Client for the SAP HANA Service
as it is included, so you have all the right versions.
Here is the command: hdbuserstore
The path to the HANA client is defined as environment variable.
This required for CommonCryptoLib, not for hdbuserstore per se, but you might want to
add it anyway, makes it a bit easier to work on the command line.
On macOS and Linux, you can use the bash_profile, for example.
There are other options as well.
On Windows, it is set in System Properties.
Same, open a prompt and enter the command, and you should get the help screen.
It is a simple command.
The encryption key used is generated at install time, if your client is a clone, maybe running
in the cloud, you might want to generate a new encryption key before using it.
hdbuserstore ChangeKey is the command.
Case is not important.
Now, let’s create a key.
I will be using the -i for interactive option, this way I will be prompted to enter a password
and do not have to enter it on the command line.
Set name of key, I will be using HAASKEY here.
Any name will do but no spaces or special characters.
Servername:port we get from the SAP HANA Service Dashboard: SQL Connection.
For on-premise HANA just use the hostname and port of the name server, and when using
tenant databases, specify the database name.
Username: well, for production systems you would never use SYSTEM of course.
Here, we are just doing a quick test.
Enter the password, and we are done.
With the List command, we can see the entries in our user store.
In our case, there is only a single entry.
Key Haaskey.
We also see the actual secure user store file, the data file and the key file to unlock the
store.
Server-side, we have already discussed the Secure Store in the File System, SSFS, for
encrypted connections (data in flight) and encrypted storage (data-at-rest).
There is a video about Data Storage and Network encryption in this series.
Here, you can see the same SSFS technology is used.
What about Windows?
Well, like I said: same command.
ChangeKey
Set
List
The key is stored in, well what would be a hidden dot directory on Linux and macOS, on
Windows it is not hidden, in ProgramData, .hdb, computer name, user identifier.
On macOS, it is in the user directory, .hdb, computer name.
So how do we use the key?
Well, let’s start with the interactive terminal command: hdbsql.
In stead of specifying with -n the server name plus port, -u username, -p password,
all we have to do is use an uppercase -U and specify the name of the key, and we are connected.
Because we connect to the HANA Service and use the SAP CommonCryptoLibrary to encrypt
our connection, we also need to enter -e and -sslprovider and -ssltruststore.
For a regular, on-premise connections, just the user key would suffice.
On Windows, of course, exactly the same command.
We do not even have to turn the slash into a backslash.
Let’s take a look at ODBC.
We created the entry in the video about encryption using the SAP CommonCryptoLib.
For CommonCrypto, we defined sslcryptoprovider and ssltruststore.
If you do not need SSL, or if you do need SSL but want to use the default Microsoft
encryption provider, you do not need to set these.
This is not specific to the SAP HANA Service, this governs how we want to encrypt our client
connection.
Could be for on-premise as well.
Without secure user store key, to connect, we would have to enter username and password.
Server:port contains the hostname plus SQL Port.
Here we have the entry for the HANA Service but for on-premise this would look the same.
OK.
Connect Successful.
Click Settings.
For the user store key, add the key parameter with as value, the name of the key.
Case is not important.
OK
If we now click Connect, we instantly get the Connect Successful.
No need to enter username and password.
Now, our key also includes server name and port, so why do we need to define this here?
Well, if you don’t you will be prompted to provide one, and a username.
Call it a bug, call it a limitation, but for this ODBC Administration tool you need to
add one.
How, does this look in the registry?
Here we have our entry, key = keyname.
OK
Linux and macOS do not work with a registry, of course, but with files.
But the rest stays the same.
Here we have a sample ODBC.INI file.
It is in my home directory, so for user DSNs.
The parameters are the same as for Windows
key=keyname
And then we can use the isql unixODBC query tool
Username and password are optional.
So, we can connect with just our key.
Run our query.
All is well.
OK
By the way, if we comment out the serverNode parameter in the ODBC.INI file, we can still
connect, so we do not have this limitation for the driver manager on macOS or Linux.
For your C++ code this would not make any difference.
For JDBC, this is similar in a way.
When we try to make a connection on the command line, we get an error: invalid option.
When we want to connect, we have to start with username, password and there is no Key
option.
However, when we implement this in a Java class, this works perfectly fine (as it would
in your C++ code for our ODBC example)
We have commented out the original connection string with username, password, and now just
make the connection with the key.
Connection test successful.
Here as well, though, we need to provide the hostname with port.
Very well, let’s try another client.
Python, for example.
I am using a Jupyter Notebook.
Here we have an example with address, port, user, password.
And this is the one with the secure user store key.
Just the key is enough.
Again, encrypt, sslCryptoProvider and sslTrustStore are only included to make an encrypted connection
using the SAP CommonCrypto library to the HANA service.
For a regular on-premise connection without SSL, you only need the key.
Very easy, yet, still very secure.
For the other clients, Node.js, Go, and Ruby, I will add some code samples to the blog for
this video.
There will be a link in the description of this video, below.
OK.
So much for the secure user store.
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