"with the Filemaker Pro ODBC driver"
You're not trying to use the ODBC driver that is used to connect to FileMaker via ODBC are you?
FileMaker is fairly picky about what drivers are supported. You need to create a system DSN with the ODBC manager, then filemaker uses that DSN to connect to the ODBC data source.
This are the settings we use.
1. Privilege set in the Filemaker solution with ODBC access rights.
2. Enable checkbox odbc/Jdbc on Filemaker Server.
3. Filemaker ODBC connection on the SQL server.
See also PDF
ODBC settings Filemaker.pdf 8.5 MB
Those are settings to connect TO filemaker from another program.
He is trying to connect to SQL server FROM filemaker.
I'm not completely sure what you mean. I am using the ODBC Manager and have tried connecting to my DB from both the Actual SQL Server driver and the driver I found inside the extras folder of the Filemaker Pro dmg. I found the Actual SQL Server driver from one of Filemaker Pro's SQL connection pdfs so I assume it is the correct one. The error occurs on the final step of the driver setup when I click test and input my username and password. My credentials are always rejected.
and the driver I found inside the extras folder of the Filemaker Pro dmg
This is the driver you use from other programs to connect TO filemaker via ODBC, it does not work the opposite direction ( to connect FROM filemaker to another data source)
Let's start at the beginning. What version of MS SQL is running on the azure instance you are trying to connect to? The chart in the link I provided above tells you IF it is supported, and what driver to use.
I'd assume you are using a mac based on your comments, so the chart says:
For MS SQL Server 2008 R2
MS SQL Server 2012
MS SQL Server 2012 SP1 on Mac OS X use:
Actual Technologies, MS SQL Server version 4.0.0
Only those three versions of MS SQL are supported (This is for filemaker 15, you also did not specify what version you are using). I am not sure that Azure actually uses any of those three. If not, you may not be able to connect this way.
The Actual driver is the correct driver to be using. AFTER you install the driver, and install the ODBC manager, you need to:
1) Open ODBC Manager
2) Go to "System DSN" tab
3) Click "Add"
4) Choose "Actual SQL Server" from the choose a driver list and click ok
You'll then complete the rest of the wizard. If at that point you're still getting authentication errors, then you want to make sure that your MS SQL user has access via the IP address you are using (should be your WAN IP).
After researching a bit more, it appears that there is no such thing as an ODBC driver for OSX to connect to azure MS SQL services.
You're connecting with node.js, which is using an FreeTDS, and not a prefabricated ODBC driver. Inline with how others connect to it.
The stackoverflow article above gives a few options for setting up DSNs that connect to azure SQL servers. Once you've setup a DSN that can connect successfully, then you can probably work on the filemaker portion.
Unfortunately, I doubt you'll be able to use ESS inside of filemaker (using a SQL table like a filemaker table). The most you'll likely be able to do is import data from ODBC into filemaker, or execute SQL using the Execute SQL script step.
Alternatively, you could setup a REST API fairly easily using node.js connecting to your SQL data source that way, then connect to the REST API to push/pull/change data from filemaker, rather than doing it through ODBC.
Thanks for the detailed response although I am slightly confused. On the Actual Technologies link you posted it says the Actual SQL Server is compatible with Microsoft Azure SQL Database. Also, I am fairly confident my database is MS a SQL Server 2008 R2 version. If I still can't connect, I also have a windows virtual machine on my computer. If I use windows, is there a driver I could use that would give me all of the typical features or would I still have to resort to some of the work arounds you gave at the end of your response. Thanks for all the help.
I've never used the actual tech drivers to connect to a MS SQL server, you may want to connect them for support to see if they can assist. They are a 3rd party, not operated by FileMaker.