Access &amp;quot;A potential security concern has been identified&amp;quot; when opening DBs off intranet - GPO??

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Oh no! Is that true? I won't be able to use thjis method, because our users have roaming profiles, and so the machine name will be different every time.

We use Citrix XenApp to publish a WS2008R2 desktop to a thin client device. It's only the desktop being published, i.e. not a full VDI, and it's from this desktop that the user will be launching these shortcuts.

There are 50-odd XenApp servers for 2000-odd users. So, the only way of getting your solution to work would be if I made a GPO with preferences, created 3 registry entries in it - for the first XenApp server, but do this 50 times, one for each server - so ending up with 150 regiistry entries filling up the users' profiles.

Is there no other way? Anyway, can't this be a Machine setting rather than an user one?

Thanks for your input.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Other things about trusting.
The trust center has trusted locations...but there are also trusted publishers.
You may be able to digitally sign your db's so that they are trusted by the client.
Given the scope of your problem, that may be simpler.

SOLUTION

johnb6767

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I don't know that you can set a UNC as a Trusted Location, but if so I don't see any reason why you couldn't do that.

When I put a mapped drive in, it auto-magically changes it to a UNC. In the GUI there is a checkbox for 'allow trusted locations on my network'. If you're reghacking, the appropriate setting for that needs to be worked out too

johnb6767

Just adding the FQDN to the Intranet Zone should be sufficient...

Only reason I suggest this, is that you can often run into Authentication issues on Intranet sites, and if you change settings like the Integrated Authentication methods to the Trusted Sites, you might break some websites.....

Least having them separated, you can manage settings individually.....

ASKER

Right, I've started from the beginning - adding the UNC path of the DB folder into group policy.

I've added it to User Config -> Admin Templates -> Windows Components -> Internet Explorer -> Internet Control Panel -> Security Page -> Site to Zone Assignment List

First I added \\DFS-share\DBfolder\* and given it a value of 1 (Local Intranet), but this makes no difference. Then I tried to give it a value of 2 (Trusted Sites Zone), but then I get a prompt that didn't appear before - please see attachment for this. I don't know why putting it in the Trusted Sites zone would cause this.

Any ideas?
Trusted-Zone.JPG

This is the publisher warning. You can set the Macros Security to Low for your workstations, but I'm not sure you can do that on a GP basis, and of course any security changes expose you to more risk.

You could always sign the databases with a commercial Digital Certificate. You may already have one, if you're working in a large corporate environment, or you can purchase one fairly cheaply these days. You could also use as self-certificate, but you must export and install several files on each machine in order to make this work.

ASKER

Right, macro security in Access 2003 can be very easily set with Group Policy, however, in Access 2010 I think they've changed how it deals with security from top to bottom, and as a result there is no Macro Security as such in Access 2010.

Do you know what the equivalent of Macro Security would be in Access Runtime 2010?

Thanks.

The concepts are the same as before, but the way you get to them has changed:

http://www.ageesw.com/macro.htm

I'm not sure how you'd do this with the Runtime, however.

From
http://www.pcreview.co.uk/forums/bypass-security-warnings-access-runtime-t1671488.html

<<<<
By far the easiest way is to start the database via a script file which
sets the macro security level to low for that single invocation of
Access. This does not require a certificate, or a registry change, and
it does not affect any other database(s) - just the one being started
by that script.

Eg. in VBScript:

dim o
set o=createobject ("Access.Application")
o.automationsecurity=1 ' set macro security LOW.
o.opencurrentdatabase "full path to your database"
o.usercontrol=true
set o=nothing

>>>

The HiTechCoach has a link off his site how to add Trust Center locations via VBA.
http://www.utteraccess.com/wiki/index.php/AddTrustedLocation
Kudos to THTC and Doug Steele

Public Function AddTrustedLocation()
On Error GoTo err_proc
'WARNING:  THIS CODE MODIFIES THE REGISTRY 
'sets registry key for 'trusted location'
'Source http://www.utteraccess.com/wiki/index.php/AddTrustedLocation

  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:
  MsgBox err.Description, , strTitle
  Resume exit_proc
  
End Function

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

johnb6767

http:#36559603

That dialog is notrmally seen when the ADS Zone Identifier has tagged the file as being from the internet/another machine. Right click the file>properties, and see if there is an "unblock" button....

ASKER

OK, now I'vw gotten rid of the "Open File - Security Warning" - it was just something that needed to be adjusted in my Trusted Sites group policy settings. I am now back to having the information bar showing...

"This database has been opened read-only. You can only change data in linked tables..."

So now it doesn't matter if the "Site to Zone Assignment List" setting of my IE group policy has the share set as Intranet of as Trusted Sites - the Read-only warning occurs regardless.

I've been reading your suggestions above, and, Argh!! This is typical Microsoft - making a meal out of something that should be very simple to do!

We have about a hundred of these databases, and at the moment we are using Citrix XenApp (published content) to leave a shortcut to various databases on the desktop of the relevant users.

So I'll have to forget about having them digitally signed I think as it would take too long.

Nick67 - The "Allow trusted locations on my network" is already Enabled via Group Policy.

johnb6767 - No, there isn't an Unblock button in the propertiers of the .ade file.

After writing all this, I tried moving one of the databases to a non-DFS share on another server and running it from there, and it didn't show a warning - great. So I thought it might be a DFS issue. But not, because I then bypassed DFS and ran it directly from the DFS source location, and it still gave a warning.

So, what can be the difference between one server and onther, in terms of security? Can you suggest places I should check? I think we can rule out IE Security Zones, and Access 2010 Trusteed Locations, as being the issue here - because the server on which it works is not listed in either of these.

Thanks.

ASKER

Further testing has concluded that it's a permissions issue. Users can do what they want on their home share (which is where it worked without warning) but only have read access on our 'software distribution' share, for obvious reasons.

But, when these .ade are opened normally, no file is created (i.e. no lock file or anything), so I don't see why this would need modify permissions. And I don't see why the .ade file would need to be modified upon opening, especially since it is only connecting to an SQL back-end.

Any ideas?

What is happening inside any MSys tables may not be readily apparent, amongst other things.
And the lock file may not being created BECAUSE of the permission issue, and that may be throwing the read-onlyness

ASKER

No, I can confirm that the lock-file doesn't appear if the .ade is opened in a folder with full permissions - even when all hidden and system files are showing.

<"This database has been opened read-only. You can only change data in linked tables..."
So now it doesn't matter if the "Site to Zone Assignment List" setting of my IE group policy has the share set as Intranet of as Trusted Sites - the Read-only warning occurs regardless.>
<but only have read access on our 'software distribution' share, for obvious reasons.>

If they are opening a file in a read-only location, are you surprised that a read-only warning is coming up?

ASKER

No, but there should be a way of switching this off, because I know the .ade file doesn't need to be modified. The .ade files have been fairly locked down, and multiple users can open them simultaneously.

<No, but there should be a way of switching this off>
I don't know of one.
All the Office programs slobber-knock you if they are read-only.
It doesn't matter if you don't want to change the file. The warning is that you can't