Password security for linked tables in Access

My Microsoft Access 2007 application is split into a front-end program and back-end database. The front-end has access to the backend through linked tables.

Whenever I password protect the back-end database the tables can be linked if the proper password is supplied. However, the password is apparently stored for subsequent access as the linked table can be opened without any further password prompt, even after exit & restart.

I could delete all links during program exit and recreate them during startup; however I am wondering if there is a conceptually better solution where the password is not stored in the linked table definition; in particular you could imagine a crash or dead battery through which the program exits prematurely and leaves all data exposed through the front-end linked tables.

Thanks for any suggestions or eventual misunderstandings on my side on how this actually works.

Valérie



valerieschupbachAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In this scenario, the password is stored in the Connection String (link).  So, as you've discovered ... when the MDB is open ... anyone can view data who may have access to the database container (window).

The one scenario that would get around this ... but requiring considerable more code and redesign ... would be to *not* link at all, but instead use the OpenDatabase Method in vba code (via DAO) for all data accessing operations, including creating Record Source's for forms, combo boxes, etc.  Depending on your app, this could be a significant change.

This is the only scenario I am aware of to avoid this situation.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
On minor thing you can do, although it doesn't really seriously protect anying in this case is the following:

It turns out if you prefix any table (or other object) with USys (similar to MSys) ... and then Tools>>Options (in A2003 - similar in A2007) ... you uncheck (show) System Objects ... at least the linked tables will be hidden in the database window.  And in conjunction with this, you can alias any linked table to any name you like. Of course, you would have to follow through with the name change in all objects (queries, etc) that reference these tables.

And/Or ... you can set the Hidden Property of any table to Yes ... and then again in Tools>Options uncheck (show) Hidden Objects.

mx
0
 
Kelvin SparksCommented:
The other thing you need to be aware of, is that the password is stored in the MSysObjects table. If users know how to get to that and can read the table,  the password for each table is there for all to see!!
I do not believe however, that the back end database remains unlocked if you front end crashes out. Each connection to it will require the password - either in a connection string, or entered to open te back end directly.
Kelvin
 
0
Firewall Management 201 with Professor Wool

In this whiteboard video, Professor Wool highlights the challenges, benefits and trade-offs of utilizing zero-touch automation for security policy change management. Watch and Learn!

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ummm ... as I understand in, the entire database password paradigm for A2007 has changed ...and the password is no longer stored in the database as it was in all previous versions ...and EASILY hacked with many free tools.  I believe it is now stored in the Registry (or elsewhere) and is supposed ... much more secure.

mx
0
 
Kelvin SparksCommented:
So it appears!!, I don't have too many 2007 dbs to worry about, but just checked one. The connect field remains blank for Access linked tables, BUT still had the SQL linked tables connection string details! That could well be a result of my dsn-less connection code which may need revising for 2007. But most of these clients are heading toward Access 2010 as fast as they can, so it may be different yet again.
 
Kelvin
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One alternative would be be to move your data to SQL Server, which has a much more robust security mechanism than does Access. Access provides an upsizing wizard for this, or there are other free tools out there which will move your data over to SQL Server Express (which is free).
0
 
BitsqueezerCommented:
...and to extend the recommendation of LSMConsulting: You can use Windows Integrated Security in SQL Server so you can create logins only for users who should work with the database (it doesn't make a difference if SQL Server Express is locally installed or on a network). In this case you can link the tables with a connection string which allows only Windows Integrated Security and you don't need to save any password anywhere because if a user logs into Windows he automatically has entered the needed password. If another user on the same machine logs in he cannot open the database no matter if SQL Server is locally installed or on the net. And he cannot hack any password because it is saved nowhere. SQL Server Express can handle databases up to 4 GB so it should be no problem to convert any Access database (with a maximum of 2 GB) to SQL Server express.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.