Solved

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

Posted on 2008-10-25
17
1,605 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:Luke Chung
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 - Microsoft MVP, Access and Data Platform) 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 
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:Luke Chung
Luke Chung 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:Luke Chung
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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