Data security of accdb files (1 Viewer)

[Josef P.]

I can recreate your screenshot with an accdb frontend. With an accde frontend I can't find the password.

Note: I do not want to show all possible methods for cracking passwords here, but only question some protection mechanisms that can be bypassed even with VBA itself.

For example: protect backend + frontend with password + use linked tables in FE => 0 protection, because you have to open the frontend for the user. And then you can access the linked tables (unfortunately) from the outside.

But you don't have to make every application extremely secure either.
I think you have to distinguish whether you only want to secure the data against third parties or whether you also want to prohibit users from directly accessing the tables.

 

[isladogs]

Its also possible with ACCDE files

I agree with your comments about some protection methods. Any security is only as good as its weakest link

For the vast majority of cases, the most important thing is just to prevent end users causing problems, often unintentionally, by accessing things they have no need to access. Standard methods of locking down applications will also deter most casual hacking

Preventing experienced and determined hackers accessing databases is much more difficult.
In most cases the additional security needed to limit such exposure is over the top.
Where data is very sensitive, it shouldn't be stored in Access anyway.

 

[JohnPapa]

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.

In other words you are politely saying that it is not secure? To view the TableDef.Connect of a linked file in the FE .accde, to I use Access to link to the FE .accde, while the software is running?

Also, if you know how secure is the Password field in a table?

You can send me a PM if you prefer.

 

[Josef P.]

Do you mean a text field with input mask "Password"?
=> SELECT PwdField, [PwdField] & "" AS PwdPlainText from ...

However, passwords can easily be stored by saving only the encrypted password and comparing the encrypted results.
e. g.

if md5(strInputPassword & SomeStringPrefix) = DLoopup("SavedPassword", "PasswordTable", "UserName='username'") ....

 

[JohnPapa]

Do you mean a text field with input mask "Password"?
=> SELECT PwdField, [PwdField] & "" AS PwdPlainText from ...

Many thanks Josef for the reply.

Did you reply to my previous comment
"In other words you are politely saying that it is not secure? To view the TableDef.Connect of a linked file in the FE .accde, to I use Access to link to the FE .accde, while the software is running?"

 

[JohnPapa]

Adding a db password to the BE is ok. The problem is connecting to it via code.

Without a password I use something like
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath where strPath = is the pathname to the BE

If my password is "1234" do I need to use something like
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath & ";PWD =" & strPassword where strPassword = "1234"

After adding a password to my BE, it seems that I need to add what you mention above in the FE, for example
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath & ";PWD =" & strPassword

Do I need to include this code in every form, whenever I try to access data from the BE?

 

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

Hi Josef,
In a new project which I am starting right now, I will try to use Colin's suggestion of disconnected ADO recordsets.

1) In the example you provided in #53, to connect to the DB you use the following at the outset instead of using in every query

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

Isn't there a security problem in a specifying the PWD in this case?

2) Is there a corresponding ADO command to open the db?


I have used disconnected recordsets (datasets) in the Cloud version of my project (www.VisualDentistCloud.com) and we had to create subs and functions for SELECT, UPDATE, DELETE etc. In other words if I want to delete a record I pass its ID together with the Table name to this sub/function and the same for the other actions.

EDIT: THIS WAS DEVELOPED ON VISUAL STUDIO AND USES SQL SERVER ON AZURE (NOT ACCESS)

Colin: Are any of these subs/functions available?

Also, since Reports do not read recordsets, can't the recordset be converted to a table and the report get the data from the table?

 

[JohnPapa]

Replying to my question above

2) Is there a corresponding ADO command to open the db?

Apparently there is no corresponding ADO to DAO.DBEngine, so the DAO.Engine could be used.

 

[Josef P.]

As soon as you open a DAO connection to the access backend, the password can be read. (See my example files in this thread).
I think it is a bug in Access that a password is stored in a TableDef even though it is not specified in the Connect string.

2) Is there a corresponding ADO command to open the db?

ADODB:

dim cnn as adodb.connection

set cnn = new adodb.Connection
cnn.open .... (oledb connectionstring to Access backend)

 

[JohnPapa]

As soon as you open a DAO connection to the access backend, the password can be read. (See my example files in this thread).
I think it is a bug in Access that a password is stored in a TableDef even though it is not specified in the Connect string.

So there is an issue with security?

 

[Josef P.]

From my point of view, yes, Microsoft may have a different opinion.

 

[JohnPapa]

After adding a password to my BE, it seems that I need to add what you mention above in the FE, for example
db.TableDefs(intCount).Connect = ";DATABASE=" & strPath & ";PWD =" & strPassword

Do I need to include this code in every form, whenever I try to access data from the BE?

Josef: If you know do I need to include the

As soon as you open a DAO connection to the access backend, the password can be read. (See my example files in this thread).
I think it is a bug in Access that a password is stored in a TableDef even though it is not specified in the Connect string.


ADODB:

dim cnn as adodb.connection

set cnn = new adodb.Connection
cnn.open .... (oledb connectionstring to Access backend)

Do I need to open the connection only when the application opens and not every time data is manipulated (for example in every form)?

 

[isladogs]

So there is an issue with security?

Yes as both Josef and I have said more than once in this lengthy thread.
Some time ago I sent the Access team of suggestions for improving the security of Access files, particularly for ACCDE files.
You can find most of these in my article

Additional Security in Access Databases

This is a list of suggested changes that could help make Access databases much more secure

www.isladogs.co.uk

Whether any of those will ever be implemented is another matter

 

[Josef P.]

Do I need to open the connection only when the application opens and not every time data is manipulated (for example in every form)?

In a nutshell: once the DAO connection to the BE is opened and kept open, there is no need to reopen it every time ... but then in the case of an open DAO connection, you can always find out the password from outside.

Please try this out with the examples I have provided.

 

[JohnPapa]

Thanks Josef. I have seen your examples which are very useful. Maybe opening the connection, getting the data and closing the connection is safer, than keeping the connection always open.

 

[Josef P.]

From a practical point of view: as long as the data in the DAO-bound form is visible, a connection is open and therefore the password can be read. Making the backend connection before opening the form and closing it after closing the form doesn't do much. It just makes everything slower and you will mostly show some data in a database application. => only using an ADODB recordset makes it a bit safer.

But: do you have to protect the application from the users so securely at all?
If that is necessary, I think an Access backend - and maybe even the Access frontend - is the wrong choice.

 

[JohnPapa]

From a practical point of view: as long as the data in the DAO-bound form is visible, a connection is open and therefore the password can be read. Making the backend connection before opening the form and closing it after closing the form doesn't do much. It just makes everything slower and you will mostly show some data in a database application. => only using an ADODB recordset makes it a bit safer.

But: do you have to protect the application from the users so securely at all?
If that is necessary, I think an Access backend - and maybe even the Access frontend - is the wrong choice.

As I mentioned, I have the same application on the desktop with Access and on the cloud, developed with Visual Studio, and deployed with SQL Server on Azure.

I have switched a long time ago to ADO (although there are many DAO remnants in my code, for example being able to search for more than one items, which ADO cannot handle directly) and intend to try to use ADODB recordset, in my new project, which has to do with Building management and before you mention the obvious, that there are many packages available, there is a special need because of specific legislation that will be passed in a year from now.

It was mentioned that the ADODB recordset does not work with reports and I suggested somewhere in this thread, whether is would be a good idea to read the recordset to a temp table and base the report on this table and never received a reply.

In any case, many thanks for your assistance so far.

 

[isladogs]

I thought I answered that question. To use ADO record sets with reports I suggested using an unbound report as a container and a form holding the data as a sub report

 

[JohnPapa]

Many thanks, I had though you meant you used the form instead of a report. My mistake.

 

[JohnPapa]

Hi Colin
I have been trying to use ADO connection string instead of linked tables.
I have been able to connect and bring over a recordset and display it on the form. To create the fields I linked the table and used the form wizard to create the form. Is this acceptable.
Once I assigned the recordset I was not able to modify the form contents. Do I need to open the recordset in a certain way to allow this. Once I alter the form contents I am guessing I would need to use INSERT, UPDATE statements.

Also, in MsysObjects the password still appears. I came across the following code which had your input, since the password was isladogs and the error message which I received. Please see attachment. Is there a way to prevent the password from appearing in MSysObjects