Hi bellenaessens ,
have you tested your DSN outside of FileMaker ? I mean tested that it connects to you MySQL database (the is usually a Connection Test button) ?
MySQL security is very high (at least on Linux). When you want to connect from a remote computer, you have to setup your MySQL Server to accept incoming connections, from everybody or specific adresses.
Thanks for your reply. No, in ODBC Manager I can find no sign of a "test connection" button of any sort. I would indeed expect to find this in such an environment (for obvious reasons), but nothing.
I have no idea how else to try and test the connection. I am trying to see also in phpMyAdmin if there are any security settings I should change, but cannot find any that could cause the problem.
1 of 1 people found this helpful
I searched about ODBC Manager for the Mac, it looks like testing the DSN depends on the driver supplier.
Installing and Configuring the ODBC Driver for Mac OS — Snowflake Documentation Here they use iODBC
Regarding phpMyAdmin, select the database, click on the Privileges tab:
I have on user set for web_alarm that can access this database. For global, I have these permission set:
Hope that helps.
1 of 1 people found this helpful
When you're re-re-re- presented with login credentials, it can often mean you have the wrong password, or it's case sensitive, or something like that.
Another issue is that the server may not allow remote connections outside the machine itself. That is, MySQL is intended to be used as a service within the server, but not directly accessed from outside the server. It looks like you have the correct external user defined using "%", so I'm not sure about this being the cause.
Things to try...
If you download the FREE MySQL JDBC driver (not ODBC in this case, but JDBC) you could try a tool like RazorSQL for an interactive-outside-FMP test. See if you get any useful connect error messages when you try to connect. You're not going to do SQL with Razor in this test, just try to set up a connection to MySQL and see if you can get to the database and, if not, what errors you get.
I would also (if it were me) write a short Java program that would try to get a connection to the database and see what actual error information I get. If you can code a little, there are lots of examples on the Internet how to write JDBC programs. They are actually quite simple, but in this case all you want to try to do is connect. Using RazorSQL will do more or less the same thing, but I'm not sure about Razor's connection error reporting.
HOPE THIS HELPS.
It gets even stranger: there is no privileges tab in phpMyAdmin. I knowfrom earlier setups of mySQL that that's where to adapt the security settings, but it's not there. (This is a set-up I didn't initiate myself, I think it was done by the hosting provider, and it shows only 1 user directly under "database server".)
I'm now contacting support from our hosting provider to see if it could be a security issue.
Thank you for your suggestions, fmpdude! I have tried razorsql and the connection fails. Since I can't access the privileges (the printscreens above are from someone else trying to help me), I have contacted support from our hosting provider, who I think set up the MySQL server in the first place.
Hmmm, in that case, I bet it's that you need a "%" user so you can access the server outside the server itself (as in, remotely).
Contacting the support team at your hosting provider is the correct way to proceed at this point. You've clearly given it the "college try" (and then some)!
Thanks for your update.
Got word from support: since it's a shared hosting package, they don't allow direct access to the mysql-database from the outside. They suggested SSH tunneling as an alternative. Will give that another try now, since I couldn't get it to work yesterday.
That's certainly understandable from their point of view but completely unsatisfactory from your point of view (as in, technical needs).
Therefore, if it's within your power, change your hosting to a dedicated "VPS" server. With a VPS you still don't have the entire server, but you have your own Linux "image" and can do whatever you need to. The hosting company could do all the work for you to switch you to the new hosting configuration.
Linux VPS hosting is not expensive. Often about $50/month, depending on memory, disk space, and such.
One of the best VPS providers I have researched, but not tried, is RoseHosting.
At least you know what was going on now.
I was thinking in the same direction, but not my decision to make. I have suggested changing the hosting package to customer in the past (for a number of other reasons as well), but no go so far.
Thanks for the suggestion and for staying on the thread, though! Bye!
Let us know if we can help you in the future!
Yes if you don't have control of the external sql source, then you are limited to permissions. They have put the security in there for a reason that does not help you!
At this point I typically suggest trying to get feeds (from their data) and a method to send data to them. This is sometimes allowed as they can "clean" the data (prevent SQL attacks and do other necessary housekeeping). But you have to get that information from them.
Keep us posted on the SSH tunneling!