0 Replies Latest reply on May 6, 2016 1:37 PM by ebrandt2016

    Server 14 migration / ODBC External Data Source Issue

    ebrandt2016

      Hello,

       

      I have been using filemaker since 1993, and have come to the end of my options. Here is my scenario.

       

      Solution Info

      I have a FMP 14 client solution running on windows 7

      • Several FM Tables
        • The rest are support tables
      • 2 External Data Souces set up to Access data from 2 different MSSQL Servers (2008 R2).

       

      When I created the original Filemaker file I created the 32 / 64 User DNS connections using NT authentication. Regarding the FM External Connections to those DNS connections,  I used the Specify Name and Password authentication. I then added my TO's and everything worked great. I tested the solution running peer-to-peer , until I got the server set up.

       

      I then configured FMP Server 14 on the Development Server (Windows Server 2012 R2) (where one of my External ODBC connections are).

      After I uploaded the file to the sever, my ODBC connections did not work. After a little research:

       

      External Connection 1 (Development Server)

      I needed to create a 64 Bit System DSN on the Development Server. I was then able to see the DSN options from the External Data Source          Settings and got that connection working. I figured that since this SQL database was on the same server as I set up FM Server, there was not issues.

       

      External Connection 2 (Development Server)

      This is where my problems started. When I tried to set up the external data source connection to the Production Server (where my 1 SQL View Lives), I was not able to connect.

       

      • SYSTEM DSN
        • I started with the System DSN on the Development Server, connections to the sever are successful. I could view the ODBC data via excel, access..etc. So I know the DSN connection to the production server from the development server work fine.
        • I tried every variation of authentication, still no dice.

       

      • EXTERNAL DATA SOURCE CONFIG
        • I am able to see the system DSN set up on the Development / FMP  server.
          • Tried every variation of authentication.

       

      I am prompted for the username and password and they do not work when entered. All of the Filemaker security permissions are set correctly and as I said. It all worked fine when I was connection to the other servers via FM Client prior to uploading to the server.

       

      I have been researching via google for 3 days and have tried everything.

       

      LINKED SERVER APPROACH

                  In SQL there is an option to create a linked server, so I added the production server to my development server with SQL Management Studio. I was able to add the view I needed from the production server to my development server and access through my External Data Source to the development server.

       

      I was happy just to get a workaround going as I moved on to other things and would revisit later. As I added a TO to my graph using the view, I got the unique key message telling me I did not have a Primary Key and to select one of the fields.

       

      Since I had this issue initially I added one via my select statement when creating the view. It’s just a row number count  and the last number matches the number of records in the found set . So I know there are not any non-unique values in the field I called PK (kinda funny).

       

      No matter how many fields I select I still get the message staying that I need to select a field with unique values.

       

      I AM AT MY WITS END ……for something that should have taken a few minutes. I am not beyond missing something but I have really tried every combination between the External Data Source settings / authentication and the System DNS setting / authentication.

       

      Please help!!!!!