AnsweredAssumed Answered

Server 14 migration / ODBC External Data Source Issue

Question asked by ebrandt2016 on May 6, 2016

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!!!!!

Outcomes