Skip to Content
Technical Articles
Author's profile photo Sohit Tandon

Accessing Azure SQL Database tables from SAP HANA Cloud

In this blog we will explore how to access an Azure SQL Database table from SAP HANA Cloud.

Prerequisites

  1. You have access to Microsoft Azure portal
  2. An Azure SQL Database
  3. Access to Microsoft SQL Server Management Studio
  4. Access to SAP HANA Cloud instance
  5. DP Agent with MssqlLogReaderAdapter enabled

We will start with Azure side of things. So let’s login to the Microsoft Azure portal and navigate to the Azure SQL Database. Towards the right of overview, click on set server firewall

Set%20server%20firewall

Set server firewall

 

We need to add the following hosts into the firewall so that they can access the Azure SQL Database

  • Client Machine used to login to Azure portal
  • DP Agent Host
  • SQL Server Management Studio Host

Add%20IP%20to%20firewall

Add IP to firewall

 

Login to the host which has Microsoft SQL Management Studio and create a connection to the Azure SQL database. You can get the Server name from the overview section (screen shot 1)

Microsoft%20SQL%20Server%20Management%20Studio%20connection%20to%20Azure%20SQL%20database

Microsoft SQL Server Management Studio connection to Azure SQL database

 

Once connected to the Azure SQL Database, navigate to master database and run the below SQL

CREATE LOGIN DP_USER with password = 'Welcome01'
go

This SQL command will create a login for DP Agent to login to the Azure SQL database

Create%20login%20for%20DP%20Agent

Create login for DP Agent

 

Connect to user database SQLDB_HSC_DE and run the SQL to create a user for login DP_USER

Create user DP_USER for login DP_USER

Create%20user%20for%20login%20DP_USER

Create user for login DP_USER

 

Now, we will make the user DP_USER as the db owner using the following SQL command.

EXEC sp_addrolemember ‘db_owner’,’DP_USER’

Log on to Azure SQL Database as user DP_USER and enable Direct Administration Connection DAC using the following commands.

sp_configure ‘remote admin connections’, 1
go

reconfigure
go

sp_configure ‘remote admin connections’
go

 

Let us look at the table in Azure SQL Database that we want to access from SAP HANA Cloud. It is the Address table

Address%20table%20in%20Azure%20SQL%20Database

Address table in Azure SQL Database

 

Login to SAP HANA Cloud and open the database explorer . Then navigate to Remote Sources under Catalog

SAP%20HANA%20Cloud

SAP HANA Cloud

Right click and add a new remote source named AZURE_SQL. We will need to provide the DP Agent location , Azure SQL Database IP and credentials to create the remote source as depicted in the below screenshot.

Remote%20source%20to%20Azure%20SQL%20Database

Remote source to Azure SQL Database

 

Once the remote source is created, it will display the objects in the Azure SQL database.

Objects%20in%20Azure%20SQL%20Database

Objects in Azure SQL Database

 

Choose the Address table and create a virtual object in SAP HANA Cloud.

Create%20virtual%20object%20in%20SAP%20HANA%20Cloud

Create virtual object in SAP HANA Cloud

Once the table is created, it will be visible under Tables in the SAP HANA Cloud catalog.

Address%20table%20schema%20in%20SAP%20HANA%20Cloud

Address table schema in SAP HANA Cloud

Click on open data to view the data in the Address table

Data%20preview%20of%20Address%20table

Data preview of Address table

 

Conclusion

SAP HANA Cloud provides users with options to access data residing in cloud databases like Azure SQL Database quite easily with simple configuration steps. This allows users to see data from various different data sources in a single pane.

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Adharapurapu Lakshmikanth
      Adharapurapu Lakshmikanth

      Hi Sohit,

      We have a requirement to put  sap hana data in to azure blob storage and we have succeded in doing this, we would like to create hana view based on virtual table by using SDA, this functionality is currently supported to connect azure blob storage or it is only applicable to Azure SQL Database ?

       

      BR

      Lakshmikanth

      Author's profile photo Sohit Tandon
      Sohit Tandon
      Blog Post Author

      Hi Lakshmikanth,

      You can import and also export data from azure blob storage. It is described here

      https://help.sap.com/viewer/f9c5015e72e04fffa14d7d4f7267d897/cloud/en-US/fd45a3b7917349a1a8cbc81e202c5cdd.html

      If you want to virtualise, you need to use the Azure datababase as described in below link

      https://help.sap.com/viewer/db19c7071e5f4101837e23f06e576495/cloud/en-US/816fae8415d94443bced7967fca78c8f.html

       

      Regards,

      Sohit

      Author's profile photo Ganesh Kumar
      Ganesh Kumar

      Hi Sohit,

      Does the above approach works for BW on HANA (7.5) on premise.

      Can we also do (in addition to simply access the tables) the Batch / Real-time replication to HANA DB (on-premise) using the MSSQLLogReader Adaptor

       

      Thanks

      Ganesh

       

      Author's profile photo Abhijith Ravindra
      Abhijith Ravindra

      Is the other way round possible? Connecting to hana db from azure? Using jdbc? Should we use cloud connector for that? Can the cloud connector run on azure vm?