Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
I501256
Advisor
Advisor
3,961
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 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 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 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 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 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 table in Azure SQL Database


 

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


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 source to Azure SQL Database


 

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


Objects in Azure SQL Database


 

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


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 table schema in SAP HANA Cloud


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


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.
4 Comments