A longtime enterprise client recently asked us to integrate a FileMaker Server with an Oracle server. No problem, right? This task requires some development to optimize the database schema and script the data sync, but using FileMaker and an ODBC driver to connect to Oracle is straightforward.
However, this Oracle server—as does Oracle Cloud—required something called “TCPS”, which left us scratching our heads.
With some research, and a little help, we not only figured out “TCPS”, but also set up the FileMaker-to-Oracle connection. As a bonus, we enjoyed working with Actual Technologies, who provide ODBC drivers for Oracle and other third-party databases, for use with FileMaker.
TCPS
“What is ‘TCPS’?”, I asked myself, perhaps not in those exact words. Even after seventeen years as a CISSP, I had never run across the term. I was pretty sure it had nothing to do with Talbot County Public Schools or the Tennessee Clinical Placement System, but digging a little deeper I discovered TCPS is an Oracle protocol to enhance security by running TCP/IP with SSL/TLS. Okay…
This post will not look into the OSI model, the TCP/IP model, or SSL/TLS and assumes basic experience configuring FileMaker Server, installing Oracle clients, and setting up ODBC DSNs. We will, however, cover the basics of supporting TCPS with FileMaker Server. Please note your specific Oracle server may require different values, additional parameters, or other modifications, but this guide should get you started and on the right path in partnership with your Oracle administrator.
0) First, you must install the Actual Oracle ODBC driver version 5.0.9 or later.
http://www.actualtech.com/download.php
An extra special thank you to Jonathan Monroe and our friends at ActualTech for the super quick turnaround in developing and releasing support for TCPS in the latest driver update.
1) Manually edit the odbcinst.ini file:
/Library/ODBC/odbcinst.ini
Add the following line in the [Actual Oracle] section, on a new line between Setup and LicenseKey.
TNS_ADMIN = "/Library/FileMaker Server/oracle/"
See attached example file odbcinst.ini.
2) The ActualTech Oracle driver allows use of a string, in the format of a tnsnames.ora entry excluding line feeds and spaces, in place of the server address. When configuring the DSN through the user interface, enter the following string in the Server field:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<<INSERT SERVER ADDRESS HERE>>)(PORT=<<INSERT PORT NUMBER HERE>>))(CONNECT_DATA=(SERVICE_NAME=<<INSERT SERVICE NAME HERE>>)))
You must replace the server address, port number, and service name—including the angle brackets—with those given to you by your Oracle administrator.
See attached example file odbc.ini.
3) Ensure permissions on the ODBC/ directory and all files within:
sudo chown -R root:admin /Library/ODBC/ ; sudo chmod -R u=rwx,go=rx /Library/ODBC/ ; sudo chmod -R u=rw,go=r /Library/ODBC/*.ini
4) Copy the attached sqlnet.ora file to:
/Library/FileMaker Server/oracle/sqlnet.ora
You will need to create the oracle/ directory.
5) Your Oracle administrator should give you an Oracle “wallet” containing, for example, cwallet.sso and ewallet.p12. Copy the wallet files to:
/Library/FileMaker Server/oracle/wallet/
6) Ensure permissions on the oracle/ directory and all folders and files within:
sudo chown -R fmserver:fmsadmin /Library/FileMaker\ Server/oracle/ ; sudo chmod -R u=rwx,go=rx /Library/FileMaker\ Server/oracle/ ; sudo chmod -R u=rw,go=r /Library/FileMaker\ Server/oracle/sqlnet.ora ; sudo chmod -R u=rw,go=r /Library/FileMaker\ Server/oracle/wallet/*.*
7) At this point you should be able to quit and reopen ODBC Manager to test interactive connectivity.
8) To enable connectivity via FileMaker Server, you must restart the fmserverd process to reload drivers and clear caches. You may do so in FileMaker Server Admin Console by clicking Stop Database Server followed by Start Database Server—a reboot is not required.
9) Now connect to your hosted database and create an external data source with the DSN you just tested.
10) Finally, use that external data source for ESS, Import, or Execute SQL. Happy coding!
Attached are example configuration files for odbcinst.ini, odbc.ini, and sqlnet.ora. Note these are just samples to show what the files look like and to use as reference. At a minimum you will need to change the hostname, port number, and service name, but your Oracle environment may require additional parameters, such as SSL_SERVER_CERT_DN. Consult your Oracle administrator.