9 Replies Latest reply on Nov 25, 2013 6:25 PM by JoelStoner

    FMSA12, Single Sign On with ODBC to SQL Server

    JoelStoner

      Hi,

       

      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.

       

      Any tips?

        • 1. Re: FMSA12, Single Sign On with ODBC to SQL Server
          PowerSlave

          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).

          • 2. Re: FMSA12, Single Sign On with ODBC to SQL Server
            LSNOVER

            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.

             

            Cheers!

            Lee

            • 3. Re: FMSA12, Single Sign On with ODBC to SQL Server
              JoelStoner

              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!

              • 4. Re: FMSA12, Single Sign On with ODBC to SQL Server
                JoelStoner

                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?

                • 5. Re: FMSA12, Single Sign On with ODBC to SQL Server
                  LSNOVER

                  Joel:

                   

                  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.

                  • 7. Re: FMSA12, Single Sign On with ODBC to SQL Server
                    beverly

                    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.

                     

                    HTH

                     

                    -- sent from my iPhone4 --

                    Beverly Voth

                    --

                    • 8. Re: FMSA12, Single Sign On with ODBC to SQL Server
                      PowerSlave

                      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.

                      • 9. Re: FMSA12, Single Sign On with ODBC to SQL Server
                        JoelStoner

                        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...