By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,221 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,221 IT Pros & Developers. It's quick & easy.

Security hole

Expert 100+
P: 218
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
Dec 14 '06 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,660
You can set the security on SQL server so that only an Application can see the tables. This would mean you'd have to use Application security in SQL Server which I only know of indirectly (never used it myself).
I'm lucky in a way - my users couldn't pick their own nose without assistance, so my security doesn't have to be all that much.
Dec 14 '06 #2

Expert 100+
P: 218
Hi NeoPa

You can set the security on SQL server so that only an Application can see the tables. This would mean you'd have to use Application security in SQL Server which I only know of indirectly (never used it myself).
I'm lucky in a way - my users couldn't pick their own nose without assistance, so my security doesn't have to be all that much.
I'm not sure that using Application level security will work, as Excel is using the ADE security, but I will certainly check it out.

Currently thinking about the folowing:-
  • set up the ADE with a connection through a user with VERY restricted access permissions to SQL
  • on loading the login form, set up a connection via a high security user.....
Dunno if it'll work yet;-)

Thanks again

Steve
Dec 14 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Not a problem Steve.
You would have to use SQL Server security then - rather than linked Domain security (I can't think of the proper name for that but it depends on your Domain logon).
Dec 14 '06 #4

Expert 100+
P: 218
Hi NeoPa


You may be interested in the route we have taken...
  • Create a project custom property to hold the (encrypted) connection string;
  • In VBA code, ensure that Persist Security info is turned OFF;
  • 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);
  • 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);
  • 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....

Steve
Dec 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
Nice one Steve.
I'm glad you got there - I don't think I could have taken you that far.
Dec 16 '06 #6

Expert 100+
P: 218
NeoPa

Do I get extra Brownie points for obscurity???

PS Merry Xmas to all you experts

Steve
Dec 18 '06 #7

NeoPa
Expert Mod 15k+
P: 31,660
NeoPa

Do I get extra Brownie points for obscurity???

PS Merry Xmas to all you experts

Steve
Absolutely, but I'm afraid your post-count stays the same :(.
Dec 18 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.