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:
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.



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

SQL 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 Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Rick

Thanks for your rapid response.

SQL Server tables can be accessed from LOTS of programs

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