1 of 1 people found this helpful
I found the answer to my main question is on page 25 of this document:
Managing ODBC Connections
In the normal course of things, connections to specified ODBC sources are made automatically when a user needs to work with ESS-based data. It’s possible, though, that sometimes a connection to an ODBC source won’t be made as expected, or that a developer will want to deliberately disconnect from an ODBC source.
In FileMaker Pro 9 or later, the File Open and File Close script steps have been extended to allow them to work with ODBC data sources. File Open, when called on an ODBC data source, will attempt to reconnect to the data source if there is not a currently open connection. File Close will attempt to close the connection to the data source. After either of these steps, the data source will behave as it normally would: if a connection has been opened, all ESS functionality will be available, and if the connection has been closed, the next request for data from that source will trigger a new connection attempt.
Or these days known as Open File and Close File.
Closing the file works, because the ESS "file" will reopen on the next attempt to refer to a shadow table.
It is a good idea to avoid closing an ESS connection when the current layout is based on that data source. It is best to find a way to navigate to a different layout first, or the user may see some odd behavior, though the operation should succeed.
After reading that Best Practices: External SQL Data Sources, I think I see the answer to my other question:
Why isn't this ESS connection already open on the server for all subsequent client sessions after the first client refers to a shadow table?
It looks like ESS is assuming that each client has to provide credentials to connect to the ESS. That's a safe assumption, but I wish I could override it so that authenticating to my FileMaker solution would be all that is required to use a more persistent pre-authenticated ESS connection on the server.