[X]My Assistant

[Louverril]

Hi,

I have the latest version of Office 365 (inc Access) and just checked some work I started a couple of weeks ago on security - see https://www.UtterAccess.com/forum/index.php...2048131&hl=

I can no longer see the connection strings in msysobjects anymore - they are all blank:

 msyssnipIs.JPG ( 32.08K )Number of downloads: 6


This is what I was seeing:
 msyssnipWas.JPG ( 13.65K )Number of downloads: 0


In any of my databases I can't see the connection strings.


These are all linked tables - I trebled checked! Am I getting far too excited or has something changed?

[dashiellx2000]

Is your application still working as you need? Normally I find very little need to look at the system tables.

[GroverParkGeorge]

"... checked some work I started a couple of weeks ago on security..."

So, do you think there's a correlation here? Did you actually make changes that impact Connection Strings?

[Louverril]

Thanks Both for responding.

I have worked it through. It is the length of the password.

A three character PWD is shown but a 20 (the maximum to set/use via VBA) is not shown

Please see the Front End and two BEs attached. If you place them both in C:\Test and open the msysobjects in the Front End you will see:

[attachment=89319:msyssnip...ShortPWD.JPG]

 msyssnipTestLocation.JPG ( 23.96K )Number of downloads: 1


DBTestEncENCRYPTENCRYPT123420.accbd has password ENCRYPTENCRYPT123420

and DBTestEncxyz.accdb has password xyz

So it looks like if you use a long password (not sure what the cut of is 12 or 14? maybe) it does not show. Which makes the connection a hec of a lot securer?

Attached File(s)

 msyssnipLongVShortPWD.JPG ( 50.99K )Number of downloads: 0

 

[Louverril]

Sorry made a hash of the attachments.

Here are the sample db's mentioned above - more passwords of differing lengths are shown.

Only the password of 20 characters is not shown.

If you place ALL both in C:\Test and open the msysobjects in the Front End you will see:

 msyssnipLongVShortPWD.JPG ( 50.99K )Number of downloads: 0


 DBTestFrontEndLinkedToEncBE_REV.zip ( 1.59MB )Number of downloads: 1



Why 20??
This post has been edited by GroverParkGeorge: Feb 2 2019, 10:50 AM

Reason for edit: Replaced attached file

[isladogs]

Sorry to say that I'm going to rain on your parade
In your excitement you forgot to check something crucial!

You are indeed correct that you cannot see the connection string for your 20 character password in MSysObjects
But if you click on that linked table you will get a 'Not a Valid Password' error (checked in both A2010 & A2019(365)

The MAXIMUM length of a password is 14 characters EXCEPT in A2007 where it was 20 characters
This is from the Access 2016 specifications file

 Capture.PNG ( 9.57K )Number of downloads: 0


Did you create that password in A2007 before upgrading to Office 365?
If you did you need to shorten it to 14 characters

However as a test, I've just created a test file of my own to see if I could replicate your issue
Access will also let me create a BE password of 20 characters
It will allow me to link a table in that encrypted BE to an FE file
I get the same blank connection string and the same not a valid password error.
The linked table is unuseable

To my mind this is a bug that needs to be reported to MS

I shortened the password to ENCRYPTENCRYPT (14 chars) and relinked
MSysObjects shows MS Access;PWD=ENCRYPTENCRYPT; and the tabe can be viewed

Sorry to be the bearer of bad news.

EDIT: This crossed your last reply. Could you check whether the tables linked to 15 char & 19 char passwords can be opened
This post has been edited by isladogs: Feb 1 2019, 11:50 AM

[GroverParkGeorge]

Thanks, I'm going to share this thread with the Access team to see what they think.

[Louverril]

Hi Isladogs,

Dash...


Yes they can but the 20 cannot :-(((
 1519PWDand20Error.JPG ( 32.44K )Number of downloads: 1

[isladogs]

Thanks George

Loverrill
I'll have a look at your newer file & will let you know if I find anything useful
For now I would urge you to stick to passwords with 14 chars MAX

BTW - you are mentioned on my website but not related to this topic.
See: Fix Shrunken Navigation Pane

[Louverril]

Fame at last!

Thank you :-)

[MikeSullivan]

Hi,

How are you setting the passwords on the backend database(s)? Were they set in Office 2007 or set with Office 2010 or later? Were they set using VBA or were they set using the UI?

If you can open the backend database directly, what encryption option is selected under Access Options -> Client Settings -> Encryption Method?

Mike

[Louverril]

Hi Mike,



How are you setting the passwords on the backend database(s)? Were they set in Office 2007 or set with Office 2010 or later? Were they set using VBA or were they set using the UI?

In my zipped example they were set in the UI using the Access version in Office 365 click to run latest version of Office 2016.


If you can open the backend database directly, what encryption option is selected under Access Options -> Client Settings -> Encryption Method?

Selected the "Use default encryption (Higher security)"

[isladogs]

OK had a quick look at the file with 14/15/19/20 char passwords both in Access and a Hex editor
One of the many reasons why a file based application can NEVEr be 100% secure, is that a huge amount of information is visible by looking at the file in a text or hex editor.
For example:

 Capture.PNG ( 71.96K )Number of downloads: 7


As you can see, the 14 & 15 char passwords are in plain sight though surprisingly not the 19 char password that worked for the linked table (unless its somewhere else I've missed

In searching I did find a lot of other info you may not know is in plain sight.
I'll send you a PM

[MikeSullivan]

To be clear, this screenshot of the hex editor is from the unencrypted front end, correct? This should not display like this when viewing the hex dump of the encrypted database itself.

You are correct, it is not recommended to store passwords like this on connection strings for this very reason. Prompting for the password would be recommended if possible.

[isladogs]

The screenshot was of course from the OP's unencrypted FE file
I've referred her to this article on my website: compare-access-file-security - MDB/MDE vs ACCDB/ACCDE

[Louverril]

But once the user puts the password in on the front end to relink. It is then stored in the msysobjects connection string anyway?

[isladogs]

Correct.
The point about the hex editor is that anyone can view the contents of an unencrypted Access file even if they don't have Access.
If its encrypted, the contents are unreadable.

As for MSysObjects, if you lock down your database as fully as possible including preventing users accessing the nav pane and encrypt both FE and BE, they will need to be fairly knowledgeable to know how to view the connection strings.
If they are that knowledgeable, they will probably just back your BE file assuming they can find it.

There is another approach which makes use of BE tables but the links are not permanent.
Instead forms are used to create temporary links used for their record source and destroy the link when the form is closed.
So there is no permanent record in MSysObjects.
I call this method 'Linked No Tables'

Mind you if your data is that confidential, the BE shouldn't be in a file based database such as Access anyway.
Use SQL Server or similar instead.

[isladogs]

Forgot to say that I have a demo of the 'Linked No Tables' approach in case it helps

 LinkedNoTablesDEMO.zip ( 463.7K )Number of downloads: 4

[Louverril]

Thanks Isla dogs,

I will look at this. Its not that confidential so I may be going overboard. But its good to know!