Table of Contents:

1 General Prerequisites
- 1.1 SAP NetWeaver release
- 1.2 Open Ports
- 1.3 Java connector
2 ADLS Gen 2 storage
3 Synapse storage
- 3.1 Create a Dedicated SQL pool
- 3.2 Create Schema (optional)
- 3.3 Create DB user
  - 3.3.1 a) OAuth 2.0 authentication
  - 3.3.2 b) Database user authentication
- 3.4 Download the JDBC driver for the SQL server
- 3.5 Create Synapse storage in Storage Management
  - 3.5.1 a) oAuth 2.0 Authentication
  - 3.5.2 b) JDBC user/password authentication

Storage management supports Synapse Dedicated SQL Pools in Azure. Storage Management automatically creates Synapse tables with round-robin distribution and can load them with data when used in combination with ADLS Gen2. It can also be used to query data back to SAP.

General Prerequisites

SAP NetWeaver release

Storage management requires SAP NW 7.01 SP15 or higher.

Open Ports

In a controlled network environment, it is common to have firewall rules in place. To enable communication between SAP systems and Azure, outbound communication from the SAP system to the following ports on the Azure side needs to be allowed:

Port	Type	AWS service

Port	Type	AWS service
1433	TCP	Synapse Dedicated SQL pool endpoint
80/443	HTTP/HTTPS	ADLS Gen2 endpoint

Storage Management allows encrypted communication through the public internet with Azure services, but for production deployment, it is recommended to have some kind of secure connectivity in place (VPN, Private Link, Private endpoints).

Java connector

Java connector is a critical middle-ware component used for processing SQL statements as well as facilitating communication with selected file storage services. Follow the steps described in the article Java Connector Setup to set it up before you continue.

The minimum required Java connector version is 222.

and library version supporting the msal4j library (optional for OAuth 2.0 Authentication).

ADLS Gen 2 storage

ADLS storage is required as storage for temporary data. Follow this guide to create the storage Azure Data Lake Gen2.

When you use OAuth2 authentication for ADLS, it can be reused also for Synapse.

Synapse storage

You need to create resources on Azure and set up authentication and authorizations. After that, you need to set connection details in Storage Management.

Create a Dedicated SQL pool

To get connectivity details for your SQL pool, enter your SQL pool Azure resource and go to Connection Strings.

Write down the above-highlighted information from the JDBC connection string: server hostname, database, and the user which will be used in the SM storage definition.

Create Schema (optional)

You can create a custom schema, otherwise, DBO is used.

Create DB user

a) OAuth 2.0 authentication

To use OAuth authentication using the same user that is set in the ADLS Gen2 storage, you need to add the user to Synapse and give him privileges for the database.

CREATE USER [app-glue-synapse-connector] FROM EXTERNAL PROVIDER; // app-glue-synapse-connector is an example Azure AD app. registration
EXEC sp_addrolemember 'db_owner', 'app-glue-synapse-connector';

b) Database user authentication

The other option is to use authentication using standard database users. You can use the built-in admin created during pool creation, or a dedicated technical user created later on.

// switch to Master database

CREATE LOGIN "app-datavard-connector" WITH PASSWORD = 'FovAy>pCYW%%WX'; // create your technical user and password

//switch to the working database
CREATE USER "app-datavard-connector" FOR LOGIN "app-datavard-connector";  

EXEC sp_addrolemember 'db_owner', 'app-datavard-connector';

Download the JDBC driver for the SQL server

Download the JDBC driver from the MS JDBC Download site. Upload the .jar file to the SAP application server at /sapmnt/<SID>/global/security/dvd_conn/mssql/
The file needs to be owned by <sid>adm:sapsys

$ ls -ld /sapmnt/DVQ/global/security/dvd_conn/*
drwxr-xr-x 2 dvqadm sapsys 4096 --- /sapmnt/DVQ/global/security/dvd_conn/mssql

$ ls -l /sapmnt/DVQ/global/security/dvd_conn/mssql
drwxr-xr-x 2 dvqadm sapsys 4096 --- /sapmnt/DVQ/global/security/dvd_conn/mssql/mssql-jdbc-9.2.1.jre15.jar

Create Synapse storage in Storage Management

You can choose between two authentication methods: database user/password, and oAuth2.0 credentials. For production environments, it is recommended to use oAuth.

Go to transaction /DVD/SM_SETUP
Create new storage of type AZURE_SDSP

a) oAuth 2.0 Authentication

Referenced storage	Already created referenced storage Azure Data Lake Gen2
Java connector RFC	RFC referencing the Java connector
Java call repeat	Number of times failed calls should be retried
Repeat delay (seconds)	Delay between retried calls
Dedicated SQL endpoint	Taken from JDBC database connection strings
Database name	Taken from JDBC database connection strings
Database schema	Existing schema in target DB (default is dbo)
Enable update	Not in use (for future implementation)
Use extended escaping	Not in use (for future implementation)
Driver path	Path to the JDBC driver (mssql-jdbc-9.2.0.jre8.jar)
Login timeout (seconds)	Maximum time in seconds in which the JDBC driver is trying to establish a connection
Fetch Size	Default size of fetch rows from the server using by JDBC driver
Hints	Additional string added to connection string when JDBC driver establishes a connection
OAuth 2.0 profile	Profile for OAuth 2.0 setup

b) JDBC user/password authentication