Solved Microsoft Access Security Notice

[Marshall Brooks]

Looking for how to resolve this ...

We have a database currently shared between 8 users.

The database is split, the backend is on a server, each user has a copy of the front end.

Half our users use Citrix and we solved the issue there by going into Trust Center and changing the macro settings there to enable macros.

4 of our users run the file locally and the trust center macros settings are locked at "Disable macros with notification."

The File path above is the path to the backend and the error message occurs 7 times - I think as the database reads in tables from the back end to the front end.

3 of the local users do not have the error message, but one does - with the same version of Office.

The one who does has set the backend (and front end) location to a trusted location and done a repair install of Office and has not been able to resolve the issue.

Corporate IT is unable to resolve the issue.

We were wondering how to make the database (either front end or back end a trusted document (as opposed to a trusted location).

I know the registry path for that, but not sure what to add for the value ...

StackOverflow mentions HKCU\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Documents\TrustRecords

I have an entry for the backend there, but there is a 36 or so character value for the key and I don't know what that means ...

Any help appreciated!!!

 

[arnelgp]

you can download an .exe that will put necessary registry entry for your db on trusted location:
Ribbons for Access 2007 / Access 2010 – Trusted Locations (accessribbon.de)

 

[Marshall Brooks]

@arnelgp - Thanks, but that isn't the issue. My coworker already manually added the database path as a trusted location and still gets the error message.
(And I'm not sure if she could run an .exe or edit the registry without admin rights, but ...)

My question was more about making the backend a trusted DOCUMENT. The registry key above is for that, but for me it has a value of something like: "a4 d1 08 af 38 a8 d4 01 00 f8 29 17 d6 ff ff ff 5c 13 8b 01 gg gg gg 7g" - Some of the numbers were changed in this entry.

I didn't know if that was arbitrary or required, or if it is unique to me or she should use the same one I used, etc.

 

[The_Doc_Man]

That is a bizarre key because the little "g" characters are not normal for a hexadecimal string. Up to the first "g" I would have said that was a hash string - but hashing cannot produce a "g" and therefore, either that is copied wrong or it is something I have never seen before as a security string. You said the numbers were changed, which might account for it. But hexadecimal keys are usually hashed values, which means that they may take into account some path and filename info. Which means it would only work correctly for one file in one place.

 

[Marshall Brooks]

@The_Doc_Man - It is probably a hash string. Let's assume it is. There are no g's in the string. I changed the string so there wasn't a way for someone to read the info and decompile it into plain text - if that is possible.

More specific question: I have macros disabled and the front end and backend paths as trusted locations and the backend as a trusted document and I do not get the security alert.

My co-worker has macros disabled and the front and backend paths as trusted locations and does not have the backend as a trusted document and DOES get the security alerts.

If she adds the key to the registry on her computer, using the same hash tag and I am using for the value of the key, is it likely to work? Is there any potential harm in doing that?

Thanks in advance!

 

[Marshall Brooks]

Not sure what to make of this, but I have some new info ...

I have two frontends for the same backend database. The first frontend is a daily-use one and on startup, it downloads about 7 tables from the backend as local tables in the frontend and my coworker gets the error message 7 times - seems related. The second front-end essentially has linked access to the same tables that get downloaded. It loads a couple of forms at startup, and my coworker said she does NOT get the message with that frontend.

That is making me doubt it is a trusted database issue and more of an issue with Access not liking data transferring across the network, but I'm not sure why.

Update: My coworker confirmed that holding down shift to open the database and bypassing the startup code the database will open without generating the message. (It also won't transfer the tables, so it isn't useable that way). Corporate IT confirmed we both have the same folder permissions. Moving her copy of the frontend to a different trusted location still generates the error.

So now I am down to "For some reason the error occurs when the frontend tries to transfer tables from the backend to the local frontend, but only for her ..."

 

[The_Doc_Man]

I changed the string so there wasn't a way for someone to read the info and decompile it into plain text - if that is possible.

Without knowing the particular hashing method, probably not.

it downloads about 7 tables from the backend as local tables in the frontend and my coworker gets the error message 7 times

How does it do the download? By linked query or by an "ImportText" or other import operation? The question is whether the files are being treated as files or as linked tables. Different file access methods are involved because the linked tables don't have to explicitly open the files in the same way that a file-based DoCmd-based import would.

 

[Marshall Brooks]

If TableExists("tblHolidays") Then
CurrentDb.TableDefs.Delete ("tblHolidays")
' If (Not IsNull(ELookup("Name", "MSysObjects", "Name='tblHolidays'"))) Then
' DoCmd.DeleteObject acTable, "tblHolidays"
End If
DoCmd.TransferDatabase acImport, "Microsoft Access", "\\backend_path\Mydatabase_be.accdb", acTable, "tblHolidays", "tblHolidays", False

There is also a:
DoCmd.TransferSpreadsheet acImport, 10, "Work Orders", "\\different_path\Work Order Spreads.xlsx", True, "WorkOrders"

But since there is only 1 Excel file and 8 tables and approximately 8 warnings, I don't think it's the spreadsheet, although I don't think that is a trusted location.

I'm just not sure why it is only an error for her ...

 

[The_Doc_Man]

The usual way to decide what is going on requires hands-on for two machines. One that works and one that doesn't.

Go to a machine that works. Using Windows Explorer, navigate to that backend path and select the .ACCDB file, but don't open it. Instead, RIGHT-click it to follow Properties >> Security and note the security settings, which for Access is normally MODIFY (as a broad brush setting.) You need the settings for both the .ACCDB file AND its parent folder. Find the Advanced Security tab and get it to evaluate the .ACCDB file as seen by that system. You will have to select the username to get an effective permission reading because that option will show you part of the Access Control List for the file. If the answer you get is partly greyed out, it probably means you were dealing with inherited permissions.

Now (still on the working machine) visit the "different path" to look at the files you want to import. Make notes.

Go to the machine that doesn't work. Repeat the steps from above. IF this is a simple security problem then the notes you take for the machine that doesn't work will be different from the notes for the machines that work and those differences will explain why you are having the problem. If the file "effective privileges" are the same, it is not a permissions issue. In that case, you probably will have either a Security Center problem or some error in the VBA setup code.

 

[Marshall Brooks]

Thank you - I'll check those ideas out!!!

 

[Marshall Brooks]

Sorry for the delay getting back to you.

On my working machine, I am in a user group with permission to read the database and for the database under Effective Permissions, I have everything except Full Control and Take Ownership. At the database folder level, I have everything except Full Control, Take Ownership, and Delete Subfolders and Files (but I think I can delete files), permissions are the same on the Excel File and folder.

My co-worker checked and she doesn't even find her name under a name search or employee username, although she can open the file from her computer.

I had Corporate IT remove and re-add her to the User Group and no changes to the security messages.

 

[The_Doc_Man]

OK, the "group" access is conveyed by an Access Control List, which is order-sensitive. Group IDs used to convey permissions should not be last in the list, particularly if the user in question appears earlier in the list with lesser permissions - including expressed or implied membership in another group - that appears earlier. When you look the file properties >> security, the list control that shows the permissions on the file shows them in order of ACL appearance. Just be sure that the group is not being overridden by an earlier group in which she could also be a member.

For instance, sites that have a corporate IT group often make the "Authenticated Users" group a member of every ACL by making an inheritable entry at the top folder of the folder chain leading to your files. Typically, Authenticated User permissions are READ-ONLY or NO ACCESS. And EVERY USER WHO USED A LOGIN falls into the "Authenticated Users" group. Your unlucky user's name won't appear separately in the Authenticated Users group because it is one of those automatic groups that Microsoft synthesizes behind the scenes. Other common groups include "Everyone", "Users", "Administrators", "LOCAL", "INTERACTIVE"... the list goes on (as it ALWAYS does for Microsoft.)

My co-worker checked and she doesn't even find her name under a name search or employee username, although she can open the file from her computer... I had Corporate IT remove and re-add her to the User Group and no changes to the security messages.

The question is then to know what name IT added to the group to make her a member. If you have a Corporate IT group then it implies you have a domain-class login (as opposed to purely local logins). If your user can get to the Windows Command Prompt, she can type "WHOAMI" to see the full domain name under which she logged in. Or you can do something a little more complex to see what groups she belongs to.

How to See Which Groups Your Windows User Account Belongs To

A security group is really just a collection of user accounts. Rights and permissions are assigned to a group, and then those rights and permissions are granted to any account that’s a member of the group. Group membership can determine a user’s access to files, folders, and even system...

www.howtogeek.com

 

[Marshall Brooks]

Progress!!!

I think the group assignments are incorrect, but ...

I re-wrote the database code. The old database had code to delete and copy some tables over to the front end. The test database cleared the data from the front-end tables and refreshed them without deleting them.

I sent my co-worker a development copy of the database and she said she got the following message:

Clicked “X”, got yellow banner for “enable macros”, clicked yes, got below popup

Clicked yes, then database opened normally.
Closed the database and opened again, and didn’t get any pop-ups. (!)

She said she still gets the errors with the released database, so I'm thinking she made the front-end a trusted document and not the backend, but I'm hoping that when I release the official file, she will have the same options and I can mark this thread solved.

(I have no idea why MS allows you to make a document trusted that refreshes data, but not one that replaces it, but ...)

 

[Marshall Brooks]

Not exactly "fixed", but resolved.

I released the new version and my co-worker no longer gets the pop-ups.

She also said she didn't have to make the new front end a trusted document, which surprised me, but ...

 

[The_Doc_Man]

She also said she didn't have to make the new front end a trusted document, which surprised me, but ...

Although there are markers in the file headers, often a file is trusted because its name has been placed in the trusted files list. If you then make a new version of the file with the same name, it is possible that you won't need to change anything. Note that this depends on group policy statements that are loaded to your computer under various circumstances based on a corporate IT decision of what rules to enforce.

 

[Marshall Brooks]

Understood, that would not have surprised me. Essentially, I said try File_Test_Demo.accdb and she said she had the option to make it a trusted document, and then I put out Database.accde and she said it worked and she didn't have to do anything. I'm guessing either one of two things happened:

• She renamed the test file I sent to what we usually call it (and renamed the file she was using to _backup or similar) and made it an .accde. Unlikely, but possible.
• More likely, the demo file made the back end as well as the front end a trusted document. (But she said she still got the error with the old front end after that, so not likely either.

Bottom line is she isn't getting the error messages anymore...

 

[The_Doc_Man]

Sounds like reason to celebrate for a minute or two and then go on to tackle the (inevitable) next problem.

 

[Marshall Brooks]

True - I wish it were more a black-and-white world, but software usually isn't. (i.e. I wish I could say "I misspelled this word and that's why the program crashed." As opposed to "I changed this line of code and it allowed it to be a trusted document, but it never did before, but I have no idea why ..."

Case-in-point, I was wondering if the backend was added to the trusted documents and I could have just had her add the registry key from my computer to solve the error. She sent me a screen shot and neither the front end or the back end are trusted documents. The test file from yesterday in a different path is a trusted document, but she no longer gets the error message ...

?????????