Using Access 2003 ADE with SQL Server (various flavors)

Our Access ADE is locked down, so that users have to enter via the usual
username/password route.
No-one can see the project window (database tables, queries, etc) and all
updating is done through forms.

BUT>>>>

If a user loads up Excel, he can go Data -> Import External Data -> Import
Data, whereupon all our backend tables, etc pop alarmingly into view!!!
Aaaaargh

Other developers must have come across this 'feature', so if you have any
suggestions as to plugging the leak I would be very pleased to hear them.

The only idea so far is to remove the Get External Data option from Excel

TIA

Steve

CyberDwarf wrote:
Show quoteHide quoteSQL Server tables can be accessed from LOTS of programs just like the tables
from any other database server.  That is not a security leak, but rather the
way it is supposed to work.  You solve the problem by applying security on
the server.  Then people can only see the tables that they have permissions
to.

If your practice has been to leave the tables wide open on the server and
try to control permissions on the application side then that was your
mistake.  You can never assume that your program is the only one that will
try to connect to the database.

Rick

Thanks for your rapid response.

Of course, you're right about that. It's just that this program has run in a
very restricted desktop environment up until now (i.e., it was the ONLY app
on the desktop!!)

Guess we'll just have to think about implementing SQL Server level security
for this new situation!!

Steve

You may be interested in the route we have taken...
  a.. Create a project custom property to hold the (encrypted) connection
string;
  b.. In VBA code, ensure that Persist Security info is turned OFF;
  c.. Make sure sure that all Windows users have no rights to access SQL
Server (ie, they won't be able to use a Trusted Connection);
  d.. When the ADE starts, we look up the required connection info from the
custom property, connect to SQL Server using the required access
permissions, then remove the Persist Security setting (the project loses the
password);
  e.. At this point we have our required connection and no-one else can
access the backend via our ADE, unless they know the admin logon &
password).
That's basically it!
We've done basic testing & it seems to work....

Object permissions
Is someone hacking into our database?
Office 2000 Access 2003 Exclusive Open Rights
Front end, Back end, and restrictions on which Forms can open.
Design Master Unavailable in Replica Set
asswords
Using Access over the Internet
Ability for a non-Admins user to create new users
Prevent Multiple Unauthorized Login Attempts
help, I enabled the security wizard