Table of Contents:

1 General Prerequisites
- 1.1 SAP NetWeaver release
- 1.2 Open Ports
- 1.3 Java connector
- 1.4 Azure BLOB storage
- 1.5 Master Key
- 1.6 Connection information
- 1.7 Create Schema
- 1.8 Create DB user
  - 1.8.1 a) OAuth 2.0 authentication
  - 1.8.2 b) Database user authentication
- 1.9 Download JDBC driver for SQL server
2 Create Azure SQL storage in Storage Management
- 2.1 a) oAuth 2.0 Authentication
- 2.2 b) JDBC user/password authentication

Storage management supports SQL Database in Azure. Storage Management automatically creates Azure SQL tables and can load them with data when used in combination with Azure BLOB. It can also be used to query data back to SAP.

General Prerequisites

SAP NetWeaver release

Storage management requires SAP ABAP stack NW 7.01 SP15 or higher.

Open Ports

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	Protocol	Target

Port	Protocol	Target
1433	tcp	Database host FQDN (e.g. azuresql01.database.windows.net)
80/443	http/https	Azure BLOB endpoint (e.g. azureblob01.blob.core.windows.net)

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 Azure SQL storage. Please follow the installation steps described in the Java Connector Setup.
Minimum required Java connector version is 222.

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

Azure BLOB storage

Azure BLOB storage is required as intermediate storage during data transfer. Please follow this guide to create the storage Azure BLOB.

Master Key

To allow BULK INSERT into database tables from the BLOB container, Azure SQL needs to store Database Scoped Credentials.
SAS token used for authentication to BLOB container is used for creation or update of these credentials, but to store this sensitive information securely, Azure SQL requires the use of Master Key.
This can be simply created by executing query:

CREATE MASTER KEY;

Specific password can be chosen during Master Key creation.
More information in the official Microsoft documentation https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver15.

Connection information

To get connectivity details for your Azure SQL database, in the Azure portal managing interface select Overview and navigate to Show database connection strings:

Note the highlighted sqlserver hostname and database name.

Create Schema

It is recommended to create a separate schema for every SAP system connected to the database.
This action is not mandatory, as the default DBO schema can be used if there will be a 1:1 relation between the SAP system and the whole Azure SQL DB.

Create DB user

There are two authentication options:

a) OAuth 2.0 authentication

To use OAuth authentication, there needs to be an application registration resulting in Tenant ID, Client ID, and Secret strings. OAuth profile setup is documented in OAuth 2.0 Authentication.
Afterward, registered application Display Name can be added as DB user:

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

To enable OAuth 2.0 authentication from the SAP side, an OAuth RFC destination needs to be created.

b) Database user authentication

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

// switch to Master database if not using single database
CREATE LOGIN "app-glue-connector" WITH PASSWORD = 'FovAy>pCYW%%WX'; // create your technical user and password

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

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

Download JDBC driver for SQL server

Download JDBC driver from MS JDBC Download site. Upload the .jar file to the sub-directory under /sapmnt filesystem, so it's available to each application server.
The default directory for the driver is /sapmnt/<SID>/global/security/dvd_conn/mssql/, but it can be changed freely, as long as every application server will have access to it via same SAP logical path/file.
The default SAP logical file (of type DIR) used to address directory with JDBC driver is /DVD/DEF_MSSQL_DRIVER.

$ 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 Azure SQL 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_SQL

a) oAuth 2.0 Authentication

Storage ID	Logical identifier of the storage (maximum 10 characters)
Referenced storage	Azure BLOB storage ID serving as staging directory during data transfer
Java connector RFC	RFC destination for communication with Java connector
Java call repeat	Number of times failed JDBC calls are repeated
Repeat delay (seconds)	Delay between repeated JDBC calls
Dedicated SQL endpoint	Database server name as specified in JDBC connection details
JDBC Port	Port for JDBC connection, 1433 is currently fixed port number
Database name	Database name as specified in JDBC connection details
Database schema	Database schema to be used (default is DBO)
Enable update	Not in use (for future implementation)
Use extended escaping	Encapsulates data contents in special characters to avoid possible problems during BULK INSERT to Azure SQL table
Driver path	SAP Logical path defined via transaction FILE. Consists of concatenation of Logical File Path and Logical File Name. Example:
Login timeout (seconds)	Time interval in seconds during which the JDBC driver is trying to establish a connection
Fetch Size	Default size of fetch rows from the server using by JDBC driver (e.g. 500)
Hints	Additional parameters added to JDBC connection string
Connection pool size	Size of connection pool used by connection pooling in JCo
OAuth 2.0 profile	Profile for OAuth 2.0 setup

b) JDBC user/password authentication

All configuration is identical as in a), only the Authentication part is switched to Use JDBC User/Password, and actual credentials are filled in.
Password can be typed in and converted to a hash string to avoid direct visibility.

SNP

(SM-2111) Azure SQL