8 Replies Latest reply on Mar 7, 2013 11:09 AM by dreed

    Trouble with ODBC import scheduled script

    dreed

      I've read that SQL server sources used as ESS need to have the following domain setup to work for files hosted on server

       

      http://www.filemaker.com/help/html/odbc_ess.20.7.html

       

       

      Since my company only allows windows accounts (not sql server accounts) to access SQL server, I can't seem to get ESS to work on FMS files, since I've not been able to convince IT to configure domain accounts as shown in the link.

       

      Unfortunately, this also appears to carry over to importing ODBC from that source when using a server side script. I received an 802 error when trying the script on the server.

       

       

      These ESS and ODBC imports work fine when run on a client, but I can't figure out a way to keep updated data available on a hosted file without using a robot client to periodically run the import.

       

      Has anyone else run into problems when SQL server only allows authentication via Windows domain accounts?

       

       

      Thanks,

       

      Dana

        • 1. Re: Trouble with ODBC import scheduled script
          PowerSlave

          Have you tried 'storing' the account details within the ODBC dsn ? If you use the full domain account (domain\accountname) and store that in the ODBC driver , and in your FM script do not have a username or password (but still tick the remember login on the import script step) , then Filemaker should use the login credentials that are stored within the ODBC datasource. This has been the case in our company and seems to work fine.

          Also be aware if you are running the 64bit version of FM server then you will need to setup the 64bit ODBC datasource. This may not be obvious at first glance because if you install FMPro on your server , it will use the 32bit datasource ODBC driver instead.

          • 2. Re: Trouble with ODBC import scheduled script
            dreed

            Thanks for the reply!  I still get no love when I try to put in my credentials when setting up the DSN, whether or not I use the domain name.  It works fine in the driver if I use Integrated Windows auth, but then it won't work with FMS.  Here's what I see if I enter my domain info in the driver.  Any ideas what I might be doing wrong, or is the SQL server just not configured to allow anything but integrated windows auth?

             

            auth.png

            error.png

            • 3. Re: Trouble with ODBC import scheduled script
              dreed

              Oh, and I should note that when logging into SQL server via the SQL server management studio, I also cannot enter my own domain credentials.  It seems that when you enter credentials those can only be SQL server accounts not domain accounts.  And when choosing windows login, you must be logged in as that user.  Seems I have to either convice IT to create a SQL server account I can use, or allow my domain account to be used for delegation/impersonation.

              • 4. Re: Trouble with ODBC import scheduled script
                BowdenData

                Hi,

                 

                I had this discussion with a client some time ago. I presented my request for a SQL Server account as a security issue. I showed them the same help file entry that you linked to. Rather than jumping through a lot of hoops with getting a domain account authorized, the SQL server account was configured very specifically in my case. The account was only valid for connecting to a specific SQL server, it could not be used to log into the domain, it had limited privs for a designated dB and designated tables within that dB, and so on.

                 

                I don't know the details of how they set up the account, but I had to specify the above details to them before they did their bit.

                 

                HTH.

                 

                Doug

                • 5. Re: Trouble with ODBC import scheduled script
                  beverly

                  SQL server can have multiple users assigned access. The users privileges, similar to FMP access, can be set up down to the field/column level. The user may be able to SELECT (find, sort), INSERT (create), UPDATE (edit), and/or DELETE records/rows data. The DBA should know how to do this. 

                   

                  -- sent from my iPhone4 --

                  Beverly Voth

                  --

                  • 6. Re: Trouble with ODBC import scheduled script
                    dreed

                    Thanks Doug and Beverly!  I finally convinced IT to allow access to SQL server with a SQL server account.  They were hesitant to create one as they like to use domain accounts for everything.  But once they created the account and gave it data_reader privileges, the scheduled FMS script can run without a hitch!

                    • 7. Re: Trouble with ODBC import scheduled script
                      wimdecorte

                      I know your problem is solved but one thing to look at is whether the FMS machine itself is properly joined to the domain.  Are you using EA for the FM accounts without issues?  If yes then there is no problem with the FMS machine being a proper member of the domain.

                      • 8. Re: Trouble with ODBC import scheduled script
                        dreed

                        Hi Wim,

                         

                        Indeed I have no trouble using AD groups to authenticate my filemaker users accessing the database.  I just couldn't get SSO to work for connecting to ESS data or doing a server side ODBC import script.  So for that I'm using a SQL account, but for regular and iwp access to the databases, the users are logging in with their domain accounts.

                         

                        Thanks,

                         

                        Dana