Filemaker: Version 16
Sql Server: 2017
On the Windows server side I created two 64-bit ODBC Data Source (System DSN):
1. FILEMAKER: connection to SQL Server through SQL Server authentication with fixed user "sa" and password (database owner); single user for all accesses.
2. FILEMAKER_SSO: Connection to SQL Server via Windows network authentication;
I created two data sources in FileMaker:
1. FILEMAKER: ODBC type with unique authentication specifying user name and password of "Sa";
2. FILEMAKER_SSO: type ODBC with Windows authentication (SSO).
In FileMaker, I created a script that, using the "run SQL"command, specifying the ODBC FILEMAKER_SSO driver, executes a Stored Procedure in SQL Server. Access to the SQL Server database takes place with the network user. Perfect. What I wanted.
In FileMaker database management, to import a SQL Server table into the ER schema I must use the FILEMAKER data source (user "sa"); if I use the FILEMAKER_SSO data source (network user) this operation is not possible. The system will send me the following message:"Your account and password cannot be used to access this file. Try again.".
The problem is as follows: when performing DML operations on the SQL Server table imported into FileMaker, all accesses to the RDBMS SQL Server are made with "sa"user. Because the table was imported via external FILEMAKER data source. I would like to use FILEMAKER_SSO so that DML operations on this table are logged with the network user.
Could it be a security issue set on SQL Server?
Has anyone had the same problem? Can you give me a helping hand to solve it?
Thank you very much