9 Replies Latest reply on Apr 26, 2012 7:54 AM by karstyn

    FMSv11 on W2K8 - DSN will not Authenticate

    karstyn
      Customer has w2k8 server using FMSA 11.0.3.

       

      ODBC (32 bit) System DSN to MSSQL works fine with file on desktop accessed with FMPRO locally.

       

      The same file hosted on FMS prompts for user and password for DSN (it sees the server's system dsn's). The account info is stored in the External Data Source settings, and works in single user mode but not when hosted. Entering the correct username and password while the database is hosted will not authenticate to MSSQL.

       

        • 1. Re: FMSv11 on W2K8 - DSN will not Authenticate
          robwoof

          I'm glad I'm not the only one with this issue. I ran into this about 6 months ago and got nowhere. Same environment (almost - Win 2008R2, FMS 11.0.3, 32-bit system DSN to MS SQL Server). I have ESS on that server to a MySQL database working just fine, so I know I've got the DSN defined in the right place.

           

          Rob

          • 2. Re: FMSv11 on W2K8 - DSN will not Authenticate
            karstyn

            Hi Rob,

             

            This may be silly question but were you using External Autentication for MSSQL or a MSSQL internal account?

            • 3. Re: FMSv11 on W2K8 - DSN will not Authenticate
              robwoof

              Hi Karstyn,

               

              Not a silly question at all. It used external authentication via the Windows domain. I think I tried with an MSSQL internal account, since that made more sense for the idea of viewing MSSQL data in the FileMaker database. Thinking back, it may be that the internal account didn't work, but I can't remember for sure, and I'm not in a position to test it right now.

               

              How about for you?

               

              Rob

              • 4. Re: FMSv11 on W2K8 - DSN will not Authenticate
                karstyn

                Hey Rob,

                 

                I hope to have more info later today. The client has been using AD accounts, so we are asking they try creating and using a MSSQL user account instead - keeping fingers crossed!

                 

                I respond with the results when I get them.

                • 5. Re: FMSv11 on W2K8 - DSN will not Authenticate
                  karstyn

                  I just heard back from the client with good news -  you must SQL Server authentication and not External Autehentication. You also need to make sure you close and reopen the database for any changes to affect once the database has been hosted, with out this step you can be chasing your tail!

                  • 6. Re: FMSv11 on W2K8 - DSN will not Authenticate
                    ch0c0halic

                    karstyn may be the bearer of glad tidings,

                     

                    In FMP in the ESS table definition there is a 'Sync" button that updates the 'Shadow table' in FMP with any changes made to the SQL table.

                     

                    No need to restart the FMP DBs.

                     

                     

                    SQL authentication can be put into the ESS driver on the FMS computer, the External Data Source definition, or into a FMP script that accesses the ESS table.

                     

                    Security on the ESS table is done using the same RLA as for an internal FMP table.

                    • 7. Re: FMSv11 on W2K8 - DSN will not Authenticate
                      beverly

                      Karstyn, I had not been following this thread until now.

                       

                      SQL databases have authentication, as FM databases have authentication.  You can assign "users" to the databases and LIMIT access down to the field level (both SQL & FM).

                       

                      FM just happens to allow us to assign "External Server" for the authentication for an account. SQL databases should never have the same authentication as the Server they are hosted upon.

                       

                      Stopping and restarting SQL databases may or may not need to happen. IF I make schema (table/field) modifications in any SQL table, I simply have to SAVE the changes. These changes in SQL do NOT automagically show up in the ESS (FileMaker shadow tables). You must go to the table in FileMaker's Manage Database dialog and click the SYNC button, so that FileMaker can query the SQL table to get the changes.

                       

                      HTH,

                      Beverly

                      SQL & FM DBA

                       

                       

                      I just heard back from the client with good news -  you must SQL Server authentication and not External Autehentication. You also need to make sure you close and reopen the database for any changes to affect once the database has been hosted, with out this step you can be chasing your tail!

                       

                      • 8. Re: FMSv11 on W2K8 - DSN will not Authenticate
                        beverly

                        JJ, would you clarify?

                         

                        Are you saying that the ESS (SQL table) should have Record Level Access set on it IN FileMaker Accounts?

                         

                        The SQL tables can (and SHOULD) have access set by the user ON the SQL server (this is the user assigned in the DSN).

                         

                        Views (in SQL), can also limit access to the record level, before they ever get to FM.

                         

                        I guess from there, only certain users could have the RLA. But oh my!

                         

                        Thanks,

                         

                        • 9. Re: FMSv11 on W2K8 - DSN will not Authenticate
                          karstyn

                          Hi Beverly,

                           

                          I don't think I phrased that post very well. A breif recap from the start for clarity. When the FM database was opened in single user mode on the FM Server computer, the DSN had no issue connecting to the MSSQL database. With no other changes the DSN would no longer authenticate when the exact same database was hosted on FM Server (still on the same computer using the same DSN with the same credentials). So this isn't about refreshing the MSSQL schema in FM, it's about authentication from FM through the DSN to the MSSQL DB, and the different behaviour when the FM database is opened in single user mode or hosted.

                           

                          Here is the final quote from the client:

                          Solution: as Jud and Marx advised it must be set to SQL Server authentication.  The challenge comes from FM not picking up changes made in Manage External Datasources until the application is closed.  So if you try “manage databases” and it can’t connect, and then you change a setting in “manage external datasources” – even if that setting should make it right, the connection doesn’t update. Thus it fails based on the original input – not the updated input.  The only way to test a change made in Manage External Datasources” is to close FM and reopen it each time.

                           

                          Similarly if you had something that worked in manage external datasources, as I think I had at one point on Friday, and then make an invalid tweak, it will look like the tweak works.  I think that’s what happened Friday when I thought I got it to work, but when Marx tested it, it didn’t, and then I logged on only to be surprised it wasn’t working….

                           

                          Hope that is clearer!