Connect SAC with Azure Synapse SQL via SAC Agent using ActiveDirectoryPassword authentication

[timmybecker]

Introduction

This is the right guide in case you have the challenge to create an import connection to MS Synapse SQL DB which is only accessible from on-prem, due to security or other reasons.

For more general procedure I'd recommend to start with the SAP Blog from @kenneth_dalvik:

Tech Blog from Kenneth Dalvik 

The more detailed information you'll find in this blog entry. I'll describe in detail how to prepare SAC Agent for a successful connection using ActiveDirectoryPassword as authentication method (In the latest MS SQL driver it would refere to "With Microsoft Entra ID Password authentication using a login ID and password entered by the user.)

Remark: Configuration happened on a Windows server

Prerequisites:

• Firewall setup completed that Synapse SQL DB accessible on Port 1433 from SAC Agent
• Host from SAC Agent has access to login.microsoft.com domain
• SAC Agent configured and DriverConfig.properties files maintained
• A user that supports authentication method ActiveDirectoryPassword without MFA to Synapse SQL DB
• JDBC driver compatible to Synapse SQL DB version
• MSAL4J Java library and depended packages for ActiveDirectoryPassword auth method

Architecture:

This is the high level solution architecture, further details will be explained below

Configure SAC Agent:

Create the config file and place it in the Tomcat folder

<Tomcat path>\webapps\C4A_AGENT\configure\DriverConfig.properties

Example file is available here: SAC Help

Place the compatible jdbc driver (I've downloaded it fromsomewhere on the server convenient for you and copy the location of the file.

MS SQL Server 2019=<file path>\mssql-jdbc-12.8.1.jre11.jar

Create system variable on the server named SAP_CLOUD_AGENT_PROPERTIES_PATH

Add the java options parameter:

-DSAP_CLOUD_AGENT_PROPERTIES_PATH=<Tomcat path>\webapps\C4A_AGENT\configure\DriverConfig.properties

In order to allow usage of my desired authentication it was observed that additional java libraries were required. The library and all dependend libraries for msal4j have been added to <Tomcat path>\lib. The below listed items have been added:

• accessors-smart-2.5.2.jar
• apiguardian-api-1.1.2.jar
• asm-9.7.1.jar
• content-type-2.3.jar
• jackson-annotations-2.18.1.jar
• jackson-core-2.18.1.jar
• jackson-databind-2.18.1.jar
• jcip-annotations-1.0-1.jar
• slf4j-api-1.7.36.jar
• json-smart-2.5.2.jar
• junit-jupiter-api-5.11.0.jar
• junit-jupiter-params-5.11.0.jar
• junit-platform-commons-1.11.0.jar
• lang-tag-1.7.jar
• msal4j-1.19.1.jar
• nimbus-jose-jwt-10.0.1.jar
• oauth2-oidc-sdk-11.23.jar
• opentest4j-1.3.0.jar

->Restart the SAC Agent service.

Create SQL Database connection within SAC

In SAC frontent go to connections->connections  

Create a new connection and select SQL Database:

Enter connection parameters. Those connection parameters have been essential:

encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;authentication=ActiveDirectoryPassword;

Confirm with OK. Ideally the connection is saved and can be used later on.

I hope this prevents some headache and effort.

Please share your comments.

Thanks.

Best regards,

Timmy