We are running FileMaker version 22.214.171.124.
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.