KB-1026 "Unable to get managed connection for <datasource>" error thrown when trying to connect to a SQL Server database

Symptoms

SQL Authentication is enabled on SQL Server yet Appian SQL Authentication fails. This can be seen in the application server log with a large stack trace, but it will contain the following important statements:

java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for jdbc/<DataSource>
...
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for jdbc/<DataSource>
...
Caused by: javax.resource.ResourceException: IJ000658: Unexpected throwable while trying to create a connection: null
...
Caused by: javax.resource.ResourceException: Could not create connection

Cause

There are three possible causes for this issue:

1. The SQL Server driver is incorrectly declared in module.xml (JBoss only).
   • The SQL Server driver is present in the <JBOSS_HOME>\modules\com\microsoft\sqlserver\jdbc\maindirectory, however it is incorrectly declared in the module.xml file, declaring the module.xml file for the wrong RDBMS.
2. Required SQL Server libraries Drivers for authentication (sqljdbc_auth.dll and sqljdbc_xa.dll) are missing (JBoss only).
   • The correct driver package was downloaded from Microsoft, however additional libraries (sqljdbc_auth.dll and sqljdbc_xa.dll) are missing, or the directory in which these files live is not correctly referenced in the PATH environment variable.
3. SQL Server Browser is not running.
   • The SQL Server rejected the request to connect on the default port. A SQL Server named instance is defined in *-ds.xml but Appian is unable to map it to a hostname and port.

Action

For Cause 1, update the module.xml to use the correct syntax for SQL Server and the correct path for the driver. Steps on how to configure module.xml can be found in Configuring Relational Databases.

For Cause 2, perform the following:

1. Add the directory containing sqljdbc_auth.dll and sqljdbc_xa.dll to the PATH environment variable.
2. Restart JBoss.
3. If the error still occurs after restarting JBoss, perform the following:
   1. Add the directory to java.library.path using custom JVM settings.
   2. Add the following line to standalone.custom.bat (located in <JBOSS_HOME>\bin), replacing <JBOSS-HOME>` with the directory of where JBoss is installed:
      

      set CUSTOM_JAVA_OPTS=-Djava.library.path="<JBOSS-HOME>/modules/com/microsoft/sqlserver/jdbc/main"

Note: Use forward slashes (/) in the above command only.

Note: If using JBoss as a Windows Service, run it as the Windows user used to log in. Also, uninstall and reinstall the JBoss Windows Service for changes to take effect.

For Cause 3, enable SQL Server Browser in SQL Server Configuration Manager under SQL Server Services to allow the named instance to resolve to a hostname and port. Do not include the port in the connection URL if <HOSTNAME>/<NAMEDINSTANCE> is used.

Workaround

If desired, integrated authentication can be turned off. This workaround should not be used for users that want to configure integrated security with SQL Server. Make the following changes in the appian-ds.xml file for this datasource to switch to SQL Server Authentication.

For a URL based connection string, add ;integratedSecurity=false (including the semicolon) at the end of the URL after the databasename property. The connection string should now look like this:

<xa-datasource-property name="URL">jdbc:sqlserver://sqlserver.example.com:1433;databaseName=MsSqlForAppian;integratedSecurity=false</xa-datasource-property>

For <xa-datasource-property> tag-based connection parameters, set the IntegratedSecurity property to false, so contents of the <xa-datasource>should now look like this:

<xa-datasource-property name="ServerName">sqlserver.example.com</xa-datasource-property>
<xa-datasource-property name="PortNumber">1433</xa-datasource-property>
<xa-datasource-property name="DatabaseName">MsSqlForAppian</xa-datasource-property>
<xa-datasource-property name="IntegratedSecurity">false</xa-datasource-property>

Affected Versions

This article applies to all versions of Appian using SQL Server as a data source and using an application server on a Windows-based operating system.