UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connection Strings In Msysobjects No Longer Showing!?, Access 2016    
 
   
Louverril
post Feb 1 2019, 07:56 AM
Post#1



Posts: 497
Joined: 29-April 08



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:

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


This is what I was seeing:
Attached File  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?

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
dashiellx2000
post Feb 1 2019, 09:44 AM
Post#2


UtterAccess VIP
Posts: 9,810
Joined: 11-March 05
From: Maryland


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

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
GroverParkGeorge
post Feb 1 2019, 09:51 AM
Post#3


UA Admin
Posts: 34,536
Joined: 20-June 02
From: Newcastle, WA


"... 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?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Louverril
post Feb 1 2019, 10:29 AM
Post#4



Posts: 497
Joined: 29-April 08



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]

Attached File  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)
Attached File  msyssnipLongVShortPWD.JPG ( 50.99K )Number of downloads: 0
 

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
Louverril
post Feb 1 2019, 11:39 AM
Post#5



Posts: 497
Joined: 29-April 08



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:

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


Attached File  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

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
isladogs
post Feb 1 2019, 11:47 AM
Post#6



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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

Attached 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

--------------------
Go to the top of the page
 
GroverParkGeorge
post Feb 1 2019, 11:59 AM
Post#7


UA Admin
Posts: 34,536
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Louverril
post Feb 1 2019, 12:09 PM
Post#8



Posts: 497
Joined: 29-April 08



Hi Isladogs,

Dash...


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

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
isladogs
post Feb 1 2019, 12:22 PM
Post#9



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
Louverril
post Feb 1 2019, 12:24 PM
Post#10



Posts: 497
Joined: 29-April 08



Fame at last!

Thank you :-)

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
MikeSullivan
post Feb 1 2019, 12:32 PM
Post#11



Posts: 7
Joined: 31-May 18



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
Go to the top of the page
 
Louverril
post Feb 1 2019, 12:40 PM
Post#12



Posts: 497
Joined: 29-April 08



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)"

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
isladogs
post Feb 1 2019, 12:48 PM
Post#13



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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:

Attached File  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

--------------------
Go to the top of the page
 
MikeSullivan
post Feb 1 2019, 01:45 PM
Post#14



Posts: 7
Joined: 31-May 18



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.
Go to the top of the page
 
isladogs
post Feb 1 2019, 01:50 PM
Post#15



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
Louverril
post Feb 5 2019, 06:33 AM
Post#16



Posts: 497
Joined: 29-April 08




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

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
isladogs
post Feb 5 2019, 06:50 AM
Post#17



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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.

--------------------
Go to the top of the page
 
isladogs
post Feb 5 2019, 07:58 AM
Post#18



Posts: 1,017
Joined: 4-June 18
From: Somerset, UK


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

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

--------------------
Go to the top of the page
 
Louverril
post Feb 5 2019, 09:10 AM
Post#19



Posts: 497
Joined: 29-April 08



Thanks Isla dogs,

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

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 12:29 PM