Security hole

[cyberdwarf]

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

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

[cyberdwarf]

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

[NeoPa]

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

[cyberdwarf]

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

[NeoPa]

Nice one Steve.
I'm glad you got there - I don't think I could have taken you that far.

[cyberdwarf]

NeoPa

Do I get extra Brownie points for obscurity???

PS Merry Xmas to all you experts

Steve

[NeoPa]

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