Have you investigated if you are dealing with a 2008 64 bit driver issue? You can still manage DSN 32 bit on the server but you must launch it via a command line.
This is the manager for the 32 bit version of the ODBC manager in windows 7 and 2008. Make sure you are setting up th eodbc driver through this version as FM doesnt not with with the 64 bit version.
Second issue, I tested this with FMPA 12, SQL Server Express 2012 and the correct 32 bit driver. I could connect but then I could not actually see any tables other than system tables. S0, I spent an hour or so playing with permissions. I ended up setting up a user account for access from FM to SQL via odbc, went to the SQL server administration for that database by right clicking the database, selecting permissions and selecting the user I created with the following script:
CREATE LOGIN [new username] WITH PASSWORD=N'New Password',
DEFAULT_DATABASE=[YourDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [new username] FOR LOGIN [new username] WITH DEFAULT_SCHEMA=[dbo]
I edited this user permission, then edited the server permissions to allow this user access.
Works just fine now.
I remember dealing with 64-bit driver issues on Windows Server 2003, but the issue then was that the DSN simply wouldn't show up in FMP on the "Select ODBC Data Source Screen." Even though it was configured on the server it just wouldn't show up there unless I used the 32-bit version. That doesn't seem to be the issue now, and FM does show compatibility with the 64-bit driver according to the recommended driver. That said, the driver they recommend (5.1.7) is full of known bugs that were fixed in later versions, so I went with 5.1.3 which is working all the way up to the point I mentioned.
I guess I'll try the 32-bit method and see if that helps. Thanks for the feedback.
Seems like a lot of extra work I've never had to deal with in the past setting up this same stuff, but I guess it's worth a shot if I can't get it working otherwise. Thanks for the info.
It is. Previously never had to do any of this, but now I can't get it to work in 12 otherwise.
One of the first things I do when I have problems in FM via ODBC is that I try to access the SQL file via another program like Excel or a SQL viewer. This is good for testing if it is a driver problem or basically a non-FM problem. If everything does work in the other program, then you know it is a FM specific problem.
I actually just did that and came back to update when I saw your response.
The MySQL database is hosted with Network Solutions (yuck!) I noticed that I was unable to connect to this database using Navicat or MySQL Workbench, which is typical. In most cases, though, I'm able to add a user with access for a specific IP address and then that opens up the ability to use 3rd party tools like that. Of course, this generally needs to be done for the IP address of a FM server, too.
Well, NetSol doesn't give me any sort of way to do that within their control panel, and then I got word from their support that they don't enable "view all databases" so you have to make sure your tool doesn't require that, which apparently Navicat does. When setting up the DSN on Windows, I noticed that the test did work successfully, however, the drop own that should show you the databases on the MySQL server was coming up blank.
I setup a MySQL database on my own test server, added the remote user for the FM IP address, and then configured a DSN on the Windows server for this database. Now in FMP I can connect, everything is nice and fast, and works perfectly.
So, looks like I just need to ditch NetSol, which was my plan anyway, and I'll be good. I appreciate the feedback!