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:
Beatrice Beaubien, PhD
i2eye, Toronto, Canada
FileMaker Business Alliance
FileMaker 11 Certified Developer
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?
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.
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?
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.
1 of 1 people found this helpful
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.
1 of 1 people found this helpful
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!
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.
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.