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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Deleted Trusted Locations Reappearing When Database Opened, Access 2016    
 
   
bazman1uk
post Jul 26 2019, 04:24 AM
Post#1



Posts: 17
Joined: 26-July 19



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
Go to the top of the page
 
Start new topic
Replies
pere_de_chipstic...
post Jul 26 2019, 06:31 AM
Post#2


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


welcome2UA.gif

You might find this article in the UA Wiki will help you: Add Trusted Location.

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

--------------------
Warm regards
Bernie
Go to the top of the page
 
isladogs
post Jul 26 2019, 09:01 AM
Post#3


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 03:01 AM
Post#4



Posts: 17
Joined: 26-July 19



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!!!
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 03:53 AM
Post#5



Posts: 17
Joined: 26-July 19



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???
Go to the top of the page
 
pere_de_chipstic...
post Jul 29 2019, 04:49 AM
Post#6


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 05:10 AM
Post#7



Posts: 17
Joined: 26-July 19



Hi Pere,

yes I know that and that IS the code I'm using. It's jut not doing as expected
Go to the top of the page
 
pere_de_chipstic...
post Jul 29 2019, 05:34 AM
Post#8


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 05:56 AM
Post#9



Posts: 17
Joined: 26-July 19



Mine is definitely HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Access\Security\Trusted Locations
Go to the top of the page
 
pere_de_chipstic...
post Jul 29 2019, 06:20 AM
Post#10


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 07:05 AM
Post#11



Posts: 17
Joined: 26-July 19



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

Go to the top of the page
 
pere_de_chipstic...
post Jul 29 2019, 10:04 AM
Post#12


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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?


This post has been edited by pere_de_chipstick: Jul 29 2019, 10:06 AM

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Jul 30 2019, 02:36 AM
Post#13



Posts: 17
Joined: 26-July 19



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
This post has been edited by pere_de_chipstick: Jul 30 2019, 04:12 AM
Reason for edit: removed potentially sensitive information in 'spath '
Go to the top of the page
 
pere_de_chipstic...
post Jul 30 2019, 04:05 AM
Post#14


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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.

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Jul 30 2019, 05:30 AM
Post#15



Posts: 17
Joined: 26-July 19



Hi,

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

The network path it is setting to is the sPath variable
Go to the top of the page
 
isladogs
post Jul 30 2019, 05:37 AM
Post#16


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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 Jet ShowPlan Manager app needs to be run in that setting

--------------------
Go to the top of the page
 
cheekybuddha
post Jul 30 2019, 05:59 AM
Post#17


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
bazman1uk
post Jul 30 2019, 07:26 AM
Post#18



Posts: 17
Joined: 26-July 19



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
Go to the top of the page
 
cheekybuddha
post Jul 30 2019, 07:48 AM
Post#19


UtterAccess VIP
Posts: 11,524
Joined: 6-December 03
From: Telegraph Hill


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.

--------------------


Regards,

David Marten
Go to the top of the page
 
bazman1uk
post Jul 30 2019, 07:57 AM
Post#20



Posts: 17
Joined: 26-July 19



I'll try copying the code into a new solution and trying again
Go to the top of the page
 
bazman1uk
post Aug 2 2019, 07:16 AM
Post#21



Posts: 17
Joined: 26-July 19



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
Go to the top of the page
 
isladogs
post Aug 2 2019, 12:36 PM
Post#22


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
bazman1uk
post Aug 5 2019, 03:17 AM
Post#23



Posts: 17
Joined: 26-July 19



Not as easy as that as it's a virtual O365 installation of Office 2016
Go to the top of the page
 
isladogs
post Aug 5 2019, 03:32 AM
Post#24


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
pere_de_chipstic...
post Aug 5 2019, 04:15 AM
Post#25


UtterAccess Editor
Posts: 10,507
Joined: 8-November 07
From: South coast, England


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?

--------------------
Warm regards
Bernie
Go to the top of the page
 
bazman1uk
post Aug 5 2019, 07:12 AM
Post#26



Posts: 17
Joined: 26-July 19



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
Go to the top of the page
 
isladogs
post Aug 5 2019, 08:06 AM
Post#27


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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?

--------------------
Go to the top of the page
 
bazman1uk
post Aug 16 2019, 04:25 AM
Post#28



Posts: 17
Joined: 26-July 19



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
Go to the top of the page
 
isladogs
post Aug 16 2019, 04:28 AM
Post#29


UtterAccess VIP
Posts: 1,693
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Go to the top of the page
 
bazman1uk
post Aug 19 2019, 08:39 AM
Post#30



Posts: 17
Joined: 26-July 19



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

It's not like a pc access it remotely.
Go to the top of the page
 

Posts in this topic
- bazman1uk   Deleted Trusted Locations Reappearing When Database Opened   Jul 26 2019, 04:24 AM
- - pere_de_chipstick   You might find this article in the UA Wiki will he...   Jul 26 2019, 06:31 AM
- - isladogs   Just out of interest, what was wrong with hard cod...   Jul 26 2019, 09:01 AM
|- - bazman1uk   The help gave me the impression that the LocationX...   Jul 29 2019, 03:01 AM
|- - bazman1uk   In addition, now if I delete all TLs from the regi...   Jul 29 2019, 03:53 AM
|- - pere_de_chipstick   Hi Am not sure why your Location50 re-appeared, b...   Jul 29 2019, 04:49 AM
|- - bazman1uk   Hi Pere, yes I know that and that IS the code I...   Jul 29 2019, 05:10 AM
|- - pere_de_chipstick   That is confusing! Have you checked the strLn...   Jul 29 2019, 05:34 AM
|- - bazman1uk   Mine is definitely HKEY_CURRENT_USER\Softwar...   Jul 29 2019, 05:56 AM
|- - pere_de_chipstick   Are you able to step through the code to check wha...   Jul 29 2019, 06:20 AM
|- - bazman1uk   Hi, I am a developer in various languages for ove...   Jul 29 2019, 07:05 AM
|- - pere_de_chipstick   Hi bazman1uk !! Is the code not adding ...   Jul 29 2019, 10:04 AM
|- - bazman1uk   Dim sPath As String 'Path to set as a...   Jul 30 2019, 02:36 AM
|- - pere_de_chipstick   Hi Bazman1UK I've looked at your code, and it...   Jul 30 2019, 04:05 AM
|- - bazman1uk   Hi, The reg KEY path IS the HKEY_CURRENT_USER...   Jul 30 2019, 05:30 AM
|- - isladogs   Although it seems not relevant here, restricted re...   Jul 30 2019, 05:37 AM
|- - cheekybuddha   Hi, Where is class/type 'My' defined? Wha...   Jul 30 2019, 05:59 AM
|- - bazman1uk   Hi, The code works fine. Well it did. It created ...   Jul 30 2019, 07:26 AM
|- - cheekybuddha   Ah sorry, I should have read the whole thread more...   Jul 30 2019, 07:48 AM
|- - bazman1uk   I'll try copying the code into a new solution ...   Jul 30 2019, 07:57 AM
|- - bazman1uk   made no difference it must somehow be embedded in...   Aug 2 2019, 07:16 AM
|- - isladogs   As nobody else seems to have experienced this issu...   Aug 2 2019, 12:36 PM
|- - bazman1uk   Not as easy as that as it's a virtual O365 ins...   Aug 5 2019, 03:17 AM
|- - isladogs   You mean Office 365 installed on a virtual machine...   Aug 5 2019, 03:32 AM
||- - pere_de_chipstick   Hi bazman1uk QUOTE .... My issue is not with the ...   Aug 5 2019, 04:15 AM
||- - bazman1uk   Access works but for now adding the TL is not work...   Aug 5 2019, 07:12 AM
||- - isladogs   Sorry but both of your last two replies are unclea...   Aug 5 2019, 08:06 AM
||- - bazman1uk   Sorry. This is on a Virtual Machine. We use thin c...   Aug 16 2019, 04:25 AM
||- - isladogs   Welcome back... So you need to check the registr...   Aug 16 2019, 04:28 AM
||- - bazman1uk   it IS on the virtual machine and completely separa...   Aug 19 2019, 08:39 AM
|- - isladogs   Just a thought and apologies if this sounds condes...   Aug 5 2019, 04:38 AM
|- - bazman1uk   Not a silly question, however yes it is on my mach...   Aug 5 2019, 07:10 AM
- - bazman1uk   Hi, that is the way I had it. Thanks   Jul 29 2019, 02:58 AM



Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 12:50 PM