1

I'm trying to make a JDBC connection using the access token that I retrieve right before making the call, through a POST call in "Token Request". I then use accessToken=${__property(access_token)} inside the JDBC Connection Configuration to retrieve it. However, even though the token is retrieved correctly (I checked with debugger), the JDBC call always fails on the first run, but runs successfully for any later call. For the consequent runs, I'm running the same exact script without altering anything, and it works. Restarting JMeter once again causes the first run to fail.

According to this post, it is because JDBC Connection Configuration is initialized before any other variables/properties are set, which makes sense. However, no matter what I try I cannot force the JDBC Connection Configuration to initialize AFTER I assign the token. Is there a way to do it?

First request fails but the second passes through

I tried putting the JDBC Connection Configuration and the JDBC call in a separate Thread Group and ticking "Run Thread Groups consecutively" in my Test Plan. I also tried putting JDBC Connection Configuration inside a While Controller that only runs after the property "access_token" is not null. Moreover, I tried replacing __property() with __P().

Veerano
  • 11
  • 3

2 Answers2

0

Move your "Token Request" sampler under setUp Thread Group.

This way the request will be executed and the property will be set before the JDBC Connection Configuration initialization.

More information: How to Use the SetUp Thread Group in JMeter

Dmitri T
  • 159,985
  • 5
  • 83
  • 133
  • Surprisingly, this didn't help, even though it sounds exactly like what I need. Perhaps JDBC Connection Configuration initialization happens before everything, regardless of whether a SetUp Thread Group is used or not? – Veerano Aug 17 '23 at 23:24
0

I was ultimately unable to make it work with JDBC Connection Configuration; however, I found a workaround by using JSR223 Sampler instead, and I think this is actually a better method since it doesn't involve requesting and using access token; all it needs is client ID and client secret.

Within JSR223 Sampler, I chose "Language: java (BeanShell)" and manually write Java code inside, based on this article, section ActiveDirectoryServicePrincipal: https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver16.

My Test Plan now has the following structure:

  • Test Plan
    • Thread Group
      • User Defined Variables
      • JSR223 Sampler
      • View Results Tree

Note that I needed to add some additional dependencies that MSAL4J relies on (it wasn't obvious from the errors I got so feel like I have to mention it here). I got the following .jar files from Maven repository:

  • msal4j-1.13.3.jar
  • mssql-jdbc-10.2.0.jre8.jar
  • mssql-jdbc_auth-12.2.0x64.dll (this one is a .dll not a .jar file)
  • content-type-2.2jar
  • oauth2-oidc-sdk-10.13.2.jar
  • nimbus-jose-jwt-9.31.jar

The Java code inside my JSR223 Sampler ended up being as follows, where vars.get() accesses the corresponding variable defined in User Defined Variables:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class AADServicePrincipal {
    public void executeQuery() {
       SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName(vars.get("server_name")); // Replace with your server name
        ds.setDatabaseName(vars.get("database_name")); // Replace with your database
        ds.setAuthentication("ActiveDirectoryServicePrincipal");
        ds.setUser(vars.get("client_id")); // Replace with your user name
        ds.setPassword(vars.get("client_secret")); // Replace with your password

       try {
          Connection connection = ds.getConnection();
            Statement stmt = connection.createStatement();
            String query = "SELECT COUNT(*) FROM table1; ";
            ResultSet rs = stmt.executeQuery(query);
            System.out.println("Performed the query successfully!");
            printResult(rs);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Exception: " + e);
        }
    }

    private void printResult(ResultSet rs) {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnsCount = rsmd.getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= columnsCount; i++) {
                if (i > 1) {
                    System.out.println(", ");
                }
                String columnValue = rs.getString(i);
                System.out.println(columnValue + " " + rsmd.getColumnName(i));
            }
            System.out.println();
        }
    }
}


AADServicePrincipal session = new AADServicePrincipal();
session.executeQuery();
Veerano
  • 11
  • 3