I've got a connection form FMS12v4 to SQL Server thru ODBC on a Windows 2008 server. 2 separate machines on same domain. When I use SQL Server authentication and elect to store the user/pass in the DSN setup, it doesn't actually store it (using SQL Server Native Client 10). Each time I save, close, and reopen the dialog, the password is gone.
Parallel to that, I have the ODBC Authentication in FMP Data Source Setup also set to use Windows Authentication (Single Sign On), and have the correct SPN set (have also tried leaving SPN empty). In this config, when a FMP client logs in, I get a FMP dialog that says "The account and password you entered cannot be used to access this file. Please try again." (The User Account of the FMS machine does have access to the domain.)
If I use the option to Specify the user / pass in the same ODBC FM Data Source Setup window, although it retains the password from session to session, when a client logs in, it still prompts for credentials to the SQL data. Providing the correct credentials grants access to the data.
Bad. Don't want users to need to provide credentials to the SQL server. According to all settings, it should log in automatically, which is how I need it to work.
Have tried both 32 and 64 bit SQL drivers.