KB88835349: Automation Engine - How to configure an ODBC connection on Automation Engine to a SQL database on a remote server

Description

Automation Engine can be set up to look up SmartName values from an external SQL database. There are several cases where you need to reconfigure the existing ODBC 3- bit connection or create a new ODBC 32-bit connection to the database:

• When the host name of the existing remote SQL server changes.
• When the name of the database changes.
• When you move the database to a new server.
• When you move Automation Engine to a new server.

If the connection is not configured correctly, the SmartName look up will fail. When you open the SmartNames view in Automation Engine Pilot, you can see an error message similar to this one:

"Error code = 80004005 (U) [Microsoft]/SQL Native Client|Named Pipes Provider: Could not open a connection to SQL Server [53]. (Source=Microsoft OLE DB Provider for ODBC Drivers)".

Procedure

You need to perform the steps given below on your Automation Engine (Master) Server:

1. Open the ODBC Data Source Administrator (32-bit) which is found in the path C:/Windows/SysWOW64/odbcad32. On Windows Server 2012, you can access it by searching for the app "ODBC Data Sources (32-bit)".

   Do not use the 64-bit version as Automation Engine 12.2.0 can only connect to the 32-bit version!

   
   

2. Go to the System DSN tab and click Add.
   
3. Select the SQL Server value and click Finish (SQL Server Native Client 10.0 may work as well).
   
4. In the Name: field enter the same name as the host name of the remote SQL server. The Description: field can be left blank if you like. And in the Server: field click the drop-down arrow and choose the host name of the remote SQL server.
   
5. Click Next. Depending on your database's configuration either:
   1. Select With Integrated Windows Authentication (when you don't need to specify a user name and password to connect to the database).
   2. Or select With SQL Server authentication using a login ID and password entered by the user. In this case, you need to obtain a valid user name and login from the administrator who set up the database (this is the most common choice).
   3. Enable the Connect to SQL Server to obtain default settings for the additional configuration options checkbox.
      
6. Click Next. Click the drop-down arrow of the Change the default database to: field and choose any of the available databases.
   
7. Click Next. Keep the default settings for this screen (only enable Perform translation for character data).
   
8. Click Finish. Then click Test Data Source....
   
9. It should return the message: TEST COMPLETED SUCCESSFULLY!.
   
10. Extra data source becomes visible in the System DSN tab.
    
11. Depending on the values saved in step 5 (use integrated Windows Authentication or specify a user name and password), you need to set up the configuration for each SmartNames which looks up a value in the database.
12. When you use the integrated Windows Authentication, the settings look like this when the database name is sqlb00:
13. When you need to specify a user name and password, it needs to look like this:
    
14. Esko support has seen cases where the configuration of the database authentication was changed during maintenance of the database server. Where it was then needed to both:
    1. Reconfigure the ODBC connection or create a new connection using user name and password instead of the integrated Windows Authentication
    2. Add the UID and password in each existing database look up SmartName's configuration as specified in the above screenshot.