7 Replies Latest reply on Nov 25, 2013 1:17 PM by angelleye

    Problems with External SQL Sources..??

    angelleye

      We used to be on Windows Server 2003 with FMS11. I have a MySQL database on our web server that we hooked up via ESS in FileMaker and everything was working great. The server was upgraded to Windows Server 2008 and FMS12, but for some reason I'm not having any luck with ESS.

       

      On the Windows Server, I've installed the MySQL ODBC Connector and I've successfully created a DSN.

       

      mysql-odbc-server.jpg

       

      Then when I'm on FMP connected remotely to the database on FMS and I go to File -> External Data Sources, and then I click on the existing MySQL datasource I can confirm that FM is indeed seeing the MySQL datasource.

       

      dsn.jpg

       

      Then here, while I've blurred this out for public posting, I can confirm that the un/pw included here in FM match what I used to setup the DSN in Windows. It's also the same thing I'm using in all of my web scripts, of course.

       

      unpw.jpg

       

      At this point everything looks great. So then I go into File -> Manage -> Database, switch to the tables, and I can see the MySQL tables showing up there, but it's not seeing the number of records or anything.

       

      manage-db.jpg


      At this point if I click to open the table so that I can use the Sync button, I get the lovely coffee cup and "Not Responding" for about 5 min on FMP until finally the window does open so that I can see all of the fields, but it's not seeing any of the data types.

       

      data-types.jpg

       

      If I click the Sync button I get this.

       

      fm-odbc-error.jpg

      The blurred out part there is just username@ipaddress, and again, I've confirmed the credentials I'm using in the FM ESS setup are the same as I'm using in the DSN on Windows as well as in my web scripts.

       

      I'm not sure how to proceed at this point, and I would appreciate information I can get on how to resolve this matter. Thanks!

       

       

       

       

       


       


        • 1. Re: Problems with External SQL Sources..??
          Diver

          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.

          • 2. Re: Problems with External SQL Sources..??
            Vyke

            C:\Windows\SysWOW64\odbcad32.exe

             

            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:

            USE [master]
            GO
            CREATE LOGIN [new username] WITH PASSWORD=N'New Password',
                             DEFAULT_DATABASE
            =[YourDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
            GO
            USE [YourDB]
            GO
            CREATE USER [new username] FOR LOGIN [new username] WITH DEFAULT_SCHEMA=[dbo]
            GO

             

            I edited this user permission, then edited the server permissions to allow this user access.

             

            Works just fine now.

            • 3. Re: Problems with External SQL Sources..??
              angelleye

              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.

              • 4. Re: Problems with External SQL Sources..??
                angelleye

                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.

                • 5. Re: Problems with External SQL Sources..??
                  Vyke

                  It is. Previously never had to do any of this, but now I can't get it to work in 12 otherwise.

                  • 6. Re: Problems with External SQL Sources..??
                    taylorsharpe

                    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.

                    • 7. Re: Problems with External SQL Sources..??
                      angelleye

                      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!