Database Reader -> Azure with clientID/secretID/tenantID

[rjkroll]

This is my first time trying to connect to an Azure databse and first time trying to use a clientID,, secretID, and tenantID. Is this possible in a normal database reader? I am using MIrth 4.4.1. I have tried the normal way of using the JDBC driver from the dropdown, modifying the URL with the Azure database info, then tried putting the clientID as username and secretID as password, but it doesn't seem to work.

Do I need to try and do this through Javascript in a Reader/transfromer instead? Also, I have not downloaded any additional JARs for drivers after a fresh install of this version of Mirth, so I need to updated to the latest Microsoft JDBC drivers?

[jonbartels]

Which DB engine? SQL Server?

Can you share documentation that shows how to do this authentication with Java/JDBC? That can be converted into Mirth almost directly.

[rjkroll]

I'd be lying if I said I wasn't trying to use AI to try and solve it. It gave me the below solution to try connect to Azure and SQL. At this point, I am not sure what the proper way would be for me to try and connect to Azure using the types of credentials i was given. I normally just connect to a normal SQL Server database on windows, so I am not sure where to even begin. Everything I try, gives me some sort of errors, but the latest i attempted to do via javascript reader was :. At this point, im willing to try anything:

// Import necessary Java classes
var DriverManager = Packages.java.sql.DriverManager;
var Connection = Packages.java.sql.Connection;
var PreparedStatement = Packages.java.sql.PreparedStatement;
var ResultSet = Packages.java.sql.ResultSet;

// Define your connection parameters directly
var server_id = 'your_server_id.database.windows.net';
var client_id = 'your_client_id';
var client_secret = 'your_client_secret';
var database_name = 'your_database_name';

// Construct the connection string
var connectionString = 'jdbc:sqlserver://' + server_id + ':1433' +
    ';database=' + database_name +
    ';authentication=ActiveDirectoryServicePrincipal' +
    ';user=' + client_id +
    ';password=' + client_secret +
    ';encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;';

// Establish the connection
var connection = null;
var statement = null;
var resultSet = null;

try {
    // Load the JDBC driver
    Packages.java.lang.Class.forName('com.microsoft.sqlserver.jdbc.SQLServerDriver');
    
    // Establish the connection
    connection = DriverManager.getConnection(connectionString);
    
    // Execute your query
    var query = "SELECT * FROM your_table WHERE some_condition = ?";
    statement = connection.prepareStatement(query);
    statement.setString(1, "some_value");
    resultSet = statement.executeQuery();
    
    // Process the results
    while (resultSet.next()) {
        // Access column values, e.g.:
        var columnValue = resultSet.getString("column_name");
        // Do something with the data...
        logger.info("Column value: " + columnValue);
    }
} catch (e) {
    logger.error('Error in database operation: ' + e);
} finally {
    // Close resources
    if (resultSet) resultSet.close();
    if (statement) statement.close();
    if (connection) connection.close();
}

[ab-mg-23]

Given what you have for JS, you'll need to update the MS SQL Server JDBC driver and pull in the appropriate version of msal4j.

[rjkroll]

Just wanted to follow up on this to see if anyone knows how to make this work in Mirth. All I am trying to do is connect to an Azure SQL database, instead of a normal SQL server database. I've tried everything I can think of to try and get it to work using a normal Database Reader, but it it seems to connect, just fails Authentication every time. Am I looking at this incorrectly and is there no native way to connect via a Database Reader in Mirth? I am able to login via this powershell script. Can anyone help me understand what I am doing wrong in Mirth or how to convert this to a Mirth process?

# Check if Az module is installed, if not, install it
if (-not (Get-Module -ListAvailable -Name Az.Accounts)) {
    Write-Host "Az module not found. Installing..."
    Install-Module -Name Az -Repository PSGallery -Force -Scope CurrentUser
}
# Check if SqlServer module is installed, if not, install it
if (-not (Get-Module -ListAvailable -Name SqlServer)) {
    Write-Host "SqlServer module not found. Installing..."
    Install-Module -Name SqlServer -Force -AllowClobber  -Scope CurrentUser
}


Import-Module SqlServer

Import-Module Az.Accounts



# Azure AD service principal credentials
$tenantId  = 'xxxxxxxxxxx'
$clientId  = 'xxxxxxxxxxx'
$clientSecret =  'xxxxxxxxxxx'

# Azure SQL Database details
$serverName  = 'xxxx.database.windows.net'
$databaseName = 'xxx-databasename'



$connectionString = "Server=tcp:$serverName,1433;Initial Catalog=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

try {

    $secureSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential ($clientId, $secureSecret)
    Connect-AzAccount -ServicePrincipal -Tenant $tenantId -Credential $credential

 
    $token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token


    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.AccessToken = $token

    Write-Host "Attempting to connect to the database..."
    $connection.Open()
    Write-Host "Connection successful!"

    $command = $connection.CreateCommand()
    $command.CommandText = "SELECT @@version as version"

    Write-Host "Executing query: $($command.CommandText)"

    $reader = $command.ExecuteReader()
    
    if ($reader.HasRows) {
        Write-Host "Query executed successfully. Results:"
        while ($reader.Read()) {
            $row = @{}
            for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                $row[$reader.GetName($i)] = $reader.GetValue($i)
            }
            Write-Output $row
        }
    } else {
        Write-Host "Query executed successfully but returned no results."
    }

    # Close the reader and connection
    $reader.Close()
    $connection.Close()
}
catch {
    Write-Host "An error occurred: $_"
}

This successfully outputs the SQL version once it connects, but for the life of me, I can't convert this to work normally in Mirth without calling the Powershell from the command line

[jonbartels]

Can you show what JARs you have loaded?

Did you pull in the MSAL JAR that ab-20-m suggested?

At a glance, your JDBC code matches the MS example - https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-java?view=azuresql#create-a-new-java-project

[jonbartels]

If you've got the time, maybe pop into Slack for some discussion and debugging?

[rjkroll]

These where the JARs I needed to download as I was testing this process to get all of the errors resolved and get to the point where it connects, tries to login, but fails Auth. It doesn't show any more in the log as to why it fails, but like I mentioned, the same process using powershell works for some reason:

mssql-jdbc-12.6.3.jre8.jar
msal4j-1.16.1.jar
oauth2-oidc-sdk-11.13.jar
json-smart-2.5.1.jar

Let me know if you need any additional information. Also, is there a new link to the SLACK channel? The one I found isn't loading correctly for some reason.