(SM-2108) Azure SQL
- SNP
Datavard 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
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 |
|---|---|---|
1433 | tcp | Synapse Dedicated SQL pool endpoint |
80/443 | http/https | Azure BLOB 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 Azure SQL storage. Please follow the steps as described in the chapter 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)
Azure BLOB storage
Azure BLOB storage is required as storage for temporary data. Please follow this guide to create the storage Azure BLOB.
Azure SQL 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.
Also, it is needed to create a Master Key directly in Azure SQL DB. For more information check the official Microsoft documentation by link https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver15 .
The Master Key is needed for the automatic creation of Database Scoped Credentials and External Data Sources to enable the import of data from the referenced Azure BLOB storage using BULK INSERT operation.
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 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
|
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
Referenced storage | Already created referenced storage Azure BLOB |
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 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 |
Connection pool size | Size of connection pool used by connection pooling in Java Connector |
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_ASQL
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 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 |
Connection pool size | Size of connection pool used by connection pooling in Java Connector |
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 |