Data security of accdb files

[Josef P.]

What is the purpose of hiding tables?

 

[The_Doc_Man]

Security by obscurity is a common method that deters the random hacker looking for low-hanging fruit, but wouldn't stop a targeted hacker with some kind of specific goal in mind. Hiding tables using the deep-hide method means extra work and the casual hacker doesn't want that. The casual hacker is a smash-and-grab artist who only takes what isn't secured.

 

[isladogs]

Many thanks Josef. Will look at the latest tomorrow morning.

You may want to add to the questions to Microsoft what I show in #22 above.

@JohnPapa
You didn't reply to my post #23 so I assumed it was working correctly for you
I cannot replicate what you're showing in post #22.
Are you able to do a short video to demonstrate how this occurs ...or possibly can we setup a short Zoom/Teams meeting next week

 

[JohnPapa]

I was doing a video, per your request, when I discovered that the tables that show in the navigation pane are the linked tables that are not hidden.

Does this make sense?

I will hide the linked tables and will let you know, if they still appear. Most likely they will not appear.

 

[isladogs]

I was doing a video, per your request, when I discovered that the tables that show in the navigation pane are the linked tables that are not hidden.

Does this make sense?

I will hide the linked tables and will let you know, if they still appear. Most likely they will not appear.

Yes it makes sense. In fact its what I wrote in post #23

 

[JohnPapa]

The problem is that with linked tables from an Access backend, you can't remove the password.
Therefore, in the example I link the tables from an unprotected backend (file path must be the same as the protected BE has later).

Hi Josef,
In my case I have a db with no password and I need to add a password and at some point in the future I may want to remove the password or change the password.

To do this I would need to have an unprotected BE, which I guess I could create by taking the protected BE and directly removing the password, right?

One of my software packages (www.VisualDentist.com) is as big as it gets, when it comes to code and I thought of adding code that would take care of the case when the DB is password protected. In other words if there is no password, as is the case now, execute the current code and if there is a password execute the code that takes care of the password.

Is the password stored in the DB connection and/or the links and do I need to do a relink of the linked tables as per your example?

Edit:
Is this enough for the connection?

Set db = DBEngine.OpenDatabase(BackendPath, False, False, ";PWD=password")
Set ConnectDAO = db

 

[JohnPapa]

Also Josef, I do a lot of relinks, because during new installs the software opens in a specific folder and then I relink to the server.

Do I need each time to use an unprotected DB to do the relinks. If yes, it would be very time consuming.

 

[Josef P.]

As already written: if you want to save the linked table without password, you have to link to an unprotected backend.
Alternative: MSFT will fix it

Alternative 2: use subst (command shell)
before link tables:
subst X: Y:\our\unproteced\dummy\BE.accdb => file path to BE = X:\BE.accdb
after linking:

subst X: /D
subst X: Y:\our\proteced\BE.accdb

 

[isladogs]

John/Josef
I don't understand why this is becoming so complex with multiple versions of your db
Why can't you just have code that handles both when there is a password and when there isn't?
For example, something like this snippet from one of my apps

          'link to external MSysObjects table in database located at strFilePath
200       DoCmd.TransferDatabase acLink, "Microsoft Access", strFilePath, acTable, "MSysObjects", "MSysObjectsEXT"

            'open external database so don't need to keep re-entering password (if any)
             'get connection string from existing record in local MSysObjects
210            strConnect = Nz(DLookup("Connect", "MSysObjects", "Database = '" & strFilePath & "'"), "")
                      
220             If strConnect <> "" Then
230                 Set ExtDb = DBEngine.OpenDatabase(strFilePath, False, False, strConnect)
240             Else
250                 Set ExtDb = DBEngine.OpenDatabase(strFilePath)
260             End If

After that I loop through and link to each of the tables in the external database

What am I missing here?

 

[Josef P.]

@Colin: How to prevent the password from being stored in the frontend when linking tables from a protected Access backend?

@John:
Alternatve 3:
dont use linked tables ... replace it with queries
select * from [MS Access;Database=Y:\path\to\BE.accdb].TabName

 

[JohnPapa]

Why can't you just have code that handles both when there is a password and when there isn't?
For example, something like this snippet from one of my apps

Colin
That is what I mentioned above
"In other words if there is no password, as is the case now, execute the current code and if there is a password execute the code that takes care of the password."

Josef
I do not understand what you mean by
"dont use linked tables ... replace it with queries". Can you please explain.

 

[isladogs]

@Josef P.
Ah, now I see.
I use disconnected ADO recordsets for this purpose then there are no linked tables visible in MSysObjects
I'll study what you've been suggesting

As for the queries, won't you need:
SELECT * FROM tblName IN '' [MS Access;PWD=xxxx;Database=FullPathToBE.accdb]
OR
SELECT * FROM [MS Access;PWD=xxxx;Database=FullPathToBE.accdb].tblName

 

[Josef P.]

Query: use connection string without password => data not readable
+ open connection to backend in frontend (e. g. after user login) to allow read data from query.

Try with attached example:
1. open form 'Connect2BE' to unlock data (and relink query)
2. Open query Tab1 or Form1 => data can be read
3. close Tab1 and/or Form1
4. click disconnect in form 'Connect2BE'
5. try open Tab1 => message 'Not a valid password'

Unfortunately, you can make the password visible without knowing the password if you create a linked table in the FE while the connection to the backend is open.
I consider the automatic appending of the password to the TableDef.Connect property when linking an Access table to be a design flaw in Access.

I use disconnected ADO recordsets for this purpose

This is the best way. The ADODB recordsets do not have to be unbound at all.
Unfortunately, you cannot use recordsets for reports.

 

[JohnPapa]

Query: use connection string without password => data not readable
+ open connection to backend in frontend (e. g. after user login) to allow read data from query.

Try with attached example:
1. open form 'Connect2BE' to unlock data (and relink query)
2. Open query Tab1 or Form1 => data can be read
3. close Tab1 and/or Form1
4. click disconnect in form 'Connect2BE'
5. try open Tab1 => message 'Not a valid password'

Unfortunately, you can make the password visible without knowing the password if you create a linked table in the FE while the connection to the backend is open.
I consider the automatic appending of the password to the TableDef.Connect property when linking an Access table to be a design flaw in Access.


This is the best way. The ADODB recordsets do not have to be unbound at all.
Unfortunately, you cannot use recordsets for reports.

Josef, while interesting, I use linked tables and cannot switch to something else right now. My software started in the DAO days and then I adopted ADO as much as possible. Hence the many references to

Dim db As Database
set db = CurrentDB

I have looked extensively for a way to use CurrentDB and specify a Password, but could not find such a command, such as

Set db = DBEngine.OpenDatabase(BackendPath, False, False, ";PWD=password")

Do you know if such a command exists, which uses CurrentDB?

 

[isladogs]

Thanks for the example. Yes I can see it works. For some reason I'd never tried doing that with a query

I've asked the A-team more than once for the passwords to be removed (or at least masked) in the Connect field, particularly for ACCDE files.

I use a form as a subreport to circumvent the issue that disconnected ADO recordsets were never made available for reports.
This is from my Encrypted Split No Strings web page:

 

[JohnPapa]

I believe I solved my problem.

I have a routine which loops through all linked tables and relinks the table to the specified DB.

I added a password to the BE and I ran my program and surely enough it recognized that it could not link to the specific DB. The only thing which I had to do is change

db.TableDefs(intCount).Connect = ";DATABASE=" & Me.File1

to

db.TableDefs(intCount).Connect = ";DATABASE=" & Me.File1 & ";PWD=password"

The software works and if I try to open the BE, I am prompted for a password.

Am I missing something?

 

[Josef P.]

The software works and if I try to open the BE, I am prompted for a password.

And if you forget the password, read the value of TableDef.Connect of a linked table in the frontend and you have the password again.

[OT]
@ Colin:

I've asked the A-team more than once for the passwords to be removed (or at least masked) in the Connect field, particularly for ACCDE files.

This is possible with linked ODBC data sources. I don't understand why this is not done in the same way for linked Access tables.
On the other hand, I don't care because I don't use an Access backend.

I use a form as a subreport to circumvent the issue that disconnected ADO recordsets were never made available for reports.

I would be satisfied with connected recordsets, they don't necessarily have to be disconnected. Which should be possible in principle, since it used to work in an ADP.

 

[isladogs]

I would be satisfied with connected recordsets, they don't necessarily have to be disconnected. Which should be possible in principle, since it used to work in an ADP.

The example app I'm referring to was designed to be locked down as much as is physically possible.
Originally, I used a simpler method which it was possible to crack (as demonstrated by Philipp Stiefel at the Virtual Access Cascade conference in 2020). The video is available here:

Hence my change to using disconnected ADO recordsets - MUCH more secure

 

[Josef P.]

If I use an ADODB recordset, I connect it directly to the backend via ADODB.Connection (see attachment FE-100.accdb).

How do I get the password from the outside with this variant?
Of course, you can use the form recordset and read out the data. But you can do that in exactly the same way with a disconnected recordset.

 

[isladogs]

Hi Josef
I haven't time to investigate in depth this week, but you appear have blocked all obvious hacks using VBA.

However, there are always other methods
The approach I used below works even if the VBE is locked with a password

I'm not going to explain that in a forum thread for obvious reasons but happy to discuss privately