KB205229874: Automation Engine - No more connection to external SQL Servers after upgrading to v16.1 or higher

Summary

After an upgrade to Automation Engine 16.1 or higher, 'Database Query' SmartNames and the 'Interact with External Database' task no longer work.

Symptoms

'Database Query' SmartNames no longer work.

'Interact with External Database' tasks fail.

Explanation

This is because Automation Engine 16.1 and higher include a recent SQL Server driver in order to support SQL Server 2017. This driver is no longer compatible with Microsoft SQL Server 2005 and some versions of SQL Server 2008. The only solution is to use an older SQL Server driver (Microsoft JDBC 4.0 or jTDS Sourceforge).

To find out which driver works depends on the version of the SQL Server that the external database is using. We recommend to first check if the Microsoft SQL Server 4.0 is working. This will most likely be the base for the newer versions of SQL Server. If this is not working, use the older jTDS driver. Be aware that this driver is no longer developed and it supports SQL Server versions only up to SQL Server 2012.

Solution

As best practice we recommend using either the Microsoft JDBC 4.0 Driver or the jTDS Sourceforge 1.3.1.

For SQL Server 2005 (all versions), SQL 2008 (all version) or SQL 2008 R2 (without service pack or service pack 1), we recommend to try the jTDC Sourceforge 1.3.1 driver. For SQL Server 2008 R2 with later service packs (service pack 2 or 3), or SQL Server 2012 (without service packs), we recommend to try the Microsoft JDBC Driver 4.0.

From SQL Server 2012 SP1 on, the default Microsoft SQL Server driver from Automation Engine itself should still work. However, Esko cannot test all combinations of different SQL Server versions with all its different service pack or updates. 

• Microsoft JDBC 4.0 Driver 
  1. Copy the old JDBC driver (sqljdbc.jar) from bg_prog_fastserver_v160\classes or bg_prog_fastserver_v141\classes to %bg_custom_dat_v010%\dat\sql
  2. In Tools > Configure, change the type of DBMS from 'Microsoft SQL Server' to 'Other'.
  3. Specify these details:
     • Driver (JDBC): com.microsoft.sqlserver.jdbc.SQLServerDriver
     • Driver File: file://'hostname_of_ae-server'/bg_data_custom_v010/dat/sql/sqljdbc.jar
     • Database URL: jdbc:sqlserver://'hostname_of_database server'\SQL Instance name';databaseName='name of database';authentication=SqlPassword;trustServerCertificate=true;encrypt=false;user=xxxxx;password=password for xxxx
• jTDS Sourceforge 1.3.1
  
  1. Copy the old JDBC driver (jtds.jar) from bg_prog_fastserver_v160\classes or bg_prog_fastserver_v141\classes to %bg_custom_dat_v010%\dat\sql
  2. In Tools > Configure, change the type of DBMS from 'Microsoft SQL Server' to 'Other'.
  3. Specify these details:
     
     • Driver (JDBC): net.sourceforge.jtds.jdbc.Driver
     • Driver File: file://'hostname_of_ae-server'/bg_data_custom_v010/dat/sql/jtds.jar
       Database URL: jdbc:jtds:sqlserver://'hostname_of_database server';DatabaseName='name of database';instancename='SQL Instance name';user=xxxxx;password=password for xxxx