Syncing around security

[kmtenor]

Hello.

 

Thank you for the marvel that is FmEasySync!  It is exactly what we needed for our relatively simple sync needs, and I truly appreciate the thought that has gone into creating it.  I am only seeking help because our application is made complex by some security requirements, and I have run out of ways I can think of to troubleshoot it.  I'm hoping someone else can offer a suggestion that will knock this loose for me.

 

Our application is an "observations" database that runs in Go on the iPhone.  It allows our managers to make safety observations and sync them to a central database for later review.  In addition, if what they are observing needs to be addressed by our maintenance staff, they can also request a maintenance ticket to be created.  We sync the maintenance requests separately, since there is already a "bucket" set up to receive requests.  we would like our users to be able to file maintenance requests without having to go through safety, as well, so the app actually has two wizards - one for Safety, the other for Maintenance.

 

 

The central "Observations" database has been set up to stand alone, so as to avoid security conflicts with other HR-related databases.  That sync is working just great, when tested from the desktop and on mobile.

 

Our "Maintenance" database has filemaker security turned on that allows users to login via their Active Directory username and password.  This keeps things simpler for them, and allows us to track who has done what and when more easily.  The sync tables for this database are simple table occurrences within the "Observations" database.  When I wire in the Maintenance database, it syncs GREAT from the desktop, but when I try to sync to it from the mobile device, the first table that tries to sync fails, saying that "Field att_id[1] is missing.  Filemaker Error Code 102").  

 

For a variety of reasons, I would prefer to avoid setting up a second database for the application to sync requests in to the maintenance side.  If necessary, it will have to be set up just for the purpose of syncing, and will have to be imported on a schedule by the server.

 

 

 

As I said, from the desktop the sync works - so I know all of the table setup is correct on both sides (mobile and hosted). Which leaves me with security as the culprit for the errors.  In fact, I have found that, if I log in to the (hosted) maintenance database and then run the sync from the mobile client, it works.

 

 

Knowing that FMEasySync is going to use the security settings on the client to attempt to sync to the host, I have done the following:

 

1. established a local FileMaker user called "Mobile" and applied it, with the same username and password, to all three databases - observations_mobile, observations_hosted and maintenance.    

 

2. I have gone so far as giving the "Mobile" user "Full Access" to all three databases, and still the sync fails from the mobile.

 

3. I have attempted to pick out the scripts that are involved in a push (on both hosted and mobile), and set them to run with "full access privileges" and that doesn't seem to have worked, either.  

 

4. I have attempted to use the "File Access" tab of the Manage Security dialog to give permission to both the mobile and hosted databases, and that did not work, either.

 

I feel like I'm missing just one, small, detailed thing that will knock this loose on the mobile side, and I'm just not sure what it is or where to find it.  Any help is greatly appreciated.

 

Thanks.

 

-Kevin

 

 

 

 

 

 

[timdietrich]

Kevin --

 

The error that you referenced ("Field att_id[1] is missing.  Filemaker Error Code 102") seems to indicate that the mobile user is connecting to the hosted database, but when the server tries to process the payload, one of the fields that it is trying to set is missing.

 

I would double-check everything, and make sure that "att_id" is a field in the hosted database.

 

I hope this helps.

 

-- Tim

[LaRetta]

You will also get this error if the relationship between the EasySync_payload_details and the ES_ data table is invalid.  At least that was one of my issues, so you might double-check the relationship in EasySync table occurrence group for that table.

[kmtenor]

Hi, Tim and LaRetta.

 

Thanks for your suggestions. I have double-checked everything, and all signs still point back to security being the issue.  Tim, you're right - mobile is successfully connecting to hosted and is even pushing the payload - but when the script tries to process that payload into the password-protected tables from the external database, it fails.  I've attempted to attach a screenshot that should answer LaRetta's question - unless there's something under the hood I need to look at within that join?

 

 

That screenshot is taken from my hosted sync database.  Note that the tables "ES_PUSH_flagwork" and "ES_PUSH_attachments" are references to TOs in an external file.  I believe this is where the problem lies, since that external file has security enabled.

 

Here's the file structure:

 

hl_observe_mobile - the mobile file

hl_observe_hosted - the hosted file - includes local tables for observations and photos, and remote references to hl_maintenance

hl_maintenance - the maintenance file - remotely referenced "flagwork" and "attachments" tables that are being synced to when necessary

 

I have done some testing from a laptop that is logged in as a local account - so, there's no way that domain accounts are providing any "assistance".  This is essentially the same situation as a mobile device, which has no concept of domain logins unless you explicitly enter them.

 

When I open up hl_observe_hosted on the laptop, it opens fine. However, if I try to open up the Manage Database dialog, I am presented with a login for the sm_maintenance database. This happens even though I have hl_observe_hosted set to auto login as "Mobile" - which is a local Filemaker account that exists on all three databases: mobile, hosted and maintenance - and has [Full Access] privileges everywhere.  If I click "cancel" - as though I don't know what an acceptable password for the maintenance database would be - and then look at the relationships graph, I get "file missing" on the references to the external TOs (flagwork and attachments).  That would cause the 102 error that we're seeing, since the table that we're trying to sync to "isn't there".

 

To test the theory, I did two syncs from the disconnected PC.  Starting from a fresh Filemaker launch (no databases open):

 

1. Just open the mobile app, create a record and try to sync.  It failed.

2. Open the mobile app.  Then open the hosted app, and go to Manage Database, entering the "Mobile" username and password.  Sync from the mobile app.  It worked.

 

I am starting to believe this is a Filemaker issue that has to do with authentication when accessing remote TOs without opening any layouts on the remote file.  When the remote file is accessed, the local security doesn't appear to be carrying through the connection.  I'm sure there's a rational explanation for why it doesn't work, and shouldn't ever work, particularly since we're talking about security.

 

I am also starting to believe that the notion of adding remote, password-protected TOs to the graph of a hosted sync database and then syncing to them might not be within the scope of problems FMEasySync was intended to solve.  In other words, I know I'm pushing the limits here.  I can work with that, but I didn't want to let it go without checking whether the expectation is that what I've done should be working. 

 

I apologize for how complicated this is to describe.  In theory, I should be able to just connect to these two tables and sync.  In this case, however, I think I might have over-complicated what is intended to be an "Easy" solution, in an attempt to make things easier for myself.  That's not FMEasySync's fault - that's human error.

 

I look forward to hearing your thoughts.

 

Thanks.

 

-Kevin

 

 

 

[timdietrich]

Kevin --

 

"I am also starting to believe that the notion of adding remote, password-protected TOs to the graph of a hosted sync database and then syncing to them might not be within the scope of problems FMEasySync was intended to solve."

 

Actually, I think the problem has more to do with how scripts running via Perform Script on Server (PSoS) are handled. I've run into something similar in the past, but it had nothing to do with sync...

 

When PSoS kicks off a server-side script, the server essentially opens up a client session that session "inherits" the account / privilege set of the user that initiated it. However, from what I can tell, the user's credentials are not honored in anything other than the database the script was started from. In other words, if the database has external references, then they will fail because the other databases will reject the credentials (even if the user has an identical in the secondary databases). So if my theory is correct, PSoS cannot reference external datasources. That would explain why you're seeing such strange behavior.

 

You might want to try disabling the PSoS option, and see what happens. My guess is that you'll see the sync work as expected. (There are three settings that you can use to disable PSoS: $$use_psos_during_push, $$use_psos_during_pull, and $$use_psos_during_sync_check.)

 

Good luck, and please keep us posted.

 

-- Tim

[LaRetta]

When PSoS kicks off a server-side script, the server essentially opens up a client session that session "inherits" the account / privilege set of the user that initiated it. However, from what I can tell, the user's credentials are not honored in anything other than the database the script was started from. In other words, if the database has external references, then they will fail because the other databases will reject the credentials (even if the user has an identical in the secondary databases). So if my theory is correct, PSoS cannot reference external datasources. 

 

Hi Tim,

 

So separated mobile which holds EasySync in the UI referencing the data file in its TOG will fail in downloading changes from served data (when using PSoS), is that your understanding?  

[kmtenor]

Thanks, Tim.

 

 

Turning off PSoS on the mobile database settings script appears to have done the trick.  I would say your assessment is right on that there appear to be some issues with how credentials get passed from DB to DB, particularly when PSoS is involved. Hopefully, the good folks at Filemaker know about this and will be able to fix it sometime soon.

 

To LaRetta's latest point, I suspect I would have been fine if there hadn't been credentials getting in the way, even with PSoS turned on and remote TOs being sync'd.  As is so often the case, security comes at the cost of simplicity.

 

For the record, I didn't mean to blame EasySync for the problems I encountered - I just meant that keeping things "easy" would imply that we should keep one mobile database syncing with one hosted database, and call it a day, without involving remote TOs.  Clearly, you intend to be able to support that scenario, and it appears to work just fine but for a filemaker glitch that none of us can overcome on our own.  Sorry if that came off wrong.  I am over the moon that your product is out there and so well thought out.  I have tons of applications for it that are just waiting to be built that will utilize your solution.

 

Thanks again.

 

-Kevin

[timdietrich]

LaRetta - In your case, I think you'll be okay. If I'm understanding you correctly, you have two files on the mobile side, one for the UI (w/ EasySync in it, too) and one for the data. Assuming that hosted file has no external references that you are trying to sync to, then you should be able to sync using PSoS.

 

Kevin - I'm glad you got it to work using Perform Script. I know it's not an ideal solution. There are so many performance benefits that come with using PSoS, and it's a shame that it can't be used in your type of configuration. 

 

I'm not sure if FileMaker is aware of the issue or not. I've seen similar behavior with CWP solutions that use the FM API, and its behaved that way for years no. I think that there may be security-related reasons for not passing credentials on the server-side in the ways that we expect them to be passed.

 

Also, there is a lot of "weirdness" with the client sessions that get spawned with PSoS, especially with regard to caching. That was why I ended up pulling the plug on "ServerSync" back in June. I had the project very far along, and then started running into a lot of issues. If you're interested, here's a blog post about what I ran into: http://www.timdietrich.me/blog/serversync-pulling-the-plug/

 

One final note: I think that if there's any benefit to using Perform Script over PSoS when syncing, it's this: Debugging is considerably easier!

 

-- Tim

[LaRetta]

Thanks, Tim!  It seemed to work fine on Mac so I wanted to make sure I wasn't missing something.