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