Filemaker Pro, ODBC Manager and Mac OS X 10.8: Configuring DSN and accessing MSSQL
Managing database records is essential for most websites. There are a number of ways to manage databases on the Mac OS, one of the most powerful being the use Filemaker Pro. In order to set up Filemaker Pro on Mac OS X (10.6, 10.7 or 10.8) and connect to a MSSQL (Microsoft Server SQL) database, the following is needed:
- Filemaker Pro ($299-$499)
- ODBC Manager (Free)
- ODBC Administrator (Free)
- MSSQL Driver – Actual SQL Server ($34.95), for example
Here’s some steps for getting this all set up:
Download and Install SQL Drivers
The SQL drivers are third party drivers that allow your computer to connect to the database of choice. MSSQL is a Microsoft language and there are a few makers of SQL drivers. I used Actual SQL Server Drivers. The package can be downloaded for free, but to process more than 3 records, you’ll need to purchase the license from Actual Technologies. Download the package and run the installer.
Download and Install ODBC Manager and Administrator
Beginning in Mac OS X 10.6, ODBC Manager and ODBC Administrator was not included with the initial operating system install and needs to be downloaded and installed separately. Download and install ODBC Manager and ODBC Administrator.
Add DSN Server to ODBC Administrator
Open the ODBC Administrator, which should have been installed in your Applications > Utilities folder, and check that the driver was installed correctly. You’ll know they were installed correctly if they show up in the drivers tab. If they were not, attempt reinstalling them.
Visit the User DSN tab and click “Add…” (You may need to click the lock to allow changes) then select the driver that you just installed. Follow the prompts to set up the DSN. The name and description are arbitrary, so give a name or description as you see fit. The SQL server is important and can be found at your database hosting provider; it may look something like this: “mssql.yourdatabasehost.com”
Proceed through the prompts and click “Test” on the last screen if you wish to verify your settings. A properly connected DSN will provide a message stating that the settings were successful.
Your done with that!
Connect Filemaker Pro
I’m going to assume that Filemaker Pro is already installed on your Mac. If not, there are many tutorials on the subject.
Open Filemaker Pro and create a new database or import an old database. Go to File > Manage > External Data Sources. Click “New”, create a name of your choice and select “ODBC” as the type. The authentication methed is up to you, but I prefer to specify my user name and password. Nothing more is needed. Save the settings by clicking “OK”.
Go File > File Options, select “Log in using” and add enter your database Username and Password, which needs to be set up in your web host (if it’s not already).
Go to File > Sharing > ODBC/JDBC and make sure that ODBC/JDBC sharing is turned on.
That’s it!
Those settings, if completely successfully, should allow you to access your external MSSQL database from Filemaker Pro.
If you’re having troubles, let me know. I’ll do my best to help or point you in the right direction!
