ajayz

Error with a Windows Authentication ODBC Connection for Filemaker

Discussion created by ajayz on Aug 28, 2016
Latest reply on Sep 25, 2016 by ajayz

Hi,

 

We are running FileMaker version 15.0.1.119.

Currently the FileMaker application successfully pulls data from MS SQL 2008 R2 tables/views via an ODBC connection hard-coded to use a special read-only SQL Server Authentication login/user - that works, we see the SQL data in FileMaker.

We have many FileMaker screen fields, across 5 or 6 forms, most having several tabs; some fields draw their data from VIEWS, some from raw data tables via JOINs defined in the FileMaker Relationships.

 

But I need to be able to restrict Users so that they can only retrieve records they are authorised to.

So my thinking was that I could instead run the FileMaker application through a Windows Authentication ODBC/DSN connection - each User’s SQL connection would have their own windows User ID as the ‘login_name’ of their SQL connection, so where User ID filtering was needed a VIEW could pull the user’s login name from its  SQL connection and filter on that login ID.

 

So when I set up this new DSN, I have “With Integrated Windows authentication” selected (see below) - all other settings are the same as the DSN that works ok using SQL server authentication:

 

 

I input the SPN as above – the DSN is accepted as ok, and when I test the connection, it works ok!

 

In FileMaker, I press the ‘Add a table’ icon on the ‘Relationships’ tab.

I then drop down to ‘Add ODBC Data Source…’:

 

I can see my new Windows Authentication DSN in the drop-down.

 

I select it, and I get:

 

I set the screen as shown, and press ‘OK’ – I get this error…

 

I tried adding the “.xxxxxxxx.xxxxx” to give the full Domain name of the server (E.G “SQLCLUSTER02.xxxxxxx.xxxxx” – still got the same error message box re cannot access using entered Account/Password (I didn't actually enter an Account/Password – we're trying to use Windows Authentication access).

 

Firstly, is FileMaker using my FileMaker login/password through this new ODBC connection – or is it using my network (I.E Active Directory) User/password?

 

Just in case FileMaker is using my actual FileMaker Account/password to connect, I created 2 accounts for me in FileMaker, both exactly matching my network login User ID:

  • One Account Name WITHOUT the Domain prefix – E.G “Mark.Watson”.
  • One Account Name WITH the correct Domain specified – E.G “DOMAIN\Mark.Watson”.

And I gave both of those accounts exactly the same password as my actual network login.

I get the error when trying to configure the data source as both of those Accounts.

 

I did see in a FileMaker manual somewhere that to use Windows Authentication I also need to supply the SPN too.

I don't know how to create an SPN for the SQL connection (I've asked our network person about it, but haven't heard back as yet).

We run FileMaker on Windows 7 PCs; the SQL 2008 R2 database is on a Windows 2008 R2 Enterprise server cluster of 2 servers.

 

I haven't found any helpful suggestions on the web for my problem.

 

Does anyone have any suggestions?

 

And just to confirm:

If I can get this Windows Authentication working:

  • Will I see each FileMaker user’s individual FileMaker Account Name or Windows User ID on their individual SQL connections in SQL, won't I – or
  • Does Windows Authentication mean we have to create a special new Active Directory FileMaker User, and everyone has to share that 1 special Windows User?

 

As an alternative, can we filter the retrieved data on Account Name, back on the FileMaker side – but we  don't know if we can (and how we can) do that.

 

Cheers,

Outcomes