Solved

Setting up Certificate on Access 2007 Runtime workstations to avoid Security Notice with Frontend DB

Posted on 2008-10-25
17
1,600 Views
Last Modified: 2013-11-29
I've done a bunch of reading on the security and cerficates used in Office VBA project but I'm still fuzzy on if I can get rid of the security notice:
"A potential security concern has been Identified"
Warning: trustworth source etc etc....
File Path: xxxx\xxx\

I have a split access 2007 operating only within my own domain (sbs 2003).  Both the FE/BE are located on a 2003 memeber server that is mapped as drive J: on all the domain workstations (XP sp3).

Also possibly worth mentioning is I have written a small visual basic "launcher" that is located on each workstation and it looks into the J: folder where the FE is, checks the file name and runs the latest version of the FE. The file name contains the day and version like this: ProgramX_08.10.24.02.accdb

I have 4 workstation that are using the access runtime and these system put up the above security warning where system with full blown access (office 2007) do not. I think I can setup the certificate authority on my domain server and sign my FE so this security message does not appear? Or can I sign the FE using the Office VBA Certificate tool and export this certificate to the other runtime workstations. I've tried a lot of different ways of signing and importing certs etc that I have read about in various articles but no success.

I wouldn't might getting an actual cert for macro signing but it looks like they are $400 a year and that's a bit much since I don't distribute projects outside my company. I'm all for security but it seems we are damn close to not being able to run projects on our own computer as we are creating them. Thanks in advance.

-Ralph
 
0
Comment
Question by:Raland9966
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 22804396
For internal company needs, you can create your own digital certificate for no cost, give it to your users and have them accept the certificate one time to avoid this problem.

http://msdn.microsoft.com/en-us/library/bb226686(office.11).aspx

But digital certificates aren't used in Access 2007. You should establish a trusted folder on each user's machine where you're installing the front end database (look under Access Options, Trust Center, Trust Center Settings). This will tell Access 2007 to trust any database located there.

While you've already created a deployment solution for your front-end database, you may still want to consider our Total Access Startup program. It lets you centrally manage all your Access applications and makes sure each user runs the Access version you specify and automatically deploys updates of the front end database to each user's desktop. More info here: http://www.fmsinc.com/MicrosoftAccess/VersionLauncher.asp

Hope this helps.

Luke
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 200 total points
ID: 22804397
The Access macro (in)Security is absurd - just as much so as it was back circa 2003 when it became part of the platform.

I suppose you have seen these links:

http://office.microsoft.com/en-us/access/HA100319991033.aspx#3  'Trusted Locations
http://office.microsoft.com/en-us/access/HA102564121033.aspx  'Trusted DB

http://msdn2.microsoft.com/en-us/library/bb421308.aspx  'A2007 Macro Security

'Security Considerations
http://msdn.microsoft.com/en-us/library/bb421308.aspx#OfficeAccess2007SecurityConsiderations_EnablingExecutableContentDatabases  

I also have the launcher scheme - except mine *always* copies the Master FE from the server to the work station - that way I *never* have to worry if a given workstation has a corrupted mdb - even though it may be the 'latest' version.  Still - I and a colleague of mine of yet - even with a vb exe pre-launcher - been able to completey get around the warning - and that's in A2003.  It's kind of a catch 22.  So, I simply set the (in)Security to LOW on the few systems that have A2003 (most are A2002).

mx
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22804454
Isn't LukeChung violating the membership Agreement?

"Advertising, promoting in any way or offering to sell any goods or services for any commercial purpose. "
0
 
LVL 75
ID: 22804574
jm ??

mx
0
 

Author Comment

by:Raland9966
ID: 22804626
--------------------------
LukeChung-FMS:
For internal company needs, you can create your own digital certificate for no cost, give it to your users and have them accept the certificate one time to avoid this problem.  
------------------------
This is want I'm trying to do, I need a little more specific details regarding "create your own digital certificate " and "give it to your users"...

I'm assuming you are talking about using the Office Tool and creating a "self Signing" certificate. which I have done. I don't know how to successfully export this cert off my workstation and import it onto the station with just the runtime. I think this is done in the bowels of interent explorer's Content/Certificates (obviously the first place a person would look for an office cert).

As for you suggestion on the access deployment solution. I seen several of these mentioned in other articles. What is the advantage to having the FE loaded to each workstation verses my setup were all the users run a single FE located on server? I can see where this might be needed with a large number of users, in my case I don't think i'll ever exceed 10 concurrent users.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22804632
mx:
ID:22804396; "While you've already created a deployment solution for your front-end database, you may still want to consider our Total Access Startup program. It lets you centrally manage all your Access applications and makes sure each user runs the Access version you specify and automatically deploys updates of the front end database to each user's desktop. More info here: http://www.fmsinc.com/MicrosoftAccess/VersionLauncher.asp" Hope this helps.

Luke

I would never advertise my services in a question because it violates the membership agreement. I have at times advised of a product that might work in a specific scenario, but nothing that I own an interest in , or know a person that has an interest in that product.

Jim
0
 

Author Comment

by:Raland9966
ID: 22804639
As far as any memebership breach, It seems a FE manager of some type is integral to a good solution to this security issue. I've seen several others mentioned in my reading, many of which are free. No worries here. It's not like he chimed in and said he had a solution and I could have in exchange for $20 to his paypal accout.
0
 
LVL 75
ID: 22804657
"What is the advantage to having the FE loaded to each workstation verses my setup were all the users run a single FE located on server? I "

Many, none the least of which is minimizing the chance of corrupting the FE (and/or BE) - especially if a user has an abnormal shutdown.  Another reason is eliminating or minimizing the 'Record Locked, Can't  Update' error message.

jm ... as far as Luke, well ... kind of a special case maybe - considering the overall contribution of FMS to the Access community.

Personally ... regarding Macro (in)Security ... it's been a joke since A2003 and is *easily* circumvented.  hence, AFAIK ... Digital Certs are just a money making scam conjured up by a handful of 3rd party companies in conjunction with M$ - and are completely worthle$$.

mx
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 75
ID: 22804670
Hey Luke ... how are things?  May the 1.0 Spell Checker live on :-)

joe
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 100 total points
ID: 22804732
"What is the advantage to having the FE loaded to each workstation verses my setup were all the users run a single FE located on server?

My gripe in that scheme is corruption and I experienced enough of that until I quit using it.

mx: i've contributed more to FMS than they have to me; I have nothing against FMS. I use and recommend their products. But I have no interest in FMS.

Jim
0
 

Author Comment

by:Raland9966
ID: 22804742
I've seen the macro security referred to as "(in)Security " several time now. is this shorthand for PITA ;) ?
0
 
LVL 75
ID: 22804774
""(in)Security '

= Insecurity =  False Security.

mx
0
 
LVL 10

Assisted Solution

by:LukeChung-FMS
LukeChung-FMS earned 200 total points
ID: 22805650
Concerns noted.  Don't mean to offend anyone.  Just trying to help with information.  I don't come here very often, so hopefully we won't blow this out of proportion.

Would appreciate clarification on what I can or can't say about our commercial products. I look at our commercial products differently from services (which I wouildn't promote here), since the products cost a fraction of what it would take to build from scratch and would seem to be quite helpful in certain circumstances.  

It would be odd if others were allowed to recommend our products but I couldn't, but I'll abide by whatever the rules dictate.

Thanks to all who've supported us and recommend our solutions. Speller 1.0 very far back. We actually just released the 2007 version (to be announced next week).  Having the experts appreciate and recommend our solutions is an important part of our ability to create those products for the community, so thanks to all.

===============

Back to this issue:

At FMS, we probably have more experience with digital signatures in Access databases than any other firm.  Yes, we rely on 3rd party certifications and their annual fees, and I won't go into what I really think about that.  The whole point behind the certificates was to have a way to authenticate module code was valid and unchanged. For some organizations, that's critical. Turning security levels down to low is not an option since it's across all of Office.

The self certification process works fine in Access 2003 for user's you know -- it doesn't work for our commercial products for the general public. When the database is run by someone for the first time, they are prompted to accept the certificate. If they do, they are no longer nagged by Access 2003 for that database or any other database using that certificate.  For 2007, the trusted location is better.

As for front end database being on each user's machine, that has significant performance gains.  It also lets you use/create temporary tables in the FE that each user can have independent of others. If you simply have a split database and everyone uses the same front-end, any temporary data stored in the front-end tables could be overwritten by others.  Maybe in the middle of the process where you expect that data to be static.

Because of the advantages of deploying the front-end database to each user's desktop and the challenge of making sure any updates you make are distributed to everyone easily, a centralized, automated way to manage that is necessary. You can build your own solution or find a commercial one <g>.

Luke
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22805708
Hi Luke,

You didn't offend me, but if I abide by the membership agreement, I can't self promote my practice here. If I recommend your products it's of no consequence because I have no interest in FMS. I have purchased your products, and my recommendation of your products has sent sales your way via my clients and will continue.

Regards,

Jim

0
 
LVL 75
ID: 22806267
"Speller 1.0 very far back."
Luke ... I beta tested  1.0 and 1.1, remember.  Last time we met was at the San Diego Access User Group - where I won the FMS product - and you later exchanged it for the Admin product.

joe  
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 22806985
Hi Joe,

Yes I remember you.  Glad to be working with a Genius!

Hope Total Access Admin is working for you, and thanks for your support.

Luke
0
 

Author Closing Comment

by:Raland9966
ID: 31509982
I'm going to check out a FE deployment solution as mentioned, to hopefully just avoid the (in)Security issue. I split the points, hopefully to everyone's satisfaction. Want to keep everyone happy since I'm going to be back with a lot more question. cheers :)
-Ralph
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now