Options for Dealing With Trusted Locations

[JMongi]

I'm working on establishing the best methods for our company to setup and distribute a basic split DB setup (Jet BE on server share; individual FEs locally).
I have my scripts working to copy files to local FEs. The only thing I'm lacking at the moment is an automated way to ensure code can be run i.e. setting the FE location as a trusted location.

I saw a few threads about registry files and whatnot. But, before I dive into understanding a specific methodology I should probably make suere thats the one I want to use.

Our server is running Windows Server 2008 and uses Active Directory but is managed by a third party IT company. So I'm assuming I will get little support outside of their typical operating procedures. Ideally, whatever process I use will not need their intervention.

Edit: I know there are quite a few threads on here. But, none seemed to actually outline how to go about doing most of it. I gather you can add registry entries (I saw @PatHartman thread in the code sample forum) and I read much about various purchasable modules to do this (not an option unfortunately). I read a post from @Isaac that mentioned using VBscript to set registry keys, but I have yet to find a reliable guide on how to do that.

 

[isladogs]

I use a professional installer app to create registry keys for trusted locations similar to this:

//trusted locations
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10 :: Path="C:\Programs\MendipDataSystems\"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10 :: AllowSubFolders=1
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10 :: Description="Mendip Data Systems"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10 :: Date="27/04/2014 19:51"

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10 :: Path="C:\Programs\MendipDataSystems\"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10 :: AllowSubFolders=1
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10 :: Description="Mendip Data Systems"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10 :: Date="08/12/2015 19:51"

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10 :: Path="C:\Programs\MendipDataSystems\"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10 :: AllowSubFolders=1
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10 :: Description="Mendip Data Systems"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10 :: Date="27/04/2014 19:51"

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location11 :: Path="C:\Programs\MendipDataSystems\"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location11 :: AllowSubFolders=1
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location11 :: Description="Mendip Data Systems"
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location11 :: Date="08/12/2015 19:51"

The script forms parts of the installation file so it is added automatically before the app is run for the first time.
The above covers Access 2007 (12.0), 2010, (14.0), 2013, (15.0) & 2016/2019/2021/365 (all 16.0)

A similar idea is used with VB script

The easiest way to determine the contents of the script is to examine the registry keys for a trusted location already created then export those to a text file. This can be used with any alterations required in your script file

 

[JMongi]

I recognize the text of a registry key. I have used regedit before. I must be missing the key bit of information in, how does having the text file/strings help me updated the registry without invoking regedit? My GoogleFu must be failing me today.

 

[isladogs]

The script file runs and adds the keys to the registry

 

[JMongi]

@isladogs - You are cracking me up today! Yes, that would be how it works at a high level. It is more like how do I "add the keys to the registry".

I've seen so many posts talk about where to add the locations, and how to format the keys, and stating to "use a script" to add the keys but nothing that I could use to actually DO THAT.

I think I found it finally. object.regwrite

Man, I couldn't find any direct references to that method anywhere. Most of the detailed examples were in VBA which is similar but not quite to VB Script.
With the registry I defintiely didn't want to guess if that would work correctly in VBScript

 

[CJ_London]

The problem with a vba script is it won’t run unless it is in a trusted location or the user allow it to run. So anything vba is not really a way forward

 

[KitaYama]

Since you're on a windows domain and Active Directory is installed, you can use Group Policy to run a script on all clients.
It will be out of user's power to cancel or prevent the execution of the file.
Normally, as the article shows, it's placed in login event. So it will be run when a client login to the Domain.

Distribute a batch file by Group Policy management - Microsoft Q&A

Hi Guys, I wanna distribute a batch file , should i type admin name and pass in the batch file ? Which account domain controller will use if the bat without admin name and pass ?

learn.microsoft.com

There are several more methods.
Google for active directory run a batch file in all clients or similar keywords.

EDIT:
Working on both registry and Group Policy may be disastrous. Be careful.

 

[arnelgp]

SOLVED: Command Line Script To Add or Delete a Registry Entry – Up & Running Technologies, Tech How To's (urtech.ca)

 

[Galaxiom]

Running scripts with Group Policy to edit the registry is a clumsy solution.
Group Policy Administrative Templates can control all the settings in Office and prevent them being overridden by users.

Personally I am happy to continue using mde front ends with signed code. They don't need Trusted Locations.

 

[JMongi]

@KitaYama - As I mentioned in post #1 - I have no admin access to set group policy. That would involve our third party IT company which I want to avoid if possible. Also, as @Galaxiom points out, it's not a solution I'm a fan of even if I had rights to set group policies.

@CJ_London - I'm not quite understanding your objection. This script/code would be part of the installation initialized by the user. So, no issues right?

@arnelgp - Thanks for the link, I'll look into that methodology.

@isladogs - Is this a sample of how you would use vbscript to create some registry entries? Or do you use some other method?

Dim oShell
Dim sReg
Set oShell = CreateObject("Wscript.Shell")
sReg = "Some registry string here"
oShell.regwrite sReg

 

[JMongi]

As I'm putting this together and checking the registry files on my local computer....

I see Office entries for 12, 14, 15, 16...but, only an Outlook folder for 14.
If I write a new key to \......\14.0\Access\Security\......\ will that cause a problem if there isn't currently an Access folder?

 

[isladogs]

I don't use vb script for this

In answer to post #11. . . no it won't

 

[JMongi]

Thanks for the input. You use a 3rd party installer to handle this, yes?

 

[Pat Hartman]

You only need to run the installer once to set up the trusted locations, make the target folder for the FE and install an updated ODBC driver if you need one or any ActiveX controls.

 

[JMongi]

@Pat Hartman - That is exactly the plan.

Since I don't have any commercial development/packaging tools, I will be using a vbscript to do the installation. It will create local storage locations, copy relevant files from the server to those locations, create a desktop shortcut to run the separate launch script, (and now) set some registry keys to make those local storage locations trusted locations for Access.

I believe I've deciphered the shell.regwrite syntax and methodology in vbscript.

 

[JMongi]

So here is the registry update subroutine:

Sub SetTrusted
sModuleName = "SetTrusted"
'This code will need updated if Microsoft adds a version of Office codebase above 16.0

'Define registry key locations and strings
Const cRegKey12 = "HKCU\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10"
Const cRegKey14 = "HKCU\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10"
Const cRegKey15 = "HKCU\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10"
Const cRegKey16 = "HKCU\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location10"

'Set registry keys for trusted
Dim aRegKey, reg
aRegKey = Array(cRegKey12, cRegKey14, cRegKey15, cRegKey16)
For Each reg In aRegKey
    oShell.RegWrite reg & "\Path", sLocalApp, "REG_SZ"
    oShell.RegWrite reg & "\Description", "Operations App Directory", "REG_SZ"
    oShell.RegWrite reg & "\Date", Now(), "REG_SZ"
    oShell.RegWrite reg & "\AllowSubfolders", 1, "REG_DWORD"
Next

Call ErrHandler (sModuleName)
End Sub

Edit: Notes for those that just see this last bit of code.
1. "Location10" is a unique identifier of a trusted location. It does not have to be "Location10" but it must not be the same as any other location key name already in use. Location10 is likely to not be in use by the majority of users. But, be aware.
2. This code is VBScript not VBA. They are similar but different. One difference is variables are not typed in VBScript. In this code "Dim strA" is okay whereas in VBA you would need to do "Dim strA as String"
3. This is a subroutine from my script. Certain variables and objects are definted elsewhere (such as oShell and sLocalApp).

 

[JMongi]

I revised the sub to be a standalone VBScript.

Option Explicit
'This code will need updated if Microsoft adds a version of Office codebase above 16.0

'Instantiate Shell Object
Dim oShell
Set oShell = CreateObject("Wscript.Shell")

'Set Trusted Location Path and Description
Dim sTrLoc, sLocName
sTrLoc = "C:\MyApps\"    'Revise to your trusted location on the local machine
sLocName = "My Trusted Apps"    'Revise to your trusted location description

'Define registry key locations and strings
'Please note that "Location10" must be unique and not already in use; this WILL overwrite if it exists
Const cRegKey12 = "HKCU\SOFTWARE\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location10"
Const cRegKey14 = "HKCU\SOFTWARE\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location10"
Const cRegKey15 = "HKCU\SOFTWARE\Microsoft\Office\15.0\Access\Security\Trusted Locations\Location10"
Const cRegKey16 = "HKCU\SOFTWARE\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location10"

'Set registry keys for trusted location
Dim aRegKey, reg
aRegKey = Array(cRegKey12, cRegKey14, cRegKey15, cRegKey16)
For Each reg In aRegKey
    oShell.RegWrite reg & "\Path", sTrLoc, "REG_SZ"
    oShell.RegWrite reg & "\Description", sLocName, "REG_SZ"
    oShell.RegWrite reg & "\Date", Now(), "REG_SZ"
    oShell.RegWrite reg & "\AllowSubfolders", 1, "REG_DWORD"
Next

WScript.Quit

 

[isladogs]

Thanks for the input. You use a 3rd party installer to handle this, yes?

Yes.
As already stated, I use an installer which includes script to trust locations when distributing software
However, I also use VBA to write to the registry for many other purposes both to HKCU & to HKLM hives.
Editing HKLM requires Access to be run as an administrator.

As @C J_London has already explained, you cannot use VBA to create a trusted location for the same app as that code cannot run unless the app is already trusted. Its a deliberate Catch-22 situation.

 

[JMongi]

Thanks Colin. I forgot you referenced the installer earlier.

I'm pleased that my script seems to work and since the directory is a user folder and the registry keys are user keys I don't think I'll run into any permission issues despite our Active Directory setup.

 

[isladogs]

Personally I am happy to continue using mde front ends with signed code. They don't need Trusted Locations.

True, but as you are well aware, MDB/MDE files are FAR less secure than ACCDB/ACCDE files:

Compare Access File Security

This article compares the security of the MDB & ACCDB file formats together with the compiled MDE & ACCDE versions.

www.isladogs.co.uk

Personally, I wouldn't be happy with using MDE FEs.
However, I accept that code signing in MDB/MDE does mitigate their lack of security to a significant extent