12 Replies Latest reply on Jan 25, 2017 9:15 AM by JasonPierce

    Authenticate against SQL?

    JasonPierce

      Good day all,

       

      I have an existing application that stores usernames and passwords in an SQL database. We frequently add and remove users from that application/SQL database.

       

      We now want to give those users access to a FileMaker solution that is hosted on FMS 15 WebDirect. We need to be able to identify the user within the FileMaker solution (eg script step "Insert Current User Name").

       

      Please advise if the following is the correct approach.

       

       

      Auto-login to solution as a user that has access to a single layout that accepts username and password.

       

      Pass the username and password to a script that checks against SQL.

       

      If the username doesn't exist in SQL, fail

      If the username and password don't match SQL, fail

      If the credentials match but account is not marked "active" in SQL, fail and look for FileMaker user by the same name and delete FileMaker account

      If the credentials match and account is "active" in SQL, look for FileMaker user by the same name and create( if it doesn't exist, then pass the credentials to "re-login" script step.

       

       

      How would I check if a FileMaker account exists, if I'm approaching this the right way.

       

       

      Thank you!

      Jason Pierce

        • 1. Re: Authenticate against SQL?
          Chris Irvine

          Hi Jason,

           

          It is possible to get there using the path you are currently on. However, this kind of approach is often discouraged because of risks you may unknowingly expose your solution to. How secure does your solution need to be?

           

          The preferred model for shared credentials between FM and some other platform would be through External Authentication. When enabled through FileMaker Server, each user's connection attempt is authenticated against the server's operating system, "Is this username & password good? If so, what security groups do they belong to?". In this model, you create a few permanent external auth accounts in your solution that correspond to your server's security groups. This keeps things simple and make security a lot more verifiable, with no guest or auto-login accounts required.

           

          Would it be possible to update an LDAP database when users are created or edited in the SQL system? If so, your FMS server machine could verify credentials agains the LDAP directory. Lots of directory/authentication configurations are available that could likely also integrate with your existing SQL system.

           

          Cheers,

          Chris

          2 of 2 people found this helpful
          • 2. Re: Authenticate against SQL?
            wimdecorte

            That's a really bad approach IMHO.  Just don't store security elements as pure data...

             

            This is always the suspect part:

             

            Auto-login to solution as a user that has access to a single layout that accepts username and password.

             

            From that point on you will have passed the FM native security and will rely on scripting to do your security.  Anything that can interrupt your scripts is very likely going to subvert your perceived security.

             

            If you don't want to keep the accounts in FM, then look at using External Authentication.

            1 of 1 people found this helpful
            • 3. Re: Authenticate against SQL?
              JasonPierce

              A high level of security is required here. I have locked down the FileMaker web server to only accept the highest ssl cyphers from the most recent browsers that are capable of utilizing PFS.

               

              As an added layer of protection outside of FileMaker security(SQL), the root of the website is locked down with Basic Auth that requires a username/password that corresponds to a "group" of users (this name/pwd is shared among the members of the group). This takes care of the bulk of the security needed.

               

              Once in FileMaker, there will only be one, read-only layout used by these off-site web users. And I need to keep a record of which specific usernames access which records.

               

              I'm thinking an alternative to creating/removing FileMaker accounts could be something more like session-auth on web pages. Use the OnLayoutEnter trigger in the secured-layout to check if we have a $$user_authenticated session variable which would have been set by the login-layout after checking username/password against SQL.

               

              This approach would bring about a some questions about how scripts are ran for webdirect.

               

              Does the script execution and variable storage happen client side in the web browser or server side?

              • 4. Re: Authenticate against SQL?
                JasonPierce

                wimdecorte wrote:

                 

                Auto-login to solution as a user that has access to a single layout that accepts username and password.

                 

                From that point on you will have passed the FM native security and will rely on scripting to do your security. Anything that can interrupt your scripts is very likely going to subvert your perceived security.

                Perhaps I'm misunderstanding how something works here, but from what I can tell FM native security would not get bypassed. The user would be "locked" into the special pre-login account that only has access to the login layout. They would only have access to anything else after they pass credentials into the "re-login" script step to become their real user.

                • 5. Re: Authenticate against SQL?
                  beverly

                  I would start here

                  Using external authentication with the FileMaker platform - Overview | FileMaker

                  and never try to _store_ authentication in any database (FM or SQL) without a method to encrypt that cannot be determined without the keys to get there.

                   

                  beverly

                  1 of 1 people found this helpful
                  • 6. Re: Authenticate against SQL?
                    wimdecorte

                    JasonPierce wrote:

                     

                    wimdecorte wrote:

                     

                    Auto-login to solution as a user that has access to a single layout that accepts username and password.

                     

                    From that point on you will have passed the FM native security and will rely on scripting to do your security. Anything that can interrupt your scripts is very likely going to subvert your perceived security.

                    Perhaps I'm misunderstanding how something works here, but from what I can tell FM native security would not get bypassed. The user would be "locked" into the special pre-login account that only has access to the login layout. They would only have access to anything else after they pass credentials into the "re-login" script step to become their real user.

                     

                    No 'by-passed' but 'passed through'.  The auto-login will in effect authenticate AND authorize the user into the solution.  And yes you may be taking steps to try and lock it down but the fact is that you let EVERYONE into your solution and try to catch the real users after they have already been authorized to some extent.  If anyone can find a way to leverage that then your solution is at risk.

                    The key qualifier is that you let EVERYONE into your solution and try to catch up with scripting vs. authenticating and authorizing at the front door.

                     

                    The security measures you outline are all focused on the web side of things.  But that does not stop people with a copy of FMP to try and access the file.

                    • 7. Re: Authenticate against SQL?
                      JasonPierce

                      beverly wrote:

                       

                      I would start here

                      Using external authentication with the FileMaker platform - Overview | FileMaker

                      and never try to _store_ authentication in any database (FM or SQL) without a method to encrypt that cannot be determined without the keys to get there.

                       

                      beverly

                      Don't worry beverly, the external application that "owns" the SQL database has the passwords hashed with a cipher (sha512) that is likely stronger than what FM uses for its native authentication system.

                       

                       

                      I am familiar with "external authentication" and we auth many users against corporate Active Directory for another FM solution. This is more of an issue of integration with an existing system that can not be tied into Active Directory.

                      • 8. Re: Authenticate against SQL?
                        JasonPierce

                        wimdecorte wrote:

                         

                        JasonPierce wrote:

                         

                        wimdecorte wrote:

                         

                        Auto-login to solution as a user that has access to a single layout that accepts username and password.

                         

                        From that point on you will have passed the FM native security and will rely on scripting to do your security. Anything that can interrupt your scripts is very likely going to subvert your perceived security.

                        Perhaps I'm misunderstanding how something works here, but from what I can tell FM native security would not get bypassed. The user would be "locked" into the special pre-login account that only has access to the login layout. They would only have access to anything else after they pass credentials into the "re-login" script step to become their real user.

                         

                        No 'by-passed' but 'passed through'. The auto-login will in effect authenticate AND authorize the user into the solution. And yes you may be taking steps to try and lock it down but the fact is that you let EVERYONE into your solution and try to catch the real users after they have already been authorized to some extent. If anyone can find a way to leverage that then your solution is at risk.

                        The key qualifier is that you let EVERYONE into your solution and try to catch up with scripting vs. authenticating and authorizing at the front door.

                        The current solution (without web interface) is separated into interface-file and data-file. Both use FM native security/LDAP user accounts. I would anticipate making the web-interface-file into a third file. That third file would be the only one with a "pre-login" user.

                         

                        Does this sufficiently isolate the "pre-login" user account from my data? I believe the data file would only be accessible after executing relogin to a real account that exists in the data file's FM native security

                         

                        The security measures you outline are all focused on the web side of things. But that does not stop people with a copy of FMP to try and access the file.

                        I believe the separated files should address this as well. However, the actual files are hosted on a dedicated server that only have filesystem access for Domain Administrators and system accounts. The FMS is configured to use SSL connections. Is there anything else locally to look at?

                        • 9. Re: Authenticate against SQL?
                          JasonPierce

                          Another idea to accomplish this would be to schedule a script on FMS to run once per hour that would query the SQL server looking for new accounts, create a native FM user account, and email a generated password to the user.

                           

                          The one-hour delay on new accounts won't be a problem for our environment, and I think this should eliminate any concerns with client scripts being exploited during login.

                           

                          I see there is a script-step for "Execute SQL", but it does not appear to return data to the FMS script to be able to determine if a new account should be created. I could make "Script Sequence" and use a system-level script to run the SQL query and dump the "new" username to a temp file then read-in that temp file with an FMS script that has an "Add Account" step.

                           

                          But is there a better way to get SQL results into a FMS script?

                          • 10. Re: Authenticate against SQL?
                            wimdecorte

                            The "Execute SQL" script is to send data to SQL server, not for SELECTs.

                            To get data you can use the "Import - from ODBC source"

                            Or if your SQL server is on the supported list you can use ESS and treat the SQL table like a native FM table.

                             

                            Accessing External SQL Data Sources (ESS) Overview and Troubleshooting | FileMaker

                            • 11. Re: Authenticate against SQL?
                              DavidJondreau

                              Jason,

                               

                              You may have covered all the bases. You seem to have a strong awareness of all the issues.

                               

                              If the accounts are only authorized for WebD access and not fmapp, I can't see a way through. But all the FM files should have that limitation. If users that would be authorized with this WebDirect method would also have direct FM access, it's a little trickier, namely because it's hard (impossible?) to 'hide' a file. A user who has a username/pass, fmapp permissions, and knows the server address can skip your opener file. Knowing the server address isn't that hard if a user has ever accessed it with FM.

                               

                              You may get different opinions, of course.

                               

                              There's a few other minor tweaks you could add. The authentication script needs to run with full access, so I would have it call a second script that actually does the SQL query. I would add another script that logs login attempts. You could have the script run automatically and use only global fields or even a custom dialog so the user never has any layout access. Just a single script. You should even give table / field permissions for those globals since that parent script running as full access.

                              • 12. Re: Authenticate against SQL?
                                JasonPierce

                                Chris Irvine wrote:

                                 

                                Hi Jason,

                                 

                                It is possible to get there using the path you are currently on. However, this kind of approach is often discouraged because of risks you may unknowingly expose your solution to. How secure does your solution need to be?

                                 

                                The preferred model for shared credentials between FM and some other platform would be through External Authentication. When enabled through FileMaker Server, each user's connection attempt is authenticated against the server's operating system, "Is this username & password good? If so, what security groups do they belong to?". In this model, you create a few permanent external auth accounts in your solution that correspond to your server's security groups. This keeps things simple and make security a lot more verifiable, with no guest or auto-login accounts required.

                                 

                                Would it be possible to update an LDAP database when users are created or edited in the SQL system? If so, your FMS server machine could verify credentials agains the LDAP directory. Lots of directory/authentication configurations are available that could likely also integrate with your existing SQL system.

                                 

                                Cheers,

                                Chris

                                This is the method I ended up using. I was trying to avoid having to rely on the LDAP database being manually kept up to date with the SQL database, but in the end it ends up being more straightforward than trying to auth directly from SQL data.

                                1 of 1 people found this helpful