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().
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:
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:
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();