Connect from SAP HANA Cloud trial to SAP HANA, express edition, in AWS via the Cloud Connector

This week Tae Suk Son announced the new possibility to use Cloud Connector with SAP HANA Cloud trial account.

You can follow the steps described by Daniel Van Leeuwen in the tutorial: https://developers.sap.com/tutorials/hana-dbx-remote-sources.html#47bd178a-66a2-4199-b4b0-c47b1245b732. Those steps show how to deploy the Cloud Connector on your local Windows machine to establish the connection. It might be a good option to try if your instance of SAP HANA express is running on your local machine too.

My case

…was slightly different. I have a few instances of SAP HANA, express edition, deployed to cloud vendors. Let’s focus on the one example, where it is deployed to Amazon Web Services (AWS).

It has a security group set that limits inbound traffic only to my laptop. Now with the Cloud Connector, I can make tables from my SAP HANA express database available to my trail instance of SAP HANA Cloud without opening HANA ports to the public internet! Let’s see how.

So, I activated the Cloud Connector in my SAP HANA Cloud trial instance. Next, I need to…

Deploy the Cloud Connector

There are different deployment options. One way — running it on a local machine — is described in Daniel’s tutorial. The other way is the one described by Patrick Leung how to Deploy SAP Cloud Connector on AWS using SAP Business Application Studio.

In my case, I want to run the Cloud Connector in the background as a Linux daemon (with automatic start capabilities at boot time), and in my case, it is Ok to install it on the same machine that runs my database.

Connect to the instance where SAP HANA, express edition, is deployed

From MacOS (simply, assuming you have an instance keyfile locally already):

ssh -i /path/to/my_instance_keyfile.pem ec2-user@hxehost

You can use other methods to logon to your instance’s OS.

The bonus Quick tip: How-to connect to an SAP HANA, express edition, EC2 host from the AWS CloudShell.

The Cloud Connector prerequisites

Now, in the OS shell of the instance running SAP HANA logged as an ec2-user OS user, I need to check and satisfy the installation prerequisites, specifically OS compatibility and Java 1.8 installation.

These and following installation steps should be done as root, that’s why first step sudo su -.

sudo su -

cat /etc/os-release
zypper search openjdk
zypper install -y java-1_8_0-openjdk
java -version

You can use SAP JVM too.

Install the Cloud Connector

And now I can install the Cloud Connector and check if it is running as a daemon. This way it will be available and running every time my SAP HANA, express edition, instance is up.

cd /tmp/
wget --no-cookies \
--header "Cookie: eula_3_1_agreed=tools.hana.ondemand.com/developer-license-3_1.txt" \
"https://tools.hana.ondemand.com/additional/sapcc-2.13.0-linux-x64.zip" \
-P /tmp/

unzip /tmp/sapcc-*-linux-*.zip
rpm -i com.sap.scc-ui-*.rpm
systemctl status scc_daemon

Please note that the version available at the time of writing this article was 2.13, but can be different at the time of reading. Check the current version at https://tools.hana.ondemand.com/#cloud.

Enable Internet access to the Cloud Connector instance

At this point, I can check the service is running from the VM’s shell with curl --insecure https://hxehost:8443/, but it is not available from the Internet, as I need to enable a rule allowing access to its port 8443.

For brevity (and geekiness) let’s use aws CLI instead of the AWS Cockpit.

Create a new security group to allow all incoming traffic to reach the port 8443 (my EC2 instance has a name HXE02; replace it and other technical names with ones from your environment):

#Set the AWS region
export AWS_REGION=eu-central-1

#Get Instance's VPC ID
aws ec2 describe-instances --filters "Name=tag:Name,Values=HXE02" \
 --query "Reservations[].Instances[0].NetworkInterfaces[0].VpcId[]"

#Create a new security group
aws ec2 create-security-group --vpc-id vpc-02708f64c1f7ef868 \
 --group-name CloudConnector \
 --description "Allow 8443 access to Cloud Connector"

#Add and ingress rule
aws ec2 authorize-security-group-ingress --group-id sg-07a8fbf91114b35e9 \
 --protocol tcp --port 8443 --cidr 0.0.0.0/0

#Display security group's ingress rules
aws ec2 describe-security-groups --group-id sg-07a8fbf91114b35e9 \
 --query "SecurityGroups[].IpPermissions"

Verify the result in the EC2 cockpit.

Now assign the additional security group to the instance running the Cloud Connector.

#Get instance Network Interface ID
aws ec2 describe-instances --filters "Name=tag:Name,Values=HXE02" \
 --query "Reservations[].Instances[0].NetworkInterfaces[0].NetworkInterfaceId"

#Get assigned security groups
 aws ec2 describe-network-interfaces --filters "Name=network-interface-id,Values=eni-0071160c754b88c6c" \
 --query "NetworkInterfaces[].Groups[].GroupId" 

#Get Instance ID
aws ec2 describe-instances --filters "Name=tag:Name,Values=HXE02" \
 --query "Reservations[].Instances[0].InstanceId"

#Set security groups
aws ec2 modify-instance-attribute --instance-id i-033f738d907b0773f \
 --groups sg-0e43ac22a862322ef sg-07a8fbf91114b35e9

Verify the result in the EC2 cockpit.

At this moment the Cloud Connector UI should be accessible on the Internet using the public IP address and the port: https://3.65.225.179:8443/.

From my local laptop, I can open it as well on https://hxehost:8443/, because it is the same host running SAP HANA, express edition.

Proceed with the configuration of the Cloud Connector

…as described in the tutorial.

In my case here is a subaccount configuration:

and here is the virtual host configuration:

Please note I am connecting to SystemDB database (port 39013) in this case.

Add remote sources in SAP HANA Cloud…

Now let me move to SAP HANA Cloud, where I can create a remote source…

-- DROP  REMOTE SOURCE "AWS_VITAL_HXE02_SYSTEMDB_SYSTEM" CASCADE;
CREATE REMOTE SOURCE "AWS_VITAL_HXE02_SYSTEMDB_SYSTEM"
	ADAPTER "hanaodbc"
	CONFIGURATION '
		Driver=libodbcHDB.so;
		ServerNode=aws-vitaliy-hxe02:39013;
		dmlMode=readonly;
		use_haas_socks_proxy=true;
	'
	WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=SYSTEM;password=myPa$$w0rd';

CALL CHECK_REMOTE_SOURCE('AWS_VITAL_HXE02_SYSTEMDB_SYSTEM');

Please note that:

• CONFIGURATION contains a mix of the remote source’s properties, like dmlMode and extra properties, like use_haas_socks_proxy,
• Both notations useHaasSocksProxy and use_haas_socks_proxy can be used,
• ODBC and HDBSQL properties can be included,
• Configuration of remote sources can be previewed and modified in a Database Explorer:
  

…and query virtual tables

It is SystemDB on SAP HANA, express edition, side, so not many tables with business data to query from there. Let me retrieve data in SAP HANA Cloud from a system table USERS in SAP HANA on-prem then.

CREATE VIRTUAL TABLE "DBADMIN"."V_HXE_USERS" 
AT "AWS_VITAL_HXE02_SYSTEMDB_SYSTEM"."<NULL>"."SYS"."USERS";

SELECT * FROM "DBADMIN"."V_HXE_USERS";

You can read more about A new approach for replicating tables across different SAP HANA systems in this post by Seungjoon Lee.

Enjoy an exploration of the data virtualization!
-Vitaliy aka @Sygyzmundovych