- SAP Managed Tags:
The SAP HANA Service is now available, and there have been some questions about how to connect to a HANA database in the cloud from a local SQL client. It's a bit complicated because all connections to HANA Service must be encrypted. In this post I'll go through some of the common scenarios. You don't have to read it all: just find the bits you need.
Note: if you are building an XSA or Cloud Foundry application that binds your application to a HANA service broker you can stop reading here. The service broker provides you with a logical connection and you don't need to do anything else. This blog is intended only for standalone clients using SQL client/server connections.
The sequence of scenarios is:
Thanks go to Bjoern Brencher, Akshay Nayak, Tom Turchioe for contributing.
For a more in-depth treatment of how to connect using the SAP Common Crypto library, I recommend Philip Mugglestone's clear and detailed step-by-step video at the SAP HANA Academy YouTube channel.
Let's start with the identifiers you need. The SAP HANA Service Dashboard shows the basic information about ta HANA database. In particular, notice the endpoint and the ID.
- The endpoint is the host and port you need for TCP/IP connections. It is usually of the form zeus.hana.prod....ondemand.com:port and here I'll write it as zeus.hana...ondemand.com:port.
- The ID of the database is a GUID, such as 45fx7b7a7-a2d9-ad49-84ab-89106146b944.
In addition you need to know the user ID and password. I'll indicate these by HANA_USER and hana_password.
I'l walk through three cases, each of which have other cases included. To start with, I'll look at TCP/IP connections that do not require specification of a local certificate (test connections and connections on Windows). Then I'll show how you can use a WebSocket to connect. And then I'll walk through how to manage certificates for properly secure connections. One word of warning: I'm no security expert and I sometimes get the names wrong for certificates, keys, and so on. This is a blog post to meet a short-term need, and not official documentation!
In each case I'll show an hdbsql command to start with, and then show a connection example from another interface to show how the keywords work.
To use a WebSocket, you'll need a version of the HANA Clients at least 2.3.106. It's downloadable from https://tools.hana.ondemand.com/#hanatools and from SAP Software Downloads.
Here is a connection from hdbsql using TCP/IP. It shows where the endpoint appears, and that you need to specify an encrypted connection. The example is split over several lines for readability.
The -n option specifies the host and port, -u and -p are for the user name and password, and the -e option specifies an encrypted connection. The -ssltrustcert option skips the validation of the host certificate and is not recommended for production use. Here we just use it for testing purposes, to verify that we have the other connection parameters specified properly.
If you are using Windows, the HANA Client uses the Microsoft encryption library by default, and the Windows certificate store contains, by default, the certificate needed to verify the HANA Service key, so for Windows users you can use the following both for testing and in production:
For the programming interfaces, just use the keywords corresponding to each of the hdbsql options, which are all documented in the HANA Client Interface Programming Reference. Here is a node.js example:
And here is a python example. Notice that the values for the **encrypt** and **sslValidateCertificate** keywords are strings, not boolean values.
Again, if you are on Windows and the application has access to the certificate store, you can leave out the sslValidateCertificate keyword, and the client will look in the Microsoft certificate store to find the right certificate.
SAP Cloud Platform uses Digicert certificates. If your certificate store does not have the correct certificate, you may need to download it and store it in a file. Here are two ways to get an appropriate certificate.
You can get Digicert certificates from https://www.digicert.com/digicert-root-certificates.htm. The one to use is the DigiCert Global Root CA, Serial #: 08:3B:E0:56:90:42:46:B1:A1:75:6A:C9:59:91:C7:4A, Thumbprint: A8985D3A65E5E5C4B2D7D66D40C6DD2FB19C5436.
If you are using the OpenSSL encryption library, which is default on Linux and Mac OS, you need to convert this file to a "pem" format which the client can use. Copy the file DigiCertGlobalRootCA.crt to ~/.ssl and then run this command to generate a pem format file (split across multiple lines for readability):
Alternatively, you can get a certificate in the proper "PEM" format from the SAP Cloud Platform Cockpit. The CA Digicert certificate is stored in the Cloud Foundry service binding for usage in CF applications. You can see the certificate here: just select the text for the certificate itself (the whole string) and store it in a file: say ~/.ssl/DigiCertGlobalRootCA.pem. Newline characters can be left in or deleted; it doesn't make any difference.
Here is an hdbsql connection string that uses this certificate:
And here are the keywords in a node.js connection method:
The connection parameters for other languages are similar.
If you are running a node.js application on Cloud Foundry, you cannot access the file system directly and so you cannot specify a file in the the sslTrustStore keyword. Instead of the path given above, you can specify the certificate as a string, like this (the body of the certificate has been replaced by "..." here: you should include the entire certificate as a string).
Some organizations block TCP/IP ports for outgoing connections. A solution in these cases is to use WebSocket connections, which run TCP/IP over an HTTP connection.
To specify a WebSocket connection given the information on the HANA Service Dashboard you need to make the following changes to the TCP/IP examples above:
You may also need to provide parameters to specify a proxy server, as environments that limit outgoing ports usually require proxy server information. Here I'll assume the proxy host is just "proxy" and that the port is the default 8080.
Here is an hdbsql connection string using a WebSocket:
The proxyhost and port values can be left off if you are not behind a firewall. Also, if you are on Windows, the SSLTrustStore and SSLProvider may not be needed.
As before, you can look up the connection parameters for your programming language in the HANA Client Interfaces Programming Reference. Here is a Python example (note the proxy_port is a string, not an integer):
And here is a node.js example:
For node.js applications in Cloud Foundry, which do not have access to the file system, you can supply the SSL Trust store as a string instead of a path. Here it is with the middle of the string cut out:
This set of examples does not yet cover the use of SAP Common Crypto Library, but I hope it provides enough to get you through most cases.
Note: if you are building an XSA or Cloud Foundry application that binds your application to a HANA service broker you can stop reading here. The service broker provides you with a logical connection and you don't need to do anything else. This blog is intended only for standalone clients using SQL client/server connections.
The sequence of scenarios is:
- TCP/IP connections without certificate validation (for testing)
- TCP/IP connections for production use
- WebSocket connections, for use in organizations that block outgoing TCP/IP connections.
Thanks go to Bjoern Brencher, Akshay Nayak, Tom Turchioe for contributing.
For a more in-depth treatment of how to connect using the SAP Common Crypto library, I recommend Philip Mugglestone's clear and detailed step-by-step video at the SAP HANA Academy YouTube channel.
Let's start with the identifiers you need. The SAP HANA Service Dashboard shows the basic information about ta HANA database. In particular, notice the endpoint and the ID.
- The endpoint is the host and port you need for TCP/IP connections. It is usually of the form zeus.hana.prod....ondemand.com:port and here I'll write it as zeus.hana...ondemand.com:port.
- The ID of the database is a GUID, such as 45fx7b7a7-a2d9-ad49-84ab-89106146b944.
In addition you need to know the user ID and password. I'll indicate these by HANA_USER and hana_password.
I'l walk through three cases, each of which have other cases included. To start with, I'll look at TCP/IP connections that do not require specification of a local certificate (test connections and connections on Windows). Then I'll show how you can use a WebSocket to connect. And then I'll walk through how to manage certificates for properly secure connections. One word of warning: I'm no security expert and I sometimes get the names wrong for certificates, keys, and so on. This is a blog post to meet a short-term need, and not official documentation!
In each case I'll show an hdbsql command to start with, and then show a connection example from another interface to show how the keywords work.
To use a WebSocket, you'll need a version of the HANA Clients at least 2.3.106. It's downloadable from https://tools.hana.ondemand.com/#hanatools and from SAP Software Downloads.
First steps: TCP/IP connections without certificate validation
Here is a connection from hdbsql using TCP/IP. It shows where the endpoint appears, and that you need to specify an encrypted connection. The example is split over several lines for readability.
> hdbsql -n zeus.hana....ondemand.com:20058 \
-u HANA_USER \
-p hana_password \
-e \
-ssltrustcertThe -n option specifies the host and port, -u and -p are for the user name and password, and the -e option specifies an encrypted connection. The -ssltrustcert option skips the validation of the host certificate and is not recommended for production use. Here we just use it for testing purposes, to verify that we have the other connection parameters specified properly.
If you are using Windows, the HANA Client uses the Microsoft encryption library by default, and the Windows certificate store contains, by default, the certificate needed to verify the HANA Service key, so for Windows users you can use the following both for testing and in production:
> hdbsql -n zeus.hana....ondemand.com:20058 \
-u HANA_USER \
-p hana_password \
-eFor the programming interfaces, just use the keywords corresponding to each of the hdbsql options, which are all documented in the HANA Client Interface Programming Reference. Here is a node.js example:
var hana = require('@sap/hana-client');
var conn = hana.createConnection();
var conn_parms_tcp_test = {
serverNode : "zeus.hana....ondemand.com:port",
encrypt : true,
sslValidateCertificate: false,
uid : "HANA_USER",
pwd : "hana-password"
};
conn.connect(conn_parms_tcp_test, function(err) {
if (err) throw err;
conn.exec("SELECT DATABASE_NAME FROM M_DATABASES", function(err, result) {
if (err) throw err;
console.log("Database name", result[0].DATABASE_NAME);
conn.disconnect();
})
});And here is a python example. Notice that the values for the **encrypt** and **sslValidateCertificate** keywords are strings, not boolean values.
from hdbcli import dbapi
conn = dbapi.connect(
address="zeus.hana...dbaas.ondemand.com",
port=port,
user="HANA_USER",
password="hana_password",
encrypt='true',
sslValidateCertificate='false'
)
print("Connected") if conn.isconnected() else print("Not connected")
conn.close()Again, if you are on Windows and the application has access to the certificate store, you can leave out the sslValidateCertificate keyword, and the client will look in the Microsoft certificate store to find the right certificate.
Managing certificates
SAP Cloud Platform uses Digicert certificates. If your certificate store does not have the correct certificate, you may need to download it and store it in a file. Here are two ways to get an appropriate certificate.
Downloading a certificate from Digicert
You can get Digicert certificates from https://www.digicert.com/digicert-root-certificates.htm. The one to use is the DigiCert Global Root CA, Serial #: 08:3B:E0:56:90:42:46:B1:A1:75:6A:C9:59:91:C7:4A, Thumbprint: A8985D3A65E5E5C4B2D7D66D40C6DD2FB19C5436.
If you are using the OpenSSL encryption library, which is default on Linux and Mac OS, you need to convert this file to a "pem" format which the client can use. Copy the file DigiCertGlobalRootCA.crt to ~/.ssl and then run this command to generate a pem format file (split across multiple lines for readability):
> openssl x509 -inform der -in DigiCertGlobalRootCA.crt \
-out DigiCertGlobalRootCA.pemObtaining a certificate from the SAP Cloud Platform Cockpit
Alternatively, you can get a certificate in the proper "PEM" format from the SAP Cloud Platform Cockpit. The CA Digicert certificate is stored in the Cloud Foundry service binding for usage in CF applications. You can see the certificate here: just select the text for the certificate itself (the whole string) and store it in a file: say ~/.ssl/DigiCertGlobalRootCA.pem. Newline characters can be left in or deleted; it doesn't make any difference.
Connecting, using a certificate to validate the server
Here is an hdbsql connection string that uses this certificate:
> hdbsql -n zeus.hana...ondemand.com:20058 \
-u HANA_USER -p hana_password \
-e -sslprovider openssl \
-ssltruststore ~/.ssl/DigiCertGlobalRootCA.pemAnd here are the keywords in a node.js connection method:
var conn_parms_tcp = {
serverNode : "zeus.hana...ondemand.com:<port>",
encrypt : true,
sslCryptoProvider : "openssl",
sslTrustStore : "/path/to/home/.ssl/DigiCertGlobalRootCA.pem",
uid : "HANA_USER",
pwd : "hana_password"
};The connection parameters for other languages are similar.
If you are running a node.js application on Cloud Foundry, you cannot access the file system directly and so you cannot specify a file in the the sslTrustStore keyword. Instead of the path given above, you can specify the certificate as a string, like this (the body of the certificate has been replaced by "..." here: you should include the entire certificate as a string).
var conn_parms_tcp_string = {
serverNode : "zeus.hana....ondemand.com:<port>",
encrypt : true,
sslCryptoProvider : "openssl",
sslTrustStore : "-----BEGIN CERTIFICATE----- MIIDr...bd4= -----END CERTIFICATE-----",
uid : "HANA_USER",
pwd : "hana_password"
};WebSocket connections
Some organizations block TCP/IP ports for outgoing connections. A solution in these cases is to use WebSocket connections, which run TCP/IP over an HTTP connection.
To specify a WebSocket connection given the information on the HANA Service Dashboard you need to make the following changes to the TCP/IP examples above:
- In the hostname or address parameter, replace *zeus* at the beginning of the hostname with *wsproxy*.
- Replace the TCP/IP port with port 80.
- Add the WebSocketURL connection parameter, providing the address /service/service-id, where service-id is the ID value shown in the HANA Service Dashboard.
You may also need to provide parameters to specify a proxy server, as environments that limit outgoing ports usually require proxy server information. Here I'll assume the proxy host is just "proxy" and that the port is the default 8080.
Here is an hdbsql connection string using a WebSocket:
> hdbsql -n wsproxy.hana...ondemand.com:80 \
-wsurl /service/<service-id> \
-u HANA_USER -p hana-password \
-e -sslprovider openssl \
-ssltruststore ~/.ssl/DigiCertGlobalRootCA.pem \
-proxyhost proxy -proxyport 8080The proxyhost and port values can be left off if you are not behind a firewall. Also, if you are on Windows, the SSLTrustStore and SSLProvider may not be needed.
As before, you can look up the connection parameters for your programming language in the HANA Client Interfaces Programming Reference. Here is a Python example (note the proxy_port is a string, not an integer):
conn = dbapi.connect (
address="wsproxy.hana...ondemand.com",
port=80,
user="HANA_USER",
password="hana-password",
websocketurl='/service/<service-id>',
encrypt='true',
sslCryptoProvider='openssl',
sslTrustStore='/path/to/home/.ssl/DigiCertGlobalRootCA.pem',
proxy_host='proxy',
proxy_port='8080'
)And here is a node.js example:
var conn_parms_ws = {
serverNode : "wsproxy.hana...ondemand.com:80",
encrypt : true,
proxy_host : "proxy",
proxy_port : 8080,
webSocketURL : "/service/<service-id>",
sslCryptoProvider : "openssl",
sslTrustStore : "/path/to/home/.ssl/DigiCertGlobalRootCA.pem",
uid : "HANA_USER",
pwd : "hana-password"
};For node.js applications in Cloud Foundry, which do not have access to the file system, you can supply the SSL Trust store as a string instead of a path. Here it is with the middle of the string cut out:
var conn_parms_ws = {
serverNode : "wsproxy.hana...ondemand.com:80",
encrypt : true,
proxy_host : "proxy",
proxy_port : 8080,
webSocketURL : "/service/<service-id>",
sslCryptoProvider : "openssl",
sslTrustStore : "-----BEGIN CERTIFICATE----- MIID...Ths3p= -----END CERTIFICATE-----",
uid : "HANA_USER",
pwd : "hana-password"
};This set of examples does not yet cover the use of SAP Common Crypto Library, but I hope it provides enough to get you through most cases.
9 Comments
