Connect SQL macro to MS SQL database using Windows authentication

Problem statement

When connecting to MS SQL database through Windows authentication and creating a data source profile in "BobSwift SQL for confluence app", you might receive one of the followings errors:

• Native Library: sqljdbc_auth.dll already loaded in another classloader

• Unable to connect to the database using jdbc:sqlserver://<servername>:<port>;instanceName=<instancename>;database=<dbname>;integratedSecurity=true

• com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

This article helps you address these errors.

Environment

Cause

Java Virtual Machine does not allow a given JNI native library to be loaded by more than one class loader and thereby throws the mentioned error message.

Solution

The following instructions help resolve the issue by downloading and placing Microsoft JDBC drivers and sqljdbc_auth.dll in the respective mentioned locations:

1. Download the Microsoft JDBC drivers (JDBC driver 4.2 for SQL Server) to a temporary location on your confluence server https://www.microsoft.com/en-us/download/details.aspx?id=5467.

2. Run sqljdbc_<version>_<language>.exe (i.e sqljdbc_4.2.8112.200_enu.exe).

3. Enter the installation directory when prompted. We recommend that you unpack this zip file in %ProgramFiles% with the default directory: "Microsoft JDBC Driver 4.2 for SQL Server" (Ex: C:\Microsoft-JDBC-Driver4.2-for-SQL-Server\sqljdbc4.2).

4. Shutdown your Confluence application.

5. Remove existing or bundled Microsoft JDBC drivers present in Confluence installation directory <confluence-install-directory>\lib or <confluence-install-directory>\confluence\WEB-INF\lib\.

6. Copy sqljdbc42.jar driver from Step#3 (Ex: C:\Microsoft-JDBC-Driver4.2-for-SQL-Server\sqljdbc4.2\enu\jre8) and place it in:

   1. Confluence's lib folder (ex: <confluence-install-directory>\lib).

   2. Java's Install path (ex: If your JAVA home directory is C:\Program Files\ Java\ jre1.8.0_171 then copy the sqljdbc42.jar to C:\Program Files\ Java\ jre1.8.0_171\lib\ext).

7. Copy sqljdbc_auth.dll (depending on your system architecture x86 or x64; ex: C:\Microsoft JDBC Driver 4.2 for SQL Server\sqljdbc4.2\enu\ ) to:

   1. C:\Windows\System32

8. Start your Confluence application.
   Please make sure you have the similar setup in the Datasource profile connection string

   dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver dbUrl=jdbc:sqlserver://<servername>:<port>;instanceName=<instancename>;database=<dbname>;integratedSecurity=true dbJar=C:\Microsoft-JDBC-Driver4.2-for-SQL-Server\sqljdbc4.2\enu\jre8\sqljdbc42.jar dbUser=appfire\sasam dbPassword=*****

   Screenshot for configuring a data source profile:

To know more about configuring Data source profile in Confluence, refer to Configure Data Source Profiles.