You have to remember that as a service, the call of filemaker server using odbc is using a system account, which will not authenticate regardless of how you store the credentials on the fm ess. Instead you need to change the authentication on the dsn level. Think about this, otherwise cwp and iwp would not work with ess. However, hosting the fm file locally on your pc would work as expected (single sign-on).
Is this for ESS or ODBC import? Server?
I'm not sure about single sign on. I don't like to setup users with the same passwords for the SQL database, as they could attach to the database with other unauthorized tools. So I use a special password that I embed in Filemaker for the Filemaker login to the SQL Server. You can derive a "secret" password from their name some how and have a different password per user, or use the same "secret" password for all the Filemaker users into the SQL database (this makes it easier to change on a regular basis). I've found this strategy to work pretty well, and it provides some added security from outside the Filemaker application.
Thanks for responding. We originally tried to store the authentication within the DSN settings, but as I mentioned, they don't stick. When we re-open the DSN after saving, the password is empty, not even ******. Logging in from FM either prompts for credentials, or says they're no good. Google research shows that Microsoft doesn't allow saving of the password in the DSN (as it's a 'security risk'), which is odd because they provide the fields to do so. I didn't mention before, but it's a Microsoft SQL Server.
In the FM Data Source setup:
- selecting "Prompt User..." works (and successfully provides access to the SQL data), but we dont want users to need to do that.
- selectng "Specify User Name and Password", appears to 'stick', but when a user logs in, the data is not available (<Table Missing>)
- selecting "Use Windows Authentication" promtps thats the user/pass is not valid.
I'm wondering why both MS (in the DSN setup) and FM (in the External Data Source setup) provide fields to store credentials, when neither of them seem to work...
Thanks for any further insight!
It's for ESS via ODBC, so SQL tables & colums can be visible in FM.
Yeah, the plan WAS to use special passwords to the SQL database in both the DSN setup and the FM External Data Source setup, but as I replied above, no such luck... We also tried to setup a AD with the FMS machine as a qualified user, but that still prompts for creds (or denies access), no matter the settings in the DNS or FM. I feel like I've tried everything, but I must be missing something.....
Is there anyone that's gotten this to work?
You can set and save a variable password in Filemaker in the ESS Database setup. You do have to reference inside of Filemaker. I have a custom function I use that gives me an obfuscated password based on the users name.
I haven't tried this with SQL Server, but in Oracle, with admin privs, you can have Filemaker drop and create new users via ExecuteSQL (the script step, not the function). So the user never sees the password at all. Or, you can have them give you the password the first time and then save it in user table with appropriate security. I prefer to obfuscate the password in any case to keep nosy users from poking where they shouldn't.
The spelling on the script step has a space: 'Execute SQL'. This script step allows INSERT, UPDATE & DELETE to an ODBC source, if permissions are granted for these types of queries. To SELECT the same source (DSN) you use the Import script step.
ExecuteSQL (the function, note spelling). Allows you to SELECT from tables/table occurrences as so named on a relationship graph. No ODBC/DSN needed.
-- sent from my iPhone4 --
Are you storing credentials on the 64bit odbc dsn? For fms 12, ESS uses the 64 bit driver and the fms script engine uses the 32 bit driver. It's a good idea to have both versions of the driver setup the same.
Finally discovered the problem....
When setting up an External Data Source (EDS) for a file that's running locally on the desktop, you send the User/Pass as User Pass. When that file is then hosted on FMS, and checking the EDS setup again, the User has become re-cast as "User" and the password is empty. Putting it in again as "Password" (ie, with quotes), it connects, and sticks.
So, for setting up EDS on local FMP files, you need to use Password on FMS files, you need "Password". Go figure...