Accessing external ODBC data sources

To host a FileMaker Pro database that works with data in an external ODBC data source, you must configure an ODBC client driver for the ODBC data source that the FileMaker Pro database accesses. For example, if the database accesses records from an Oracle database, you must configure an Oracle client driver.

Note  The ODBC client driver must be installed and configured on the primary machine in your FileMaker Server deployment.

When a FileMaker Pro client or web publishing session connects to the hosted FileMaker Pro database, FileMaker Server accesses data in the external ODBC data source on behalf of the client. Therefore, the client connected to FileMaker Server does not directly interact with the external data source, and does not need to install and configure an ODBC client driver. Only the primary machine in the FileMaker Server deployment requires the ODBC client driver.

For detailed information on designing a FileMaker Pro database that accesses external data sources, see Claris Pro and FileMaker Pro Help.

To configure an ODBC client driver and set up a DSN on FileMaker Server:

The way you interact with the data source, provide a password, and perform and display query results varies with each application's client driver. Additionally, data source configuration can vary from one ODBC client driver manufacturer to another.

Use the following procedures as a general guideline for configuring data sources (refer to the documentation that accompanies each data source application for details).

Configuring the DSN (FileMaker Server only, Windows)

  1. Open Administrative Tools:

    • Windows versions with the Start menu: On the Desktop, click Control Panel > Administrative Tools.

    • Windows versions with the Start screen: Display the charms, then click Settings. Select Show administrative tools.

  2. In Administrative Tools, open Data Sources (ODBC).

  3. In the ODBC Data Source Administrator, click the System DSN tab.

    If your data source appears in the list, the driver is already configured. You can skip the remaining steps and begin accessing ODBC data, or choose your data source and click Configure to modify how you'll interact with the data source.

    A System DSN is available to all users who log in to your computer. A User DSN is available only when you log in.

    Important  Only System DSNs are supported for FileMaker Pro databases that work with ODBC tables in the FileMaker Pro relationships graph.

  4. Click Add to configure a driver for your data source.

    A list displays all ODBC client drivers installed on your computer.

    FileMaker Server supports several third-party drivers. For information on supported drivers, see Support.

    To install a new driver, use the driver's installation program.

  5. Choose the driver for the data source that the hosted FileMaker Pro database is designed to access, then click Finish.

  6. For Data Source Name, enter the identical DSN that the FileMaker Pro database is designed to connect to.

    Important  The name you enter here must be exactly the same as the DSN that the database was designed to use in FileMaker Pro. For each DSN used by each database you host, you must configure a matching DSN on the FileMaker Server primary machine.

    Many drivers also offer options to customize how you access a data source, such as identifying a particular data source file.

Configuring the DSN (FileMaker Server only, macOS)

These instructions assume you have installed the ODBC Manager from Actual Technologies, available at www.odbcmanager.net, which is a freeware product not supported by FileMaker software.

  1. Launch the ODBC Manager utility. (The ODBC Manager is installed in the Utilities folder in the Applications folder.)

  2. In the ODBC Manager, click the System DSN tab.

    If your data source appears in the list, the driver is already configured. You can skip the remaining steps and begin accessing ODBC data, or choose your data source and click Configure to modify how you'll interact with the data source.

    A System DSN (Data Source Name) is available to all users who log in to your computer. A User DSN is available only when you log in.

    Important  Only System DSNs are supported for FileMaker Pro databases that work with ODBC tables in the FileMaker Pro relationships graph.

  3. Click Add to configure a driver for your data source.

    A list displays all ODBC client drivers installed on your computer.

    FileMaker Server supports several third-party drivers. For information on supported drivers, see Support.

    To install a new driver, use the driver's installation program.

  4. Choose the driver for the data source that the hosted FileMaker Pro database is designed to access, then click Finish.

    A setup dialog box appears.

  5. For Data Source Name, enter the identical Data Source Name that the FileMaker Pro database is designed to connect to.

    Important  The Data Source Name you enter here must be exactly the same as the Data Source Name that the database was designed to use in FileMaker Pro. So for each Data Source Name used by each database you host, you must configure a matching Data Source Name on the FileMaker Server primary machine.

    Many drivers also offer options to customize how you access a data source, such as identifying a particular data source file.

Configuring the DSN (Linux)

To configure data sources for Linux, create (if necessary) and edit the ODBC configuration file (odbc.ini) with values for the data sources with which you want to connect. For Oracle data sources, you must also update the Oracle configuration file (tnsnames.ora) with values that match in both files.

FileMaker Server for Linux supports the following data sources:

  • MySQL

  • SQL Server

  • Oracle

  1. Install a driver supported by FileMaker. For information on supported drivers, see Support or go to the Knowledge Base and search for the article "Which ODBC data sources/drivers are supported with External SQL Data Sources?".

  2. If the ODBC configuration file (odbc.ini) doesn't exist, create it by entering:

    sudo touch /etc/odbc.ini

  3. Update the values in the ODBC configuration file (odbc.ini) located in /etc folder. For example:

    Oracle:

    Copy 
    [Oracle]
    Driver = OracleODBC
    ServerName = oradb
    Database = <name>
    Server = <host>
    Port = 1521
    client_charset = 'UTF-8'

    MySQL:

    Copy 
    [MySQL]
    Driver = MySQL
    Database = <name>
    Server = <host>
    Port = 3306
    client_charset = 'UTF-8'

    SQL Server:

    Copy 
    [SQL Server]
    Driver = ODBC Driver 17 for SQL Server
    Database = <name>
    Server = <host>
    Port = 3306
    client_charset = 'UTF-8'

  4. For Oracle: Follow these steps:

    1. Edit the values in the Oracle configuration file (tnsnames.ora) file located in the /opt/FileMaker/FileMaker Server/Data/TnsAdmin/ (FileMaker Server only) or the /opt/Claris/Server/Data/TnsAdmin/ (Claris Server only) folder. For example:

      Copy 
      ORADB =
       (DESCRIPTION = 
         (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.170.1.0)(PORT = 1521))
         )
       (CONNECT_DATA =
         (SERVICE_NAME = ORADB)
       )
      )

    2. Restart FileMaker Server. Enter the command:

      • FileMaker Server only: sudo service fmshelper restart

      • Claris Server only: sudo service claris restart

      See Starting or stopping FileMaker Server components.