9 Replies Latest reply on Nov 23, 2011 10:40 AM by pthomas

    ESS with MS SQL Server and access privileges

    ghoffman

      I have successfully established an ESS connection from a local Filemaker database to a MS SQL Server using AD credentials through a System DSN. That local file happens to sit on a Windows Server 2008 R2 machine that is hosting Filemaker Server Advanced 11. That server is in the same domain as the SQL Server's host.

       

      The problem arises when I share a copy of that file under FMSA on that same server. When opening the FMSA-shared version of the file or otherwise trying to use or open the ESS connection, I'm prompted for credentials and no set of credentials succeeds in establishing the connection.

       

      Here is the prompt:

      Login Prompt.jpg

       

      Here is the result:

       

      Login Failed.jpg

       

      This prompt does not appear when the file is run locally with FMP, but only appears when FMP opens a shared version of the file. The credentials I use here are exactly the same as I use with the locally-opened file on the same machine. The DSN is the same.

       

      This appears to be the same issue Rob Woof reported under the old TechNet, so I repeated his subject line. However, I could not find his resolution in the archives of TechNet here on FMDEV.

       

      I'm beginning to suspect that the privileges under which FMSA runs are affecting this ESS connection. Does anyone have any other ideas?

        • 1. Re: ESS with MS SQL Server and access privileges
          BeatriceBeaubien

          Hi Gary,

           

          The last message in that thread, in July, 2011, was from Tim Brown, and I couldn't see a follow-up confirmation that it helped:

          <https://fmdev.filemaker.com/message/57743#57743>

           

          Best wishes,

           

          Beatrice Beaubien, PhD

          i2eye, Toronto, Canada

           

          FileMaker Business Alliance

          FileMaker 11 Certified Developer

          • 2. Re: ESS with MS SQL Server and access privileges
            mrwatson-gbs

            Hi ghoffman, Gary,

             

            are you hosting an FM-DB and a MySQL DB from the same server? Is that good?

             

            I couldn't follow exactly which file "that file" was, but it sounds like the FM-file on FMSA couldn't find the ODBC connection at all. (As far as I have understood the way FM & ODBC work together...) When this happens FM asks for ODBC credentials although there is no DB there.

             

            Is the ODBC Connection set up on the FM-Server, where the file is hosted?

             

            Russell Watson

            • 3. Re: ESS with MS SQL Server and access privileges
              ghoffman

              Russell,

               

              The server in question does not host any MySQL db's. It is dedicated to serving FM db's.

               

              The ODBC connection does work when a FM db is opened locally on the server. However, when the same FM db is then shared by FMSA, it asks for the credentials as I noted.

               

              Gary

              • 4. Re: ESS with MS SQL Server and access privileges
                pthomas

                Hi Gary,

                 

                I would think that because you have the ODBC source set to use AD security it will be attempting to use the login credentials of the workstation that is running FMP not the credentials that the FM server is running under (which will have been used when opening the database locally on the server), have you checked that the AD account for the workstation has been set up in the SQL Server?

                 

                Another thing to try would be to change the ODBC connection to use a SQL username and password instead of AD security and see if you still have the same problem?

                 

                Cheers,

                 

                Paul.

                • 5. Re: ESS with MS SQL Server and access privileges
                  ghoffman

                  Thanks for the ideas, Paul.

                  The problem occurs no matter who logs into a Windows session on the server, either the user that is set up in the ODBC connection or another user. All user accounts I have tested have access to the MS SQL Database. In fact, the problem occurs when trying to access the shared file from FMP running on a separate Windows 7 or Mac OS X workstation.

                   

                  For testing, I could ask the DB admins of th SQL Server to give me an SQL username, but that's not how they are administering users on their server. Besides, accessing AD-controlled databases is an advertised feature of Filemaker Server. I'm just trying to make it work as advertised.

                   

                  In fact, in my latest testing, I can close the file in FMSA and open it locally with FMP without even moving it and it accesses ODBC data just fine. After I close it, I then open it with FMSA and try to access it as a shared file and the problem recurs.

                   

                  I've now opened an incident with Filemaker to see if we can get their engineers to tackle the problem. I'm still open to other suggestions until they find me an answer.

                   

                  Gary

                  • 6. Re: ESS with MS SQL Server and access privileges
                    pthomas

                    Hmmm the only other thing I can think is to check the account the FMSA service is set to run as, that may be the account that it is trying to use to authenticate with the SQL Server.

                     

                    Cheers,

                     

                    Paul.

                    1 of 1 people found this helpful
                    • 7. Re: ESS with MS SQL Server and access privileges
                      ghoffman

                      Well, yes, that is the solution. In the Services administration of the host machine, I changed the user that FMSA logs in as to the user that had rights on the MS SQL Server database, and now it works "as advertised." I need to review the implications of this, such as:

                      Does this impair the ability of FMSA to serve databases or run the web engine or otherwise change the behavior of my FM database structure.

                       

                      So I guess this is a tentative: Bingo!

                      1 of 1 people found this helpful
                      • 8. Re: ESS with MS SQL Server and access privileges
                        ghoffman

                        Here is a follow-on. It seems the only way to get this to work is for the Filemaker Server service to log on to the local machine using the credentials of the domain user who is connecting via the ODBC connection to the MS SQL Database AND that domain user needs to have admin rights on the local machine.

                         

                        Without admin rights, Filemaker Server service can't serve databases and IWP, etc. don't run right either. Without rights to the ESS database, Filemaker Server prompts for credentials for the ODBC connection.

                         

                        While my solution can now work, I am troubled by the implications:

                         

                        1. The user account I am using to access the remote MS SQL Database now has full admin rights on the host machine of the Filemaker Server, which to me is a security concern.

                         

                        2. I cannot connect to any other MS SQL Database in the domain except by using this domain user's credentials. I don't know about connecting to non-domain data sources or using SQL credentials on a domain-controlled database.

                         

                        I will report my results to Filemaker to see if they have any further suggestions.

                         

                        Thanks all who have read and responded to my query.

                         

                        Gary

                        • 9. Re: ESS with MS SQL Server and access privileges
                          pthomas

                          Hi Gary,

                           

                          Glad you have at least got a work-around even if you aren't 100% happy with the way you had to do it.

                           

                          Generally when I am setting up a SQL Server I will also set up a new domain account with full network administration access and a very strong password. This is the account I then use for all of the SQL Server services and in your particular instance is also the account I would set the FileMaker Server service to use as well.

                           

                          This is the account I would use for any installations of SQL Server on our network, so that I know that if required the servers will be able to talk to each other and any other pieces of software will also be able to talk to each of the different servers on the network.

                           

                          Cheers,

                           

                          Paul.