We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

question about Securing Access Database

Medium Priority
533 Views
Last Modified: 2012-05-11
We have a commercial application which is going to be distributed to customers.

it's split front end/back end using SQL server as the back end. We have encrypted server traffic and hashed passwords etc so that a user cannot connect to the dbms using external tools and their password and inspect data. Some of the database contents are quite confidential.

When our app is not running the table links are destroyed (last thing it does before it shuts down).

However, I am disturbed by the fact that it is possible to create an access database and link to a system/hidden table in the running application accde file and discover the encrypted passwords in the connection strings of msysobjects.

We need persistent connections. We have a database design with >300 tables and it's highly integrated so we use tables all over the place. With north of 100,000 lines of code I don't think we can re-architect to connect and disconnect dynamically and in any event that would comprise a significant performance overhead.

Any advice from anyone for ways to lock this down? I've been searching but no luck so far.

Thanks , rj8820
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Any advice from anyone for ways to lock this down? I've been searching but no luck so far.>>

  The use of Trusted Connections for SQL Server security would remove the need to embed a username/password in the connection string.

JimD.

Author

Commented:
Sorry but we have kept away from this for two reasons:

most importantly using trusted connections means that any user can either write their own data access databases against the server, connect to the database and play, or they can download tools like SSMS and connect to the server using a trusted connection and inspect the database from their own windows account. This is a clear security problem we have to avoid, and

second, not every client technical environment would support trusted connections for other security reasons (i.e. in our banking clients)

any other thoughts? Why would microsoft allow this at least without providing the ability to block it..?

Author

Commented:
So I have a corollary question: if in access you set the open mode to exclusive does that affect linked sql server tables>? or just the local  tables? i.e. if I open in exclusive mode does that prevent external users from connecting or does it mess up the linked table connections too by making them exclusive??
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<most importantly using trusted connections means that any user can either write their own data access databases against the server, connect to the database and play, or they can download tools like SSMS and connect to the server using a trusted connection and inspect the database from their own windows account. This is a clear security problem we have to avoid, and >>

  While that's certainly true, SQL Security is also in place; they can only do the things you allow them to do.  If they don't have access to a table, then they still won't get access even if they by-pass the app.

<<second, not every client technical environment would support trusted connections for other security reasons (i.e. in our banking clients)>>

  I may be naive, but I thought trusted connections were pretty secure.  If I'm authenticated to the domain, and I can trust that, well that doesn't seem to say much then for windows security.

<<any other thoughts? Why would microsoft allow this at least without providing the ability to block it..? >>

  LSM has given you a couple, but Access is inherently insecure.  considering that you've already encrypted the passwords, I'm not sure how much further you can go.

<<When our app is not running the table links are destroyed (last thing it does before it shuts down).>>

  I'm wondering what happens on an abnormal disconnect and your DB doesn't have a chance to delete the links...

JimD.

Author

Commented:
to LSMConsulting: thanks, that's an interesting line and I'll test that one out.

to JDettman:  yes the point on abnormal terminations is one that occurred to us but I figure is you can find some fix for the remote system table connections then it actually wouldn't matter too much if you lef tthe links in tact. Our testing revealed that you cannot connect to a linked table from the outside. You can connect to a local Access Jet managed table but not to one which is linked to a server.

We will do two things tomorrow: first test the exclusive access idea, second to test the password encrypted DB with a launcher.

Does anyone know how secure accde files are generally in terms of the code: is it reasonable to assume that the code cannot realistically be traced or decompiled in any way?? I just want to know if we write convoluted code to supply the password wether it can easily be cracked. I realise most things can eventually be cracked given enough time and brain power but how about accde?

Author

Commented:
By the way JDettman, your point about data security and giving database roles is taken but we tried that and we ended up with more than 150 database roles with all the permutations and combinations and access needs to be manageable by a user admin and on a functional basis not data basis as we have an extremely integrated data model. The other things are, 1. the database structure is an important part of our IP: we don't want anyone having casual access, and 2. ANY ability to dick around with the data in the database potentially creates significant support issues for us so we just wanted to slam the door shut - which our customer DBA's would prefer too.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Does anyone know how secure accde files are generally in terms of the code: is it reasonable to assume that the code cannot realistically be traced or decompiled in any way?? >>

  Only one outfit has done it to my knowledge and you must prove ownership before they will provide source from a MDE.

JimD.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'll go out on a limb and say that choosing Access + linked tables for this sort of application was probably not a wise choice. I realize that these things tend to outgrow their initial intended use, but at some point you've got to say "I've done all I can with the Access platform, might be time to move to a more secure one where I can do away with links and such." If you are truly concerned with data security it would be time to do away with linked tables and handle your data entirely via classes or some similar concept. This does away with a LOT of data security issues, at the expense of a LOT of programming time.

Author

Commented:
LSMConsulting, of course you're right and I agree - but I always agree with 20/20 hindsight. We have  a plan to migrate off Access on to a web-browser based platform as soon as we are able to afford the effort but in the meantime we are where we are and I'm grateful for your pragmatic assistance.

Author

Commented:
So thanks to all.. we have test two things: the password encryption idea with a launcher and opening the database exclusively. We have arranged for the launcher to derive the password algorithmically so it is never stored in the launcher anywhere (other than in memory) and the launcher is also password encrypted itself. The launcher also makes sure that Allowbypasskey, openmode, and other options are actually set correctly in the main app too in case anyone figures out how to change them without the password.

Opening the accde in exclusive mode does not impact linked tables. Concurrency for linked tables is defined and managed by the server of course.

So we will be using both these two things together and I think that will be pretty tight.

Thanks for your comments guys...

Cheers, RJ
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
< off Access on to a web-browser based platform >

web-based apps are inherently less secure than desktop-based solutions, so you might be jumping from the frying pan into the fire. Be careful and review thoroughly before making a decision to do that.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.