Error with a Windows Authentication ODBC Connection for Filemaker

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.

Sorry, if you are saying that the Single Sign-On (SSO) only works in a local PC environment (I.E the FileMaker database must be stored on the User’s own PC), that does not tie in with what your manuals are saying.

If you look about ½ way down page 55 of the “FilemakerServer 15 Getting Started Guide” (fms15_getting_started.pdf) we see this section:

Enabling ODBC data source single sign-on (Windows)

The very 1st detail line in that section says:

"If you work with FileMaker Pro databases hosted by FileMaker Server that accessODBC data from Microsoft SQL Server, you can configure the master machine toenable single sign-on (SSO). ODBC data source single sign-on allows FileMakerPro clients to use their Windows-authenticated login credentials andpermissions to access Microsoft SQL Server without logging in."

That 1st line specifically says that we can use SSO in ODBC on FileMaker databases hosted on the central FileMaker Server – and other text, in this document and in other FileMaker manuals, points to using SSO with FileMaker files on a FileMaker Server too – I'm sure I didn't see any comment about it only working locally within a PC, only with FileMaker databases stored on the local PC.

Below the section heading and initial line that I've reproduced above, that manual goes on to discuss the configuration/setup that is required to get SSO working - in my original email for this call, I showed what we had done here to activate SSO.

If we can't use SSO as we want to (I.E with databases hosted on a Filemaker Server), can you please explain what page 55 of that manual is actually saying? Am I misreading the manuals? Or is that manual (& others?) just wrong about SSO? Does Filemaker really not allow SSO with databases on a Filemaker Server?

I can't answer why the documentation says what it says, but I could never get it (SSO) to work. I recall many years ago when first struggling with this, I contacted our FM systems engineer asking about this process and about the "SPN". Don't quote me 100% on this, but the response was along the lines of "the FM engineers who set this up were working with Windows NT Server and they got it to work under some specific combinations of PC OS and server OS. Those staff had left the company some time ago, the Microsoft server OS's evolved, and the code in FM had not be looked at or altered from when it was first introduced, and so forth". The advice was to just use a SQL server account, which we did, and it worked very well.

My first attempt was with Windows 7 PC's, Windows Server 2003, and FMP11. I subsequently tried this at another company later on using Win7 PC's, Win Server 2008, and FMP12 and FMP13. We also tried with Win Server 2012. At the second company, I was working directly with their Microsoft IT person who had full control over the software and hardware.

The SQL server account was setup with only the bare minimum privs needed. In both companies, we only needed to be able to read data from a few specific tables in a specified SQL dB.

Maybe someone out there has been able to get it to work recently and will chime in, but I would go ahead and look at setting up the SQL server account in the meantime.

We do have Filmaker successfully pulling records from MS SQL 2008 R2 via an ODBC connection, with the 2 other types of DSNs (I.E a DSN using a SQL Server Account, and a DSN using an Active Directory Group whose name exactly matches the name of an External Server Group defined in Filemaker) - they both do work - BUT neither of those DSN 'types' results in my personal network Login ID (I.E "Mark.Watson") being stored in the 'connecting Login ID' columns on my personal SQL connection's record in the SQL connections system table - so in the VIEW I use to pull SQL data, I cannot apply security based on the known user's Login ID so that unauthorised records are not returned to Filemaker. That's my whole problem - I need to connect to SQL as each User's network login ID, so that the SQL VIEW can filter on the User's personal Login ID, to exclude records not authorised to the User.

If that just cannot be done, then I have to try something different - it will make my querying MUCH MORE complicated in SQL.

If someone has got Filemaker 15 ODBC Single Sign-On working via a Filemaker file hosted on the Filemaker Server, some help on setting up SSO in that scenario would be much appreciated.

Sorry, I don't understand what your 2nd paragraph means, from the "OTOH, ..." - I haven't got SSO running in any fashion as yet - that's my problem, I just can't get it to work.

And what do you mean "Do I have full control on the server?" - my network login is in the Domain Admins A.D. group in our Windows domain - so I personally have full Domain access.

ON 09/09/2016 @ 2:28pm Sydney time, I sent a 2nd separate email to 'apac_support@filemaker.com', titled "Cannot get Single Sign-on DSN/ODBC connection from Filemaker into MS SQL Server working"; a couple of minutes later I received a confirmation email that had Subject = "160909-000004". In that email, I further explained my problem setting up SSO thru a database on the Filemaker Server; in that email I included screen shots of all of the configuration settings we made to try to satisfy the setup requirements of SSO (the settings described in the section starting 1/2 way down page 55 of your 'fms15_getting_started.pdf' manual).

Has anyone out there got SSO working in a Windows domain thru a filemaker database hosted on the Filemaker Server?

I have solved my data security problem a different way - I am restricting Users to their authorised records by limiting access on a record-by-record basis via a formula coded against each of the applicable VIEWs in our MS SQL 2008 database (accessed via ODBC) thru a Privilege set. So I no longer need to set up Single Sign-On to help me. This record-by-record filtering seems to be working ok.