8 Replies Latest reply on May 31, 2016 11:11 AM by mikebeargie

    Filemaker Pro MS SQL Server ODBC Driver Connection Failure

    jeg263

      down votefavori

       

       

      I am trying to connect to my MS SQL Server database with the Filemaker Pro ODBC driver. However, whenever I try to connect to my database it sends either an error that it can't connect to the datasource or an error that the username or password is incorrect. I know the credentials I am using are correct because I can connect to my Jetbrains IDE and to the mssql library in node.js with the same credentials. Does anyone know what the problem could be? My database is running on Azure. Do any setting need to be changed in the driver based on using Azure that could cause the problem?

        • 1. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
          mikebeargie

          "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 Software Deployment, Business Software Solution - FileMaker

           

          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.

          • 2. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
            karina

            Hi,

             

            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

            • 3. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
              mikebeargie

              Those are settings to connect TO filemaker from another program.

               

              He is trying to connect to SQL server FROM filemaker.

              • 5. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
                jeg263

                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.

                • 6. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
                  mikebeargie
                  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.

                   

                  Software downloads: Mac ODBC connectivity by Actual Technologies

                  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.

                  http://stackoverflow.com/questions/3283897/using-azure-sql-services-from-a-mac

                   

                  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.

                  • 7. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
                    jeg263

                    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. Screen Shot 2016-05-31 at 12.50.43 PM.png

                    • 8. Re: Filemaker Pro MS SQL Server ODBC Driver Connection Failure
                      mikebeargie

                      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.