Linking Access Front-End to SQL Back-End in a Interesting Environment

We currently have a split Access database where we need to move the back-end to a SQL database.  What makes this tricky is we have an interesting network environment.  We have satellite offices in other locations.  Some of these offices were acquired in mergers, and are currently on separate networks (the networks have not been consolidated yet).

Most of our users are on the “main” network where the SQL server resides, but some of our employees in these satellite offices are not.  They have user credentials for the “main” network, but seldom use them (this database will be one of the few things they will need to access on the “main” network.

So, I have been trying to figure how to set up the ODBC connections for them so they can seamlessly connect to the SQL server on the “main” network and use the database.  I have played around with various different options (User DSN, File DSN), but have not had the success I desire.

My initial hope was to set up a File DSN, so I didn’t need to manually create User DSN connections on each and every workstation.  However, I ran into issues there.  These “satellite” users have to use SQL Server Authentication, since the Windows Authentication they are logged in under is on a different network (and therefore different credentials).  

We don’t really expect these “satellite” users to remember their credentials on the “main” network, so we created a general user account on the “main” network with an unexpiring password that has access to the SQL Server database we need.  So the thought was to use these credentials in the SQL Server Authentication of the ODBC connection.  However the issue we have run up against is that regardless if we try a User DSN or File DSN, the password is not remembered, so it always prompts the user for the password every time they try to access a new table during their session.  Obviously, that is not going to fly.

So I am not really sure what to do here.  Does anyone have any ideas that would work in our scenario?  

I fully expect to have two different front-ends, one for the “local” users and one for the “satellite” users.  I don’t think the local users will be a problem, as they can just use Windows Authentication.  But these satellite users situation is really causing me some headaches here.

Note.  I started a discussion here on this topic, but have seemed to run into a dead-end (in fairness, the person did answer my original question, but I added on to it).

Thanks