Spring Boot Maven Project configuration with MS SQL Database integratedSecurity=true

Question

Is it possible to pack the required DLL dependencies to use MSSQL integratedSecurity option without having to put DLL files in the target machines JVMs folders or to automate this process with Maven?

My pom.xml JDBC dependencies:

<properties>
    ...
    <spring-boot.version>2.4.4</spring-boot.version>
    <maven.compiler.source>11</maven.compiler.source>
    <maven.compiler.target>11</maven.compiler.target>
    <version.java>11</version.java>
    ...
</properties>
...
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>9.2.1.jre11</version>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc_auth</artifactId>
    <version>9.2.1.x64</version>
    <type>dll</type>
</dependency>

My application.yml configuration:

spring.datasource:
  url: jdbc:sqlserver://SOMESERVER;databaseName=SOMEDATABASE;integratedSecurity=true
  driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

My error:

2021-04-01 11:20:17,133 ERROR [main] com.zaxxer.hikari.pool.HikariPool: HikariPool-1 - Exception during pool initialization.
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:2a5498f2-4672-42b4-87a6-4cb2087ee382
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3206)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:72)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4015)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4004)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2768)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2418)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2265)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1291)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:881)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:560)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:223)
    at com.sun.proxy.$Proxy98.getMetaData(Unknown Source)
    at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.initDatabaseType(ProcessEngineConfigurationImpl.java:1556)
    at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.initDataSource(ProcessEngineConfigurationImpl.java:1511)
    at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:1001)
    at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:972)
    at org.camunda.bpm.engine.spring.SpringTransactionsProcessEngineConfiguration.buildProcessEngine(SpringTransactionsProcessEngineConfiguration.java:67)
    at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:55)
    at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:34)
    at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:169)
    at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:101)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1884)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getObjectForBeanInstance(AbstractAutowireCapableBeanFactory.java:1266)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:345)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1380)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1300)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:657)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:640)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:119)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1413)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1380)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1300)
    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:887)
    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:791)
    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:229)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1354)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1204)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:564)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:769)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:326)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1313)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1302)
    at com.morneaushepell.camunda.BootApp.main(BootApp.java:13)
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-9.2.1.x64 in java.library.path: [C:\Program Files\Java\jdk-11.0.10\bin, C:\Windows\Sun\Java\bin, C:\Windows\system32, C:\Windows, C:\Program Files\Common Files\Oracle\Java\javapath, C:\Windows\system32, C:\Windows, C:\Windows\System32\Wbem, C:\Windows\System32\WindowsPowerShell\v1.0\, C:\Program Files\Microsoft\Web Platform Installer\, C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v1.0\, C:\Program Files\Git\cmd, .]
    at java.base/java.lang.ClassLoader.loadLibrary(ClassLoader.java:2660)
    at java.base/java.lang.Runtime.loadLibrary0(Runtime.java:827)
    at java.base/java.lang.System.loadLibrary(System.java:1871)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<clinit>(AuthenticationJNI.java:51)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4014)
    ... 71 common frames omitted

Is it possible to pack the required DLL into the project using Maven and a Spring Boot project?

Answer 1

Probably you can obtain the required result by combining several Maven plugins.

On one hand, you can use the copy dependencies mojo to copy the dynamic library to a certain location. Consider for instance:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-dependency-plugin</artifactId>
    <version>3.1.2</version>
    <executions>
        <execution>
            <id>copy-dependencies</id>
            <phase>prepare-package</phase>
            <goals>
                <goal>copy-dependencies</goal>
            </goals>
            <configuration>
                <outputDirectory>${project.build.directory}/dll</outputDirectory>
                <includeArtifactIds>mssql-jdbc_auth</includeArtifactIds>
            </configuration>
        </execution>
    </executions>
</plugin>

Now, the library should be available so it can be loaded by com.microsoft.sqlserver.jdbc.AuthenticationJNI and System.loadLibrary.

Java will look for native libraries in the system library path.

As you can see in the stack trace, the system library path is defined by the system property java.library.path - typically it is well suited if you have only a JNI library - and different environment properties, dependent on the operating system, PATH in Windows and LD_LIBRARY_PATH in Linux and Unix systems - relevant if you have dependent libraries, for example.

The way in which that properties an environment variables can be defined depends on how you are running your application.

One first, less portable, approach will be to use the above-mentioned copy-dependencies mojo to output the dependencies to a common, standard, directory usually included in the PATH or LD_LIBRARY_PATH variables.

If you are using the Spring Boot Maven plugin, probably you have several ways to do this.

jvmArguments

<plugin>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-maven-plugin</artifactId>
  <configuration>
    <jvmArguments>
      -Djava.library.path=${project.build.directory}/dll
    </jvmArguments>
  </configuration>
</plugin>

systemPropertyVariables

<plugin>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-maven-plugin</artifactId>
  <configuration>
    <systemPropertyVariables>
      <java.library.path>${project.build.directory}/dll</java.library.path>
    </systemPropertyVariables>
  </configuration>
</plugin>

environmentVariables

<plugin>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-maven-plugin</artifactId>
  <configuration>
    <environmentVariables>
      <PATH>${project.build.directory}/dll</PATH>
    </environmentVariables>
  </configuration>
</plugin>

All these features require a forked process.

Similar features exists for the Maven exec plugin as well.

As you indicated in your comment, the suggested approaches are more focused probably on a development, not final deployment perspective.

The way in which the problem can be solved from a final deployment perspective will depend on the actual mechanism that you are using to run the application.

If you were running the application on Linux, or at least you have a suitable native library to perform the integrated security authentication, I would recommend you to pack the application in a docker container and perform the necessary tweaks in the JVM configuration to make it work.

Unfortunately, there is no native library version of mssql-jdbc_auth for Linux and, although you have the ability to configure Kerberos for a similar purpose, you will run into a problem probably greater than the one you are trying to solve.

You can try packing the dependency as a resource in your jar/war archive, copy it to a writable location - the folder identified by java.io.tempdir usually will fit these requirements - on application startup, and then load then using System.load - not System.loadLibrary - passing as argument the full path and name including extension of the copied dll: honestly I never tested it before, but as far as a native library is loaded only once in the JVM and second load tries will have no effect perhaps it will allow you to preload the library before the MS SQL Server driver itself.

Probably this approach will only be suitable if you run your app as a standalone Spring Boot application: if you run your application in a traditional container, Tomcat, etcetera, as a WAR file, the fact that a shared library can only be loaded once maybe can cause you problems when redeploying the application.

For traditional container deployments, in my opinion, the best option is use the shared library directory they usually provide and place there your dll prior application deployment.