How to get past Access security prompts transparently - using public (not self-signed) cert?

Question

How does one get a distributed Access application to run without first running the end user through the security prompts that tend to make me look a little silly for my choice of development platform? If I were writing in .NET, I surely would not have this problem, but of course, I would not get nearly so much programming done.

Facts:

1. I am now programming in Access 2016 but have a mixture of Access 2010, 2013, & 2016 both full & runtime, 32- and 64-bit clients running my apps. So I cannot use .accde files. And I really do not want to have to distribute multiple versions for different environments.
2. I prefer not to distribute .accdb files because I do not want to have half-knowledgeable folks trying to get into my code or even looking at tables directly. Yes, I know I can set the DB properties to disallow the bypass key, but to use this effectively, I have to create some back door for me to set it back lest I forget and disallow it in my development copy some day. So that is not my favorite idea. And I do put a password on my VBA, so it is protected. But I want to keep the clever types one step further from trying to tinker with things.
3. So, in the end, I put a password on my code and distribute .accdr files. This is some small protection against tinkering by users, and if anyone does know to just change the extension & open it, at least I know my VBA code is password-protected.

But I say all of that only because it was a further complication when I finally got tired of all the macro & security prompts, after many years of distributing Access application to a number of clients, and went through the whole Dunn & Bradstreet thing & purchased a Comodo code-signing cert for my business.

Nobody should have to know how to do the Trust Center stuff (they certainly don't have to do that with other programming platforms), and none of that is available in Access runtime anyway. So I have been doing this registry setting:

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security]
"VBAWarnings"=dword:00000001

But that is at the HKCU level, not HKLM, and every time a user changes computers, I have to deal with explaining the security prompts. It is rare enough that nobody ever remembers how, and often enough to be a pain.

So now I guess it comes down to two questions:

1. How do I distribute a digitally-signed .accdr file? I cannot open the .accdr file to create an .accdc file using File → Package & Sign as I did with an .accdb.
2. Is there any way, short of the .reg settings above, to disable the macro notification when I need this to work in both full & runtime versions of Access.

I guess I was just hoping that getting my business an officially code signing cert from Comodo would have solved at least some problems, but I fear that either my own ignorance here or the actual facts are precluding me from being successful in this endeavor.

Am I even close to a solution here? That is, it seems that I am actually signing the .accdc file, not the .accdb, and there is no way I can see to get an .accdr out there that will respect the certificate I am trying to apply.

Answer 1

Hi Brian,

I thought all you have to do is go to the Registry and add your app folder as a Trusted Location to avoid all the security warnings. Have you tried that?

Just curious...

Answer 2

Check out the Registry locations here, including executables to do the job for you:

Trusted Location

Hope it helps...

Answer 3

.theDBguy: this is true (registry settings), but that applies only to my computer. I have these applications distributed to dozens of other computers at my clients' offices, and the settings are in HKEY_CURRENT_USER, not HKEY_LOCAL_MACHINE. I am getting tired of always having to provide version-specific .reg files and coach folks through applying them when they change from one computer to another.

Answer 4

And I should clarify that the Trusted location will be different for each client office, since the UNC and/or mapped path to the location where my FE DB resides will be different for each of them.

This is on top of the fact that, as I noted in my original post, I need to distribute this to Access 2010, 2013, & 2016, full & runtime, 32- and 64-bit. I gave up using Trusted locations long ago in favor of the one .reg entry that I posted in my original post and that precludes any need for tinkering with Trusted Locations:

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security]
 "VBAWarnings"=dword:00000001

With that, there is no need to set Trusted Locations per computer or user. But, of course, that is still unique to at least the Access version (14.0 / 15.0 / 16.0), and possibly to the Office architecture (32/64-bit). The whole point of my post is how to get my applications to run on other computers without having to be involved in every installation. I know I could write a batch file to determine the Access version & architecture, check for the existence of the relevant registry entry above, insert it if it does not exist, then open my application. But I am trying to present Access like a normal & professional application, and I thought there might be some benefit to getting my code-signing certificate, but it seems that it does nothing to reduce the amount of babysitting necessary to get my application to look professional.

It seems that, after 20 years of programming in Access from versions 97 forward, it is still far less than acceptable as a platform for creating & distributing applications to other parties. I can accept the requirement to have Access full or runtime installed; that is not all that different from requiring .NET or Visual C++ libraries as a prerequisite for other application installations. But the whole matter of security leaves me looking like a child in relation to my clients who expect me to get them a product that can be opened from any computer on their network having Access full/runtime installed without all this user-profile-specific preconfiguration.

Answer 5

I think one option would be to create an installer that includes the registry setting that you already use; and have people use the installer to set up your apps whenever they arrive at a new pc. That's what I do.

Answer 6

But we do not install these applications. There are often many different Access applications for a single client's office, and my apps are not core applications; they are integration applications that work with ODBC drivers to add functionality and/or data not built into the other core applications.

The Access runtime or full version and required ODBC DSNs are installed on all client computers when initially set up, and we just place the .accdb or .accdr file in an appropriate network location and push out a shortcut to the users form the server using a batch file that copies the shortcut to the workstations. We do not really want multiple copies of these Access applications floating around on individual computers for the obvious reason that it would require some serious version control. Yes, I suppose I could do that, but my plate is full of user-functionality code requests, and I never seem to get around to rewriting these many applications in ways that accommodate Access' weakness for leaving users stranded with regard to platform issues like this.

And we are talking about 50 computers in at least one case, and perhaps 60+ users on the domain. They will never remember how to dig around for the installer and are not administrators on their own computers anyway.

So I assume this means that I am left with this option that I have been trying to avoid for a long time: do not provide users with shortcuts to the actual Access applications, but instead to a .bat or .cmd file that does this:
•Checks the version/architecture of the Access installation
•Determines if the required HKCU registry entry exists for that particular Access version/architecture
•Write the registry entries if required.
•Opens the Access .accdr/.accdb file

That is not actually a bad idea from a functionality perspective, but for two things:
•I still think it looks somewhat less than professional to interpose normal application execution with batch or command files. I guess I can live with that, though. It is not as though I can write one Access file to do this work for me, since that itself would be hostage to the same limitations.
•I think I will need some help figuring out how to determine the version & architecture from a .bat/.cmd file.

Answer 7

So my hope now is that someone here has had to deal with all of this before and can provide some help with command-line statements to determine the Access level & architecture.  I have done many batch files in the past, but not a lot that involve branched logic and none that determine application level/architecture. If nobody here has no specifics, I can put it up on a command-prompt forum.

It has always seemed risky to solve the problem of ensuring my clients that my code is non-malicious by disabling macro warnings, since this obviously leaves the user vulnerable to any other Access application that could be malicious or just downright poorly-written.

I was hoping that going to the work and expense of proving that I am a legitimate business and getting the publicly-trusted code-signing cert would have solved this problem. But that all now turns out to be entirely irrelevant to the problem, since it does not solve the one problem it really needs to solve, and now I fail to see any purpose in the code-signing cert at all. I am not about to learn another coding language to make up for this deficiency in Access as a development platform, but I sure wish something would be done to make it look less cobbled-together and more legitimate to the client. It is as though it was never really written to be used for generally-distributed applications, only for deployment on developers' computers and networks where IT staff will be available to tinker with registry settings at every user move from one computer to another.

Answer 8

Brian:

Would it be helpful if VBA code could automatically add your application to the users Trusted Locations registry on their computer without having to do it manually when the application is opened? If that would be helpful to you, respond and I can provide it. I have been using it for years. I can't remember where I got it, but if that would help your anxiety, I would be happy to post it here.

Answer 9

That might help, but here is the question: would it be able to add the location without first prompting them to allow Macros? The goal here is to make opening Access applications transparent to end users.

I love Access for its simplicity of development, but this one element always seems to require some tinkering with settings.

Answer 10

It doesn't have anything to do with Allow Macros. I don't know anything about your application, but just copy it to a module and Call it when the application opens with an Autoexec forms On Open event using:

Call AddTrustedLocations

Of course, if you don't have an Autoexec form that opens when the application is run, then you will need to create one. I will assume you know how to do that. Make sure you test the code on your own machine, of course. Delete the Trusted Location where the application resides and then after you add the code and run it, check your Trusted Locations settings to make sure it was added.

As to the Allow Macros setting you cannot set this with VBA code that I could find. It should be set when the application is developed and before it is distributed. Here is the Public Function:

Public Function AddTrustedLocation()
On Error GoTo err_proc
'WARNING:  THIS CODE MODIFIES THE REGISTRY
'sets registry key for 'trusted location'
Dim intLocns As Integer
Dim i As Integer
Dim intNotUsed As Integer
Dim strLnKey As String
Dim reg As Object
Dim strPath As String
Dim strTitle As String
strTitle = "Add Trusted Location"
Set reg = CreateObject("wscript.shell")
strPath = CurrentProject.Path
'Specify the registry trusted locations path for the version of Access used
strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(Application.Version, "##,##0.0") & "\Access\Security\Trusted Locations\Location"
On Error GoTo err_proc0
  'find top of range of trusted locations references in registry
  For i = 999 To 0 Step -1
      reg.RegRead strLnKey & i & "\Path"
      GoTo chckRegPths        'Reg.RegRead successful, location exists > check for path in all locations 0 - i.
checknext:
  Next
  MsgBox "Unexpected Error - No Registry Locations found", vbExclamation
  GoTo exit_proc
chckRegPths:
'Check if Currentdb path already a trusted location 'reg.RegRead fails before intlocns = i then the registry location is unused and 'will be used for new trusted location if path not already in registy
On Error GoTo err_proc1:
  For intLocns = 1 To i
      reg.RegRead strLnKey & intLocns & "\Path"
      'If Path already in registry -> exit
      If InStr(1, reg.RegRead(strLnKey & intLocns & "\Path"), strPath) = 1 Then GoTo exit_proc
NextLocn:
  Next
  If intLocns = 999 Then
      MsgBox "Location count exceeded - unable to write trusted location to registry", vbInformation, strTitle
      GoTo exit_proc
  End If
  'if no unused location found then set new location for path
  If intNotUsed = 0 Then intNotUsed = i + 1

'Write Trusted Location regstry key to unused location in registry
On Error GoTo err_proc:
  strLnKey = strLnKey & intNotUsed & "\"
  reg.RegWrite strLnKey & "AllowSubfolders", 1, "REG_DWORD"
  reg.RegWrite strLnKey & "Date", Now(), "REG_SZ"
  reg.RegWrite strLnKey & "Description", Application.CurrentProject.Name, "REG_SZ"
  reg.RegWrite strLnKey & "Path", strPath & "\", "REG_SZ"
exit_proc:
  Set reg = Nothing
  Exit Function
err_proc0:
  Resume checknext
err_proc1:
  If intNotUsed = 0 Then intNotUsed = intLocns
  Resume NextLocn
err_proc:
  Resume exit_proc
End Function

 

Answer 11

So my hope now is that someone here has had to deal with all of this before and can provide some help with command-line statements to determine the Access level & architecture.  I have done many batch files in the past, but not a lot that involve branched logic and none that determine application level/architecture. If nobody here has no specifics, I can put it up on a command-prompt forum.

Hi Brian,

I am guessing Albert may have been down that road before and has a lot of experience that might be of help to you.

With regards to using a bat/vbs/cmd file to launch your app looking less professional and also not wanting to learn a new programming language, I seem to remember using a program in the past where it basically took the batch file and converted it into an EXE file. If you can do this, it might help with the "professional feel" of your application.

Just my 2 cents...

Answer 12

Thank you. Part of the issue is that I am a half IT/network support consultant and half Access programming. When I wear my system integrator hat, I really hate programs that take all kinds of custom tinkering. They just seem flaky to me--such things as having to run .reg files manually or set rights to folders. And the whole Enable Macros / add Trusted Locations thing really needs to be transparent to the end user and, hopefully, even to the IT professional installing the software; anything less just gives me the feeling that the developer is not really playing in the big leagues, that the developer is just patching stuff together.

Now, I know I put far too much work into my Access applications, and they do far too many things (lots of Windows API calls, file/FTP transfers, ODBC connections etc) for it to be truly minor league stuff, but I complain all the time when developers send me other applications that take all of this custom knowledge & tinkering...so I am trying to avoid being guilty myself.

And, just for the record, I still think it is a funky answer on Microsoft's part that the official way to get an application to quit complaining about macros being dangerous is not for the developer to get a code-signing certificate (the way one would get rid of security messages on web pages by getting a trusted SSL cert), but to disable that bit of security or be required to hard-list the location as Trusted--even though only this one file (my Access app) is truly to be trusted at that location. It just seems like shoddy underlying platform design to me and tends to make all my clients assume that if they had two minutes' more time every day, they could just as easily pop out an application as I can.

Answer 13

I can’t speak for the other frequent contributors to this forum, but for my part, if you consider the whole forest and not just the individual trees, you will find ACCESS is a pretty good product for the money. The engineers provided a way to use the product in a multi-user environment (assuming you follow the procedures the engineers suggest) and they also provided a way for developers to create applications for users who do not have the full version of ACCESS installed on their computer for free. The engineers also needed to consider how any user/developer might install and use it under the various operating systems in use throughout the world. Also, some users might use macros only, some VBA code only, some both and some neither.

Having said all that, Trusted Locations has been an issue for developers/users since the inception. There have been hundreds of comments about that posted here. I can’t tell you exactly why Trusted Locations is part of ACCESS Options, but I don’t believe having to deal with it should be considered “unprofessional” or “just patching stuff together”. Given the complexity of the program itself, the various versions of ACCESS in use, the various operating systems and various data security issues in play all over the world, some “custom tinkering” is just inevitable.

As to Enable or Disable macros, I always just Enable them and ignore the warning comment. We have many more security concerns to worry about than if some hacker can insert some malicious code because macros are enabled. Once they are set in the development file, I have never received any warnings at all, but I use version 2007, so your version may have different settings. As far as I am concerned, they could get rid of the enable/disable macro settings altogether and for that matter, Trusted Locations as well. Securing data should not be part of ACCESS anyway. There are just too many variables. Data security is the responsibility for the developer/user and the client, not the software program.

I just wanted to give some perspective and wish you the best of luck with your project.