The "driver_path" paramater is not working in the embulk-input-sqlserver plugin

[kei-hito]

ENV

• OS: Debian 11.4
• Java: openjdk version "1.8.0_342
• embulk-0.9.24
• embulk-input-sqlserver: 0.13.2

TO REPRODUCE

1. Download the jdbc driver mssql-jdbc-9.2.0.jre8.jar from microsoft github.

wget https://github.com/microsoft/mssql-jdbc/releases/download/v9.2.0/mssql-jdbc-9.2.0.jre8.jar

2. Create the config.yml.

in:
  type: sqlserver
  driver_path: /path/to/driver/mssql-jdbc-9.2.0.jre8.jar
  url: jdbc:sqlserver://xxxxxxxxxx.net:1433;database=xxx;authentication=ActiveDirectoryPassword;
  user: xxxxxx
  password: xxxxxx
  table: xxxxxx
out:
  type: stdout

4. Execute the "embulk run config.yml"

Data transfer was successful.
However, the log shows that driver v7.2.2.0 is used instead of the driver specified in driver_path(v9.2.0).

2024-09-09 07:06:16.024 +0000 [INFO] (0001:transaction): Using JDBC Driver 7.2.2.0

[Full log]

user# embulk run config.yml
2024-09-09 07:06:10.482 +0000: Embulk v0.9.24
2024-09-09 07:06:11.258 +0000 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
2024-09-09 07:06:13.150 +0000 [INFO] (main): Gem's home and path are set by default: "/root/.embulk/lib/gems"
2024-09-09 07:06:14.689 +0000 [INFO] (main): Started Embulk v0.9.24
2024-09-09 07:06:14.786 +0000 [INFO] (0001:transaction): Loaded plugin embulk-input-sqlserver (0.13.2)
2024-09-09 07:06:14.839 +0000 [WARN] (0001:transaction): "UTC" is recognized as "Z" to be compatible with the legacy style.
2024-09-09 07:06:14.870 +0000 [INFO] (0001:transaction): Connecting to jdbc:sqlserver://xxxxxx.net:1433;database=xxxxxx;authentication=ActiveDirectoryPassword; options {user=xxxxxx, password=***, applicationName=embulk-input-sqlserver, loginTimeout=300, socketTimeout=1800000}
2024-09-09 07:06:15.652 +0000 [INFO] (pool-2-thread-1): [Correlation ID: xxxxxxxxxxxxxxxxxxx] Instance discovery was successful
2024-09-09 07:06:16.024 +0000 [INFO] (0001:transaction): Using JDBC Driver 7.2.2.0
2024-09-09 07:06:16.157 +0000 [INFO] (0001:transaction): Using local thread executor with max_threads=16 / output tasks 8 = input tasks 1 * 8
2024-09-09 07:06:16.164 +0000 [INFO] (0001:transaction): {done:  0 / 1, running: 0}
2024-09-09 07:06:16.195 +0000 [WARN] (0020:task-0000): Z is deprecated as a military time zone name. Use UTC instead.
2024-09-09 07:06:16.196 +0000 [WARN] (0020:task-0000): "Z" is recognized as "Z" to be compatible with the legacy style.
2024-09-09 07:06:16.262 +0000 [INFO] (0020:task-0000): Connecting to jdbc:sqlserver://xxxxxxxxxxx, password=***, applicationName=embulk-input-sqlserver, loginTimeout=300, socketTimeout=1800000}
2024-09-09 07:06:16.513 +0000 [INFO] (pool-5-thread-1): [Correlation ID: xxxxxxxxx] Instance discovery was successful
2024-09-09 07:06:16.814 +0000 [INFO] (0020:task-0000): SQL: SELECT * FROM "xxxxxxx"
2024-09-09 07:06:16.820 +0000 [INFO] (0020:task-0000): > 0.00 seconds

[hiroyuki-sato]

Hello, @kei-hito. Thank you for your report.
It seems that driver_path in the sqlserver doesn't support yet. We will discuss about this feature.

[kei-hito]

@hiroyuki-sato Thank you for your reply.
I understand that this is not supported.

[hiroyuki-sato]

@kei-hito Could you tell me why you want to use a newer JDBC driver?

[kei-hito]

@hiroyuki-sato
I would like to use "AzureDirectoryServicePrincipal" or "AzureDirectoryManagedIdentity" as the authentication type.
Those authenticaqtion types require v9.2 or v8.3.1 or later of Microsoft JDBC Driver for SQL Server.
So I need a newer JDBC Driver.

Official Microsoft Document: Connect using Microsoft Entra authentication

[hiroyuki-sato]

@kei-hito Thanks.

We need more discussion about this improvement.
It will take a few time.

Below is the temporary workaround. (You can build it by yourself)

diff --git a/embulk-input-sqlserver/build.gradle b/embulk-input-sqlserver/build.gradle
index 6f99454..2ecd4c3 100644
--- a/embulk-input-sqlserver/build.gradle
+++ b/embulk-input-sqlserver/build.gradle
@@ -1,6 +1,6 @@
 dependencies {
     implementation(project(path: ":embulk-input-jdbc", configuration: "runtimeElements"))
-    implementation "com.microsoft.sqlserver:mssql-jdbc:7.2.2.jre8"
+    defaultJdbcDriver "com.microsoft.sqlserver:mssql-jdbc:7.2.2.jre8"
     implementation("com.microsoft.azure:adal4j:1.6.7") {
         exclude group: 'org.slf4j', module: 'slf4j-api'
         exclude group: "org.apache.commons", module: "commons-lang3"

git clone http://github.com/embulk/embulk-input-jdbc
patch -p1 < diff.patch
./gradlew embulk-input-sqlserver:dependencies --write-locks
./gradlew embulk-input-sqlserver:gem

You will find embulk-input-sqlserver/build/gems/embulk-input-sqlserver-0.14.0-java.gem

[kei-hito]

@hiroyuki-sato
Thank you for your immediate response.
Could you tell me why you use "defaultJdbcDriver" instead of "implementation".
I tryied building with a newer version of driver with "implementation" and it seems to work correctly.

-    implementation "com.microsoft.sqlserver:mssql-jdbc:7.2.2.jre8"
+   implementation "com.microsoft.sqlserver:mssql-jdbc:12.8.1.jre8"

[hiroyuki-sato]

@kei-hito

If you build plugin by yourself, implementation is OK.

We will not change default JDBC driver yet, so attached code expected use diver_path configuration.
Attached code does not implement completely. so It can't find default JDBC driver (You must need driver_path configuration explicitly).

[kei-hito]

For now, I will try to build it myself. Thank you for developing the great plugin.