DevTest configuration for connecting to Azure SQL Server

Products

• Service Virtualization

Issue/Introduction

This article will provide information on database connections to Azure SQL Server/SQLMI (SQL Managed Instance) for DevTest components IAM, Enterprise Dashboard and Registry using UMI (User Managed Identity).

Environment

All supported releases of DevTest.

Cause

The default JDBC driver {{IAM-HOME}}\lib\lib\main\com.microsoft.sqlserver.mssql-jdbc-12.4.2.jre11.jar we deliver with DevTest is not compatible. This is a common JDBC driver compatibility issue with Keycloak and Azure SQL Database. The error occurs because the Microsoft SQL Server JDBC driver version has compatibility issues with the Hibernate version used in Keycloak.

Resolution

Azure SQL Server supports only one database schema and it is recommended to use SQL Managed Instance (SQLMI) for multiple schemas.

The created UMI ClientID need to be added to the Database-->Security and then needs to be configured in the DevTest property files as below.

IAM: iam.properties

iam.db.vendor=mssql

iam.db.url=jdbc:sqlserver://[SERVER]:[PORT];databaseName=[DATABASENAME];msiClientId=<ClientIdCreated>;encrypt=true;trustServerCertificate=true;authentication=ActiveDirectoryMSI;

 

for IAM:

copy the file C:\\CA\\DevTest10x8\\lib\\shared\\mssql-jdbc-8.2.0.jre8.jar  to IAM_HOME/providers folder 

 

Enterprise Dashboard: dradis.properties

dradis.db.internal.enabled=false

dradis.db.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver

dradis.db.url=jdbc:sqlserver://[SERVER]:[PORT];databaseName=[DATABASENAME];msiClientId=<ClientIdCreated>;encrypt=true;trustServerCertificate=true;authentication=ActiveDirectoryMSI;

 

Registry: site.properties

lisadb.pool.common.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
lisadb.pool.common.url=jdbc:sqlserver://[SERVER]:[PORT];databaseName=[DATABASENAME];msiClientId=<ClientIdCreated>;encrypt=true;trustServerCertificate=true;authentication=ActiveDirectoryMSI;

lisadb.internal.enabled=false

Login credentials for Azure SQL server VM are Username & UMI ClientID as password.