With SAP’s continued evolution of their cloud offerings, it is likely that on some projects we’ll have to connect an on-premise DSP to HANA as a Service running on the SAP Cloud Platform. One example of this is to populate a HANA staging area for S/4HANA Migration Cockpit where both HANA and S/4HANA are services on the SAP Cloud Platform.
The following steps show how to create a connection from a SQL Server instance to HANA as a Service running on the Neo Trial environment in SAP’s Cloud Platform. Knowledge of HANA and SQL Server is required for the configuration. Once the connection from SQL Server is configured, the DSP can use it to read and write data to the HANA service using the same techniques as would be used for an on-premise HANA database.
1 - Log into the SAP Cloud Platform Cockpit, enable the SAP HANA service and create a new database.
Use a sensible Database ID and ensure Web Access is ON.
It will take a few minutes for the database to be provisioned and started.
2 - Create a HANA database user for the DSP connection. The user does not require any special permissions.
3 - Create a test table with some rows
create table test(ID int, name varchar(20));
insert into test values(1, 'michael');
insert into test values(2, 'jon');
At this point we have a HANA as a Service database called connectiondemo, a HANA database user DSP, and a table called TEST. We must now configure connectivity from the on-premise system to the cloud.
4 - Install the Cloud Connector
The Cloud Connector must be installed on-premise on a machine that has connectivity to the SQL Server instance used for the DSP. We installed it on the SQL Server server.
Download and install the Cloud Connector for your OS - https://tools.hana.ondemand.com/#cloud. Installation and configuration are covered in this document - https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/e6c7616abb5710148cfcf3e75d96d596.html
5 - Log into the on-premise cloud connector administration cockpit and register your SAP Cloud Platform account.
Once your account is successfully registered you can see the connection from both the on-premise cloud connector administration cockpit and the cloud platform cockpit.
6 - Configure the service channel
In the on-premise cloud connector administration cockpit, select your cloud account, select the On-Premise to Cloud option from the left hand menu, then click on the Add button (+).
Select HANA Database as the Type.
Enter the name of the HANA database created in step 1, and a Local Instance Number (any except 00).
When the service channel is created the local Port (30115 in our example) can now be used to connect to the HANA database.
The final steps are similar to connecting to an on-premise HANA database. We assume the HANA Client is already installed.
7 - Create an ODBC DSN
Open the ODBC Data Source Administrator, select the System DSN tab, then click Add…
Select the HDBODBC driver.
Specify a Data Source Name and Description. The server and port is determining using the hostname of the server where the Cloud Connector is installed and the service channel port from step 6. In our example the Cloud Connector is on the same machine as the SQL Server instance, so we used localhost and port 30115.
8 - Create a SQL Server Linked Server
In SQL Server Management Studio create a new linked server.
Specify a name for the Linked Server and select Microsoft OLE DB Provider for ODBC Drivers. Enter the DSN created in step 7 as the data source.
On the security page use the credentials created in step 2 as the remote login.
You can now execute queries against the HANA as a Service database using the OPENQUERY function, which passes the statement to the remote system for efficient processing.
Please sign in to leave a comment.