(SM-2108) Azure Synapse SQL

Datavard 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

Datavard 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, an 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

Datavard 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 both S3 and Redshift storage. Please follow the steps in the article Java Connector Setup to set it up before you continue.

Minimum required Java connector version is 222

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

ADLS Gen 2 storage

ADLS storage is required as storage for temporary data. Please 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, set up authentication and authorizations. After that, you need to set connection details in Datavard 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, 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 JDBC driver for SQL server

Download JDBC driver from MS JDBC Download site. Upload the .jar file to 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 2 authentication methods: database user/password, oAuth2.0 credentials. For production environments, it’s 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

Dedicated SQL endpoint

Taken form JDBC database connection strings

Database name

Taken form 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 which the JDBC driver is trying to establish connection

Fetch Size

Default size of fetch rows from server using by JDBC driver

Hints

Additional string added to connection string when JDBC driver establish connection

OAuth 2.0 profile

Profile for OAuth 2.0 setup

b) JDBC user/password authentication

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

 

Referenced storage

Already created referenced storage Azure Data Lake Gen2

Java connector RFC

RFC referencing the Java connector

Dedicated SQL endpoint

Taken form JDBC database connection strings

Database name

Taken form 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 which the JDBC driver is trying to establish connection

Fetch Size

Default size of fetch rows from server using by JDBC driver

Hints

Additional string added to connection string when JDBC driver establish connection

Username

IF using JDBC user/password authentication - username

Password

IF using JDBC user/password authentication - password

Password hashed

Set to true is password is hashed