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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> 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
 
pere_de_chipstic...
post Jul 26 2019, 06:31 AM
Post#2


UtterAccess Editor
Posts: 10,509
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,707
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, 02:58 AM
Post#4



Posts: 17
Joined: 26-July 19



Hi, that is the way I had it. Thanks
Go to the top of the page
 
bazman1uk
post Jul 29 2019, 03:01 AM
Post#5



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#6



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#7


UtterAccess Editor
Posts: 10,509
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#8



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#9


UtterAccess Editor
Posts: 10,509
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#10



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#11


UtterAccess Editor
Posts: 10,509
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#12



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#13


UtterAccess Editor
Posts: 10,509
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#14



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#15


UtterAccess Editor
Posts: 10,509
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#16



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#17


UtterAccess VIP
Posts: 1,707
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#18


UtterAccess VIP
Posts: 11,541
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#19



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#20


UtterAccess VIP
Posts: 11,541
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
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 06:58 PM