Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Security _ Deleted Trusted Locations Reappearing When Database Opened

Posted by: bazman1uk Jul 26 2019, 04:24 AM

Hi,

After a stupid decision by our IT department to remove all blanket trusted locations on all our PCs, I now have a situation where I need to add a trusted location with subfolders to 200 odd users to allow them to open an Access 2016 database. They cannot click enable content as the locked down default form is preventing it

I have been trying to write a VB.NET application to run first to check if the TL exists for that location in the registry and if not add a registry key for it, then launch the database.

Our GPO access only allows us to add/modify HKEY_CURRENT_USER and the TL locations are located below Software\Microsoft\Office\16.0\Access\Security\Trusted Locations

Just for testing, I hard coded it to create a Location50 with AllowSubfolders and it seemed to work. I then decided I do really need it to be dynamic and make it create the next Location number after what is already in use. So I then deleted Location50 from the registry

I then added a pre process to identify the last used LocationX key and then add the TL using the next number. This seemed to work OK too a it added Location2 as Location0 and Location1 were already in use.

This is where the problems start!!!

Firstly, I opened the database again and where it worked before with Location50, it didn't now and seemed to not recognise the Location2 TL in the registry?? No entry in the Trust Center Settings in Access itself

Secondly, and the really weird thing, Location50 that was hard coded earlier and removed from my code and deleted from the registry, reappeared in the registry??
So I deleted this key again and reopened the database and it continues to recreate itself.

Any ideas on this?

Cheers

Baz

Posted by: pere_de_chipstick Jul 26 2019, 06:31 AM

welcome2UA.gif

You might find this article in the UA Wiki will help you: https://www.UtterAccess.com/wiki/AddTrustedLocation.

For A2016 you may need to change the strLnKey line from:
strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(db.Version, "##,##0.0") & _
"\Access\Security\Trusted Locations\Location"

to:
strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location"

hth

Posted by: isladogs Jul 26 2019, 09:01 AM

Just out of interest, what was wrong with hard coding as e.g. Location 50.
I've used that approach For over ten years to assign trusted locations in the registry using a script file without any issues

Posted by: bazman1uk Jul 29 2019, 02:58 AM

Hi, that is the way I had it. Thanks

Posted by: bazman1uk Jul 29 2019, 03:01 AM

The help gave me the impression that the LocationX folders had to be sequential from 0, hence detecting what the last one was and setting accordingly

I've also got a Location22 that I tried also, but then realised that there's a poss that people could have 22 TLs here. That also keeps coming back like 50 after deleting too!!!

Posted by: bazman1uk Jul 29 2019, 03:53 AM

In addition, now if I delete all TLs from the registry and create one manually in Access, it creates a Location0 key in the registry Ok, however it has no subkeys other that the (Default) key with no value set. No Path, Description, Date or anything???

Posted by: pere_de_chipstick Jul 29 2019, 04:49 AM

Hi

Am not sure why your Location50 re-appeared, but the trusted location will be different for each user logged onto the PC.

The code I suggested in the link will added a trusted location for each user the first time they use the database; once it has been added the security warning will not be shown for any subsequent logon (for that user).

Trusted locations do not have to be sequential; however when the code runs it will search for a spare location record in the registry and assign the trusted location to that location number.

hth

Posted by: bazman1uk Jul 29 2019, 05:10 AM

Hi Pere,

yes I know that and that IS the code I'm using. It's jut not doing as expected

Posted by: pere_de_chipstick Jul 29 2019, 05:34 AM

That is confusing!

Have you checked the strLnKey?

Paste
? "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(db.Version, "##,##0.0") "\Access\Security\Trusted Locations\Location"
and click return.

On my PC this produces "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location"
which is wrong - it should be
"HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\Trusted Locations\Location"

Check your registry to check the registry path being used.

hth

Posted by: bazman1uk Jul 29 2019, 05:56 AM

Mine is definitely HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\Trusted Locations

Posted by: pere_de_chipstick Jul 29 2019, 06:20 AM

Are you able to step through the code to check what it is doing?

I have used the code consistently for some years now and am at a bit of a loss as to what is failing iconfused.gif

Posted by: bazman1uk Jul 29 2019, 07:05 AM

Hi,

I am a developer in various languages for over 20 years now. Line by line is how I debug it. It runs through perfectly with no reference to the other Locations. It's like Access is adding it itself when you open the database and it's not just a specific database, it's any database.

When Access is open, another registry key seems to create itself HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Resiliency with a subkey named StartupItems
The key in it is named +0( of REG_BINARY type but the data itself is hex and unreadable

This disappears once Access is closed


Posted by: pere_de_chipstick Jul 29 2019, 10:04 AM

Hi bazman1uk

iconfused.gif !!

Is the code not adding the trusted location correctly or is it just adding the additional location unexpectedly?

Could you post the exact code you are using to set the registry value?


Posted by: bazman1uk Jul 30 2019, 02:36 AM

Dim sPath As String 'Path to set as a Trusted Location
Dim sDescription As String 'Description of the Trusted Location
Dim sParentKey As String
Dim lVal As Integer
Dim kFound As Boolean

sPath = "\\YourPath\Shared\"

sParentKey = "Software\Microsoft\Office\16.0\Access\Security\Trusted Locations"

With My.Computer

Dim key As Microsoft.Win32.RegistryKey = .Registry.CurrentUser.OpenSubKey(sParentKey)
For Each subKey In key.GetSubKeyNames
Select Case subKey
' REMOVE ROGUE KEYS
Case "Location22", "Location50"
.Registry.CurrentUser.DeleteSubKeyTree(sParentKey & "\" & subKey)
Case Else
If .Registry.CurrentUser.OpenSubKey(sParentKey & "\" & subKey, True).GetValue("Path") Is Nothing Then
MsgBox("No Path")
Else
MsgBox(.Registry.CurrentUser.OpenSubKey(sParentKey & "\" & subKey, True).GetValue("Path"))
If .Registry.CurrentUser.OpenSubKey(sParentKey & "\" & subKey, True).GetValue("Path") <> sPath Then
MsgBox("Incorrect Path")
Else
MsgBox("Correct Path")
Exit Sub
End If
End If
End Select

Next

lVal = 0

Do
sDescription = "Location" & lVal
If .Registry.CurrentUser.OpenSubKey(sParentKey & "\" & sDescription, True) Is Nothing Then
' MsgBox("Not Found")
Exit Do
Else

End If

lVal = lVal + 1

Loop

.Registry.CurrentUser.CreateSubKey("Path")
.Registry.SetValue("HKEY_CURRENT_USER\" & sParentKey & "\" & sDescription, "Path", sPath)

.Registry.CurrentUser.CreateSubKey("Description")
.Registry.SetValue("HKEY_CURRENT_USER\" & sParentKey & "\" & sDescription, "Description", "Root trusted path for ReMIT")

.Registry.CurrentUser.CreateSubKey("Date")
.Registry.SetValue("HKEY_CURRENT_USER\" & sParentKey & "\" & sDescription, "Date", Format(Now(), "dd/MM/yyyy HH:mm"))

.Registry.CurrentUser.CreateSubKey("AllowSubfolders")
.Registry.SetValue("HKEY_CURRENT_USER\" & sParentKey & "\" & sDescription, "AllowSubfolders", 1, Microsoft.Win32.RegistryValueKind.DWord)

sPath = Nothing
sDescription = Nothing

End With

Posted by: pere_de_chipstick Jul 30 2019, 04:05 AM

Hi Bazman1UK

I've looked at your code, and it throws up a number of compilation errors, which I am not able to resolve.

Though the first query I have is that the registry path you have is not "HKEY_CURRENT_USER\", but a network location.

I am not an expert on the registry, but understand that if the registry is locked down then you can only change HKEY_CURRENT_USER settings,

I will put out a shout to see if anyone else can help.

Posted by: bazman1uk Jul 30 2019, 05:30 AM

Hi,

The reg KEY path IS the HKEY_CURRENT_USER\...... location.

The network path it is setting to is the sPath variable

Posted by: isladogs Jul 30 2019, 05:37 AM

Although it seems not relevant here, restricted registry hives such as HKEY_LOCAL_MACHINE can only be edited if Access is opened using Run As Administrator.
That is why my https://www.UtterAccess.com/forum/index.php?showtopic=2051922 app needs to be run in that setting

Posted by: cheekybuddha Jul 30 2019, 05:59 AM

Hi,

Where is class/type 'My' defined? What is its definition? Is it in vb.net?

Perhaps you should get the writing to the registry working first before over-complicating.

I have a (VBA) module for adding trusted locations - it is probably based on the code that Bernie linked to, but varies a little:

CODE
Option Compare Database
Option Explicit

Function AddTrustedLocation(strLocationPath As String, _
                            Optional blIncludeSubfolders As Boolean, _
                            Optional strDescription As String) As Boolean
On Error GoTo Err_AddTrustedLocation

  Const DWORD             As String = "REG_DWORD", _
        SZ                As String = "REG_SZ", _
        ALLOW_SUBFOLDERS  As String = "AllowSubfolders", _
        NETWORK_LOCATION  As String = "AllowNetworkLocations", _
        LOCATION_KEY      As String = "Location", _
        DATE_KEY          As String = "Date", _
        PATH_KEY          As String = "Path", _
        DESCRIPTION_KEY   As String = "Description", _
        MAX_LOCATIONS     As Integer = 999, _
        BS                As String = "\"

  Const LOC_KEY_1         As String = "HKEY_CURRENT_USER\Software\Microsoft\Office\", _
        LOC_KEY_2         As String = "\Access\Security\Trusted Locations"

    Dim blRet             As Boolean, _
        strVersion        As String, _
        strLocKey         As String, _
        strKeyVal         As String, _
        i                 As Integer

  strVersion = Application.Version
  If Right(strLocationPath, 1) <> BS Then
    strLocationPath = strLocationPath & BS
  End If
  With CreateObject("wscript.shell")
    On Error Resume Next
    For i = 1 To MAX_LOCATIONS
      strLocKey = LOC_KEY_1 & strVersion & LOC_KEY_2 & BS & LOCATION_KEY & i & BS
      strKeyVal = .RegRead(strLocKey & PATH_KEY)
      If Err = 0 Then
        If InStr(strLocationPath, strKeyVal) > 0 Then
          If strKeyVal = strLocationPath Then
'           Trusted location already exists
            Debug.Print "Trusted location '" & strLocationPath & "' already exists."
            blRet = True
            Exit For
          Else
'           A folder higher up the path is trusted, check whether it includes subfolders
            strKeyVal = .RegRead(strLocKey & ALLOW_SUBFOLDERS)
            If Err = 0 Then
              If Val(strKeyVal) = 1 Then
                Debug.Print "'" & strLocationPath & "' is trusted as a subfolder of '" & .RegRead(strLocKey & PATH_KEY) & "'"
                blRet = True
                Exit For
              End If
            Else
              Err.Clear
            End If
          End If
        End If
      Else
        On Error GoTo Err_AddTrustedLocation
'       Location not found, we can use it to create new location
        .RegWrite strLocKey & PATH_KEY, strLocationPath, SZ
        .RegWrite strLocKey & DATE_KEY, Now, SZ
        .RegWrite strLocKey & DESCRIPTION_KEY, strDescription, SZ
        If blIncludeSubfolders Then
          .RegWrite strLocKey & ALLOW_SUBFOLDERS, DWORD
        End If
        Debug.Print "'" & strLocationPath & "' is now a Trusted Location.", "[" & strLocKey & "]"
'       If the location is a network share then this key needs to be added to Trusted Locations
        Select Case True
        Case Left(strLocationPath, 2) = BS & BS, IsMappedDrive(Left(strLocationPath, 2))
          strLocKey = LOC_KEY_1 & strVersion & LOC_KEY_2 & BS & NETWORK_LOCATION
          .RegWrite strLocKey, 1, DWORD
          Debug.Print "Trusted locations can include network shares.", "[" & strLocKey & "]"
        End Select
        blRet = True
        Exit For
      End If
    Next i
    If Not blRet Then
      MsgBox "Unable to add any more Trusted Locations - " & MAX_LOCATIONS & " have already been created.", _
             vbOKOnly + vbInformation, _
             "Location count exceeded"
    End If
  End With

Return_Result:
  AddTrustedLocation = blRet
  Exit Function

Err_AddTrustedLocation:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: AddTrustedLocation" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basTrustedLocation", , "Error: " & Err.Number
  End Select
  Resume Return_Result

End Function

Function IsMappedDrive(strDrive As String) As Boolean
' adapted from:
' http://www.la-solutions.co.UK/content/V8/MVBA/MVBA-Mapped-Drives-UNC.htm#GetMappedPathFromDrive
  Dim i As Integer
  
  With CreateObject("WScript.Network")
    With .EnumNetworkDrives
      If .Count Then
        For i = 0 To .Count - 1 Step 2
'          Debug.Print .Item(i), .Item(i + 1)
          If .Item(i) = strDrive Then
            IsMappedDrive = True
            Exit For
          End If
        Next i
      End If
    End With
  End With
  
End Function

The code is late-bound so you don't need any references, but obviously you will need to run it from a db in an already trusted location.

Or it can be easily translated into vbScript or .net

hth,

d

Posted by: bazman1uk Jul 30 2019, 07:26 AM

Hi,

The code works fine. Well it did. It created Location50 and Location22, etc. OK beforehand and it echoed in the TL settings in Access. My issue is not with the code.

It's with the registry entries that keep coming back after they have been deleted once a database is opened and now creating the TL in registry is not echoing in Access

Posted by: cheekybuddha Jul 30 2019, 07:48 AM

Ah sorry, I should have read the whole thread more closely! blush.gif

Are you sure you are using the new executable after updating the code and re-compiling? It sounds as if the original executable is being used.

Posted by: bazman1uk Jul 30 2019, 07:57 AM

I'll try copying the code into a new solution and trying again

Posted by: bazman1uk Aug 2 2019, 07:16 AM

made no difference

it must somehow be embedded into access itself as no matter what database I open or create, they reappear once access is open!! frown.gif

Posted by: isladogs Aug 2 2019, 12:36 PM

As nobody else seems to have experienced this issue, it may be that you should reinstall a fresh copy of Access.
Suggest removing it completely first to clear all existing TL registry data

Posted by: bazman1uk Aug 5 2019, 03:17 AM

Not as easy as that as it's a virtual O365 installation of Office 2016

Posted by: isladogs Aug 5 2019, 03:32 AM

You mean Office 365 installed on a virtual machine. If so, why is that any more complex to reinstall than any other workstation?

Posted by: pere_de_chipstick Aug 5 2019, 04:15 AM

Hi bazman1uk

QUOTE
.... My issue is not with the code.

It's with the registry entries that keep coming back after they have been deleted once a database is opened and now creating the TL in registry is not echoing in Access


Is this perhaps an attribute of the way Access works in your environment. Does it affect how your db runs and, if not, does it matter?

Posted by: isladogs Aug 5 2019, 04:38 AM

Just a thought and apologies if this sounds condescending, but you are checking the registry on the virtual machine (not the host workstation)...aren't you?

For info, I have multiple VMs each hosting different versions of Access on a variety of Windows versions.
Each requires the TLs to be independently set up. I'm pleased to say that none suffer from your issue.

Posted by: bazman1uk Aug 5 2019, 07:10 AM

Not a silly question, however yes it is on my machine.

Posted by: bazman1uk Aug 5 2019, 07:12 AM

Access works but for now adding the TL is not working in Access anymore. It used the clear the Enable Content but doesn't anymore since the reg keys keep coming back. A manually setting it within Access works ok, however the entry it puts in the registry is empty

Posted by: isladogs Aug 5 2019, 08:06 AM

Sorry but both of your last two replies are unclear, at least to me.
Can you please clarify whether the issue is on a virtual machine hosted on your PC ...or a VM on another PC ...or where?

Posted by: bazman1uk Aug 16 2019, 04:25 AM

Sorry. This is on a Virtual Machine. We use thin client boxes that connect to a virtual image hosting server to connect to our own pc image

Posted by: isladogs Aug 16 2019, 04:28 AM

Welcome back... smirk.gif
So you need to check the registry keys on the virtual machine ...not the workstation itself.

Posted by: bazman1uk Aug 19 2019, 08:39 AM

it IS on the virtual machine and completely separate from the host machine

It's not like a pc access it remotely.