Installer is not in trusted location; add to trusted location on install

[hecon5]

Stepping back to consider this a little more, this add-in is essentially a tool in the toolbox of a developer. The bridge that enables version control for Microsoft Access database development. The add-in (no longer) requires admin rights to install or remove, installs under the current user's profile (AppData), and works very similar to the built-in access wizards. I am just trying to understand why something like that would not be allowed. It seems like this is part of giving the team the tools required to do the job...

It has to do with that the "addin" toolbox is not configured/installed by our IT group (reasons are myriad). I am able to "install" the addon (and then it does show up in "Database Tools> Add-Ins > Version Control"), but get the following error when loading Access. Adding the file as a reference makes the link work, and loads VCS forms without issue.

I also want to keep the big picture in mind... I am happy to include a module to add some endpoints if that would help make this workable in your environment, but I guess I am hesitating a bit to do significant refactoring at the core level for something that is more of an edge case that benefits a very small percentage of users.

Let me know what you think. (I am also happy to discuss this offline or by phone/video call if that would be easier.) Thanks!

I don't think a significant refactor is required (nor warranted). I think the API calls as shown should suffice (honestly, if it really came down to it, I think just launchVCS, VCSVersion (should that be "VersionVCS" to be consistent?), and exportMySource would suffice for now. It would be nice to expose the build from source (this would allow a template file to autobuild), but that'd be gravy.

Originally posted by @hecon5 in https://github.com/joyfullservice/msaccess-vcs-integration/issues/119#issuecomment-758825739

[joyfullservice]

I have this nearly completed, but ran out of time today... I will keep you posted!

[joyfullservice]

@hecon5 - Could you test this for me? Add the following code block to the end of modAddIn and run VerifyTrustedLocation. After running this, try launching the add-in from the add-ins menu. Let me know if it works!

'---------------------------------------------------------------------------------------
' Procedure : VerifyTrustedLocation
' Author    : Adam Waller
' Date      : 1/12/2021
' Purpose   : The location of the add-in must be trusted, or the user will be unable
'           : to run the add-in. This function ensures that the path has been added
'           : as a trusted location after confirming this with the user. If the user
'           : declines to add as a trusted location, it warns them that the add-in may
'           : not function correctly.
'---------------------------------------------------------------------------------------
'
Public Function VerifyTrustedLocation() As Boolean

    Dim strPath As String
    Dim strVal As String

    ' Get registry path for trusted locations
    strPath = GetTrustedLocationRegPath

    ' Use Windows Scripting Shell to read/write to registry
    With New IWshRuntimeLibrary.WshShell

        ' Attempt to read the registry key
        On Error Resume Next
        strVal = .RegRead(strPath & "Path")
        On Error GoTo 0

        ' Check for existing value
        If strVal = vbNullString Then

            ' Get permission from user to add trusted location
            If MsgBox2("Add as Trusted Location?", _
                strPath, _
                "The add-in location must be trusted to use the add-in in Microsoft Access.", _
                vbQuestion + vbOKCancel) = vbOK Then

                ' Add trusted location
                .RegWrite strPath & "Path", FSO.GetParentFolderName(GetAddinFileName) & "\"
                .RegWrite strPath & "Date", Now()
                .RegWrite strPath & "Description", mcstrTrustedLocationName
                .RegWrite strPath & "AllowSubfolders", 0, "REG_DWORD"

                ' Return true
                VerifyTrustedLocation = True
            Else
                MsgBox2 "Trusted location NOT added", _
                    "The Version Control add-in may not function correctly.", _
                    "Please run the installer again if you would like to add the trusted location." _
                    , vbExclamation
            End If
        Else
            ' Found trusted location with this name.
            VerifyTrustedLocation = True
        End If
    End With

End Function

'---------------------------------------------------------------------------------------
' Procedure : RemoveTrustedLocation
' Author    : Adam Waller
' Date      : 1/12/2021
' Purpose   : Remove trusted location entry.
'---------------------------------------------------------------------------------------
'
Public Function RemoveTrustedLocation()

    Dim strPath As String
    Dim strVal As String

    ' Get registry path for trusted locations
    strPath = GetTrustedLocationRegPath

    With New IWshRuntimeLibrary.WshShell
        On Error Resume Next
        .RegDelete strPath & "Path"
        .RegDelete strPath & "Date"
        .RegDelete strPath & "Description"
        .RegDelete strPath & "AllowSubfolders"
        .RegDelete strPath
        On Error GoTo 0
    End With

End Function

'---------------------------------------------------------------------------------------
' Procedure : GetTrustedLocationRegPath
' Author    : Adam Waller
' Date      : 1/12/2021
' Purpose   : Return the trusted location registry path. (Added to trusted locations)
'---------------------------------------------------------------------------------------
'
Private Function GetTrustedLocationRegPath() As String
    GetTrustedLocationRegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
        Application.Version & "\Access\Security\Trusted Locations\" & mcstrTrustedLocationName & "\"
End Function

[hecon5]

Get a compile error on mcstrTrustedLocationName, looks like it's not defined anywhere?

[joyfullservice]

Get a compile error on mcstrTrustedLocationName, looks like it's not defined anywhere?

Ahh... Should be Private Const mcstrTrustedLocationName = "Office Add-ins"

Sorry, that was at the top of the module.

[hecon5]

SUCCESS!

[hecon5]

Ok, so this is awkward. I uninstalled the addon, and then when I re-ran with the updated code from master, it no longer ran. Took me a while to figure out, but need to add VerifyTrustedLocation to autorun AFTER it's installed.

Like this:

Public Function AutoRun() As Boolean
    Dim strMsgBoxTitle As String
    Dim strMsgBoxText As String

    If CodeProject.FullName = GetAddinFileName Then
        ' Opening the file from add-in location, which would normally be unusual unless we are trying to remove
        ' legacy registry entries.
        If IsUserAnAdmin = 1 Then RemoveLegacyInstall
    Else
        ' Could be running it from another location, such as after downloading
        ' and updated version of the addin. In that case, we are either trying
        ' to install it for the first time, or trying to upgrade it.
        If IsAlreadyInstalled Then
            If InstalledVersion <> AppVersion Then
                strMsgBoxTitle = "Upgrade Version Control?"
                strMsgBoxText = "Would you like to upgrade to version " & AppVersion & "?"
            Else
                strMsgBoxTitle = "Reinstall Version Control?"
                strMsgBoxText = "Version " & AppVersion & " is already installed, would you like to reinstall it?"
            End If

            If MsgBox2(strMsgBoxTitle, strMsgBoxText, "Click 'Yes' to continue or 'No' to cancel.", vbQuestion + vbYesNo, "Version Control Add-in") = vbYes Then
                If InstallVCSAddin Then
                    MsgBox2 "Success!", "Version Control System add-in has been updated to " & AppVersion & ".", _
                        "Please restart any open instances of Microsoft Access before using the add-in.", vbInformation, "Version Control Add-in"
                    CheckForLegacyInstall
                    VerifyTrustedLocation
                    DoCmd.Quit
                End If
            Else
                ' Go to visual basic editor
                DoEvents
                DoCmd.RunCommand acCmdVisualBasicEditor
                DoEvents
            End If
        Else
            ' Not yet installed. Offer to install.
            If MsgBox2("Install Version Control?", _
                "Would you like to install version " & AppVersion & "?", _
                "Click 'Yes' to continue or 'No' to cancel.", vbQuestion + vbYesNo, "Version Control Add-in") = vbYes Then

                If InstallVCSAddin Then
                    MsgBox2 "Success!", "Version Control System has now been installed.", _
                        "You may begin using this tool after reopening Microsoft Access", vbInformation, "Version Control Add-in"
                    CheckForLegacyInstall
                End If
                VerifyTrustedLocation
                DoCmd.Quit
            End If
        End If
    End If
    AutoRun = True

End Function

BUT WAIT...There's more!

I uninstalled/reinstalled a few times, and it's kind of inconsistent whether it remains trusted or not; sometimes I had to open the addin folder, launch the accda file and run VerifyTrustedLocation manually.

I'm going to do some more testing tomorrow after rebooting to troubleshoot and make sure I didn't fat finger something initially, too.

[joyfullservice]

It could also be a group policy setting that applies periodically to the workstation. If you have access to the registry, you might watch and see whether the key disappears...

[hecon5]

I'll keep an eye on it; in either case, I'll make a PR to add the registry location at the end as shown.

[hecon5]

(Copying in from other thread to keep consolidated, sorry for duplication.)

Additional troubleshooting: I have discovered that after installing/coping to Addins and adding as a trusted location, Access would not accept it as a trusted file until I'd gone to the addins folder and then opened the file, and "enabled" it. Once this was done, it appears to work nicely.

I'll test out if making a cmd script would launch the addin so the user could do this right away after install (and so they don't need to know where it is); this would cut down on steps needed to run the installer.

[hecon5]

Finally got back to this; I'm in the final stages of testing the batch file.

I'm going to do it in two steps; the OpenAddinFile will fire from the Autorun routine if VerifyTrustedLocationreturns true, and I'll add auto-close logic to AutoRun if a user opens the file from the Addin location.

After some iterative testing, I've had instances where if the filename changed (or just black magic) (due to my own incompetence), the file is not trusted even if the location is trusted when copying in a new one; this should help for any file changes.

OpenAddinFile will spool up a shell script to wait for the Addin .laccdb file and the Installer's .laccdb file to disappear (to reduce open Access instances; I've found several cases where if Access was open (at all), trusting the file didn't work.

[hecon5]

Need input: I've tried to use the With New IWshRuntimeLibrary.WshShell, but I keep getting err 438 "object doesn't support this property or method". I feel like there's something especially obvious I'm missing here. The message box displays the correct string, so I'm kind of lost.

    '(removed excess code to show intent, the rest of it is in PR)
   Dim cOpenAddin As New clsConcat

    With cOpenAddin
        .AppendOnAdd = True
        .Add "@Echo Off", vbCrLf
        'Build the rest of the string... removed here for brevity
          'BLAH BLAH BLAH (more stuff)
          ...

        With New IWshRuntimeLibrary.WshShell
            .Run .GetStr, vbHide, False
        End With
       MsgBox .GetStr
    End With

[joyfullservice]

.AppendOnAdd should be set to the character you want to add at the end of each call to .Add. Common examples would be:

.AppendOnAdd = vbCrLf
.AppendOnAdd = vbLf

Once you have set what you want added at each line, you can just call it like this:

With cOpenAddin
    .AppendOnAdd = vbCrLf
    .Add "@Echo Off"
    .Add "REM My interesting comment."
    .Add "REM Another line just because I can."
    'Build the rest of the string... removed here for brevity
      'BLAH BLAH BLAH (more stuff)
      ...

    With New IWshRuntimeLibrary.WshShell
        .Run .GetStr, vbHide, False
    End With
   MsgBox .GetStr
End With

Also, .Run is expecting a file name, if I recall correctly. You would want to use WriteFile to save the batch file, then execute it. You can see an example in the Git integration class.

[hecon5]

sigh... thanks! For some reason, I (wrongly) thought that this implementation would allow me to skip the file. I build the file for my other cases, but don't know where I was.

Testing this out now after my coffee.

[hecon5]

Ok, I think it's nearly there!

[hecon5]

Note, I also discovered an issue with the IDE names can't match; this was pulled in, too.

[hecon5]

Ok, so belay that last comment. Turns out it's caused by something else.

I am able to get the install launch steps to work when I build against master, and they "work" when I have the development version, too.

When I try to compile the dev branch, building from source and export no longer function like I expected.

That said, when I use dev branch, it will no longer export/import, and will just crash hard. I'm troubleshooting why that is, but it's rather difficult, as the application doesn't save any logs

[joyfullservice]

@hecon5 - One thing I have seen is if the add-in project gets corrupted during development it can crash Access when opening the file. If the installed version of the add-in is corrupted, it makes it harder to resolve since you can't build from source. 😄

If you start encountering anything strange, build the project from source. This cleans up the database and VB Project. If you get a crash while building, you might need to just download and install a working version of the add-in first, install that, build the dev branch from source, then re-open and install the newly built add-in. This should get you back in business with the latest version of the add-in.

[hecon5]

Well, if my chicken is running around, I can't very well put an egg on it...

[hecon5]

Well, this is interesting:

When I name the addin to something else (aka...ver control.3.2.2), installing it, and then building a (new) file from source, I get "29058", can't create a module for a form or report when an instance of the form or report is in browse mode.

I noticed that it checks to see if the file names are the same, perhaps they need to just name the addin project something wholly different while running build/export (aka...VersionControl addin) or something to that effect?

[joyfullservice]

Yes, there are some specific nuances when you are building the VCS project. You will see how I dealt with the project name in modAddIn.RunExportForCurrentDB() in conjunction with the API callback for when building from source. Development on the VCS add-in is expecting the original file and project name for all these things to work as designed.

[hecon5]

Ok, this is ready; last question on this and I think it's ready (I still am having issues with #130; but the installer works for me to ensure the file is trusted).

How long should we wait for the lock file to dissapear? Right now, it's set to an hour (testing), but I think perhaps 1-3 minutes is as long as we should wait before just deleting the lock file. To me, the most common cause of the lock file sticking around is Access crashed, so less is more, but also want to give Access/user time to close it before just doing things for them.

[joyfullservice]

@hecon5 - Can you do some testing with the latest dev build and confirm that it works as expected in your environment? If so, I think we may be ready to close this out.

[joyfullservice]

Here are some screen shots:

[hecon5]

Will check tomorrow, looks good!

[hecon5]

Checked; it works over here; just a single change of settings based on my findings, I'll let you choose.

[joyfullservice]

I made a change today to have the add-in install in its own dedicated installation folder. This takes care of the potential security concern of trusting the entire \Microsoft\AddIns folder, and paves the way for eventually adding the VSTO ribbon. The advantage of trusting the folder is that (in most environments) that will be the only security setting users will need to add. They won't need to re-trust the add-in each time it is upgraded. In your case, you would simply check the second box to trigger the additional launch and trust process.

If this looks good to you, and works in your environment, I think we can probably close this out! 😄

[hecon5]

Looks good; the open button works; see my "complaint" ;) but I can just have this built in my repo as I like without issue.