Question

installing certificate using vba

Asked by: talonsblade

i work for a company that does not allow unsigned macros to be run on their computers.  i just ran into a problem with my certificates.  i have a word application that i created that allows users to connect to several adp applications to access data on our sql database.  this word document allows me to release updated versions of the applications and the users do not notice the updates because the word document downloads them automaically.

the problem i just ran into is that my certificate expired and i had to get a new one and i will run into this again in a year.  i have used makecert to create a certificate that lasts for 90 years so i will not have the problem again but i cannot figure out how to get this cert as a trusted publisher on the 150-200 user computers without visiting each computer myself.  i am using my short term cert as a stopover because it is already a trusted publisher but i would like to migrate to the long term cert.

Is there a way to install a certificate into the trusted root certificate store folder using vba

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-10-14 at 08:29:48ID24811604
Tags

certificate vba macro digital signature

Topics

Microsoft ADP

,

Access Coding/Macros

,

Microsoft Access Database

,

Microsoft Word

,

Windows Network Security

Participating Experts
7
Points
500
Comments
33

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Certificates in MS Office
    I have a certificate from a CA that I would like to use in Excel to sign macros. How do I import the cert into office? Thanks, TNC
  2. The VBA project is currently signed as [No certificate]
    Hello, all. I created a Digital Signature (DS) for my VBA Excel applications about one year ago. I've been able to edit those apps and retain the DS. A few days ago I got a new laptop. When I try to edit the Excel apps I am always prompted to "Enable macros", as ...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: talonsbladePosted on 2009-10-16 at 06:04:52ID: 25589078

any ideas?

 

by: vadimrapp1Posted on 2009-10-16 at 09:41:08ID: 25591066

Install it into domain trust.

 

by: vadimrapp1Posted on 2009-10-16 at 09:41:40ID: 25591069

Here's how.

 

by: talonsbladePosted on 2009-10-16 at 10:23:15ID: 25591459

i work for a company that works for the mililtary.  we do not have control over our network or computers.  the reason we are using access for everything is because we are not allowed to do any programming with real languages that would produce .exe files.  we are stuck using vba and access.  we can install cirtificates on our own computers but we cannot do it across the domain because we cannot control the domain, it is controled by the gov

 

by: vadimrapp1Posted on 2009-10-16 at 10:34:29ID: 25591553

And everybody decides for themselves what publishers and certificates to trust? anybody can get hold of any certificate, but it's which one you trust is what's important.

I would take a look at http://msdn.microsoft.com/en-us/library/aa374863%28VS.85%29.aspx . If I understand your intention correctly, you want Word to install a certificate that would allow itself to run the code it otherwise is not allowed to run. Isn't this hacker's wet dream...

 

by: talonsbladePosted on 2009-10-16 at 10:48:58ID: 25591657

well sort of, but thats not why im doing it.  i have a government id card that expires every year, when it expires, instead of renewing the certificate when i get a new card they give me a whole new certficate which causes all my programs not to work untill i get all the programs back out there with my new cert and everyone trusts that cert.

right now i have a good cert out there with everyone so my code can run.  what i want it to do is install another cirtificate, a long term cirtificate, so that at a later date i can switch to that one and never have to worry about the cirtificate expiring again.

 

by: vadimrapp1Posted on 2009-10-16 at 10:56:48ID: 25591732

From what I know, program can't install certificate and tell the the system to trust it, just like I said above - it would be major security breach. Only administrator can do this, either local computer admin, or domain admin. But I'm not really an expert in security. Perhaps someone in security-related are will give you better idea, such as in http://www.experts-exchange.com/Security/Operating_Systems_Security/Windows/

 

by: vadimrapp1Posted on 2009-10-16 at 10:58:02ID: 25591747

I asked moderator to publish this question in that zone.

 

by: talonsbladePosted on 2009-10-16 at 11:30:02ID: 25592047

the program does not necessarily have to make the certificate trusted.  the problem is that if i just use the certificate to digitaly sign the vba, the cirtificate is not valid unless it is in the root store, so the user will not be able to choose trust from this publisher.  everything the application lanches they will have to tell it to run the vba.  

if i can just get the cert installed the the root store, the user can choose to trust all from this publisher and then they only have to do that once.

 

by: talonsbladePosted on 2009-10-19 at 05:45:15ID: 25604619

bump

 

by: JOrzechPosted on 2009-10-19 at 07:19:55ID: 25605346

 

by: talonsbladePosted on 2009-10-19 at 10:48:41ID: 25607339

we are not admins on our domain, net even our computers.  im beginning to think this is impossible without having our admins going to all the computers with a cd because thumb drives cannot be used on gov computers.

 

by: talonsbladePosted on 2009-10-23 at 11:15:59ID: 25646897

bump.  

the code that will be installing the certificate will already be signed by a trusted publisher since my current certificates are trusted by all users.

 

by: vadimrapp1Posted on 2009-11-27 at 17:21:00ID: 25923673

This is the case when the answer is "you can't do that". The code can't certify itself , neither directly, nor indirectly (such as by certifying its own publisher). Only administrator can.

 

by: vadimrapp1Posted on 2009-11-27 at 17:22:48ID: 25923680

,hence, accept http:#25592047

 

by: vadimrapp1Posted on 2009-11-27 at 17:23:41ID: 25923682

correction: accept http:#25591732

 

by: TolomirPosted on 2009-11-28 at 02:01:05ID: 25924652

The whole approach to possible solution is worng, the asker has to purchase a certificate from e.g. verisign to solve the problem:

http://www.verisign.com/code-signing/content-signing-certificates/microsoft-office-vba/index.html

           
For developers and software publishers of Microsoft Visual Basic® for Applications (VBA) macro projects, code signing reduces error messages and builds trust in your reputation. VeriSign® Code Signing Certificates for Microsoft® Office and VBA authenticate your identity and validate code integrity.            

  • Digitally sign Microsoft VBA Macros for Microsoft Office 
  • Protect your brand and your reputation with a trusted digital signature 
  • Reduce the cost of maintaining code with a free timestamp
     
          ---
He cannot use a self-signed certificate since the root CA is unknown (untrustworthy) with vba code signed from verisign the problem will not arise since the verisign root CA is known to windows.

---

Since such a solution was not properly worked out I suggested a delete w/ refund.

Tolomir EE Cleanup Volunteer  


 

by: TolomirPosted on 2009-11-28 at 02:07:53ID: 25924666

 

by: TolomirPosted on 2009-11-28 at 02:12:21ID: 25924673

Ok here's the no you cannot do that clause:

You will not get a 90 years certificate from any well known certification authority.

You can only add a root certificate to each computer that you use to sign your VBA code for e.g. 90 years.

Please see the difference between signed code and root certificate. This are in fact 2 certificates though on different trust levels.

http://en.wikipedia.org/wiki/Root_certificate

Tolomir


 

by: vadimrapp1Posted on 2009-11-28 at 05:45:04ID: 25925183

The question was very  specific:

Is there a way to install a certificate into the trusted root certificate store folder using vba

and the answer was in http:#25591732:

program can't install certificate and tell the the system to trust it, just like I said above - it would be major security breach. Only administrator can do this, either local computer admin, or domain admin.

This is certainly valuable addition to the KB, no reason to delete it at all.

There's nothing wrong with "the whole approach to the solution" - it's 100% valid question. Wrong would be exactly not to consider this approach and begin with purchasing public certificate for local network.

 

by: TolomirPosted on 2009-11-28 at 07:31:16ID: 25925475

Installing a root certificate on 150 - 200 computers is more expensive then a few hunders dollars...
Let's assume you need 5 minutes per computer it will take you more than 12 hours minimum.

Tolomir

 

by: aikimarkPosted on 2009-11-28 at 11:23:36ID: 25926324

Is this version Access2007 or Access2003?

 

by: tedbillyPosted on 2009-11-28 at 14:55:35ID: 25927076

The whole point of the Certificate is to confirm that a program can be trusted. So, your certificate used by your VBA is to let the user know that your VBA code is trustworthy. Therefore, your VBA code in can't say 'Trust Me' while I install a certificate that says you can 'Trust Me'. That's like a robber providing his own alibi.

"You can't do that" is the correct answer. Award the points to http:#25591732

 

by: younghvPosted on 2009-11-28 at 16:15:18ID: 25927264

All,
I don't have a dog in this fight, but (if this is a U.S. Military domain) the right answer is for the Asker to work with the appropriate IT Department (DOIM, J-6, etc.) in whatever Military unit he is in.

Running unauthorized macros - or installing certificates without proper Domain Administrator level accounts is an absolute violation of the User Agreement for anyone with a DoD user account.

IMO - "You can't do that" is the right answer in more ways than one.

 

by: aikimarkPosted on 2009-11-28 at 16:20:51ID: 25927282

In Access2007, there is a way to bypass this security feature on a directory-by-directory basis.

 

by: talonsbladePosted on 2009-11-30 at 04:58:12ID: 25933445

ok, here is the deal, the program that is installing the certificate is already trusted by all computers.  the problem is that my certificate is only good for like 6months to 1 year and then it changes and i have to go and get eveyone to trust it again.  what i want to do is have the already trusted certificate install another certificate.

 

by: TolomirPosted on 2009-11-30 at 05:05:32ID: 25933498

This does not work, only Administrators are allowed to install new certificates.

I suggested you buy a well known certificate with duration of 3 years, from e.g. verisign so you can roll out your application without having to install a new certificate to all client computers.

Tolomir

 

by: younghvPosted on 2009-11-30 at 05:15:55ID: 25933559

talonsblade,
As mentioned above, you need to work with the DOIM at your HQ to get this resolved.
Regardless of the fact that there may be 'work-arounds' to this situation, you are operating on a U.S. Government base and those responsible for the network need to be the ones making these modifications.

@All Experts -
"Experts-Exchange" is currently on the allowed sites list for all U.S. Department of Defense networks. It is not prudent to give advice to bypass security restrictions on these networks/computers.

The Asker does not have the necessary permissions for a reason - he is not a member of the appropriate department.

 

by: talonsbladePosted on 2009-11-30 at 05:16:20ID: 31641110

well, guess it cant be done.  thanks everyone for your input but a verisign for vba is 1200 dollars and i know my company wont pay for that

 

by: LSMConsultingPosted on 2009-12-11 at 03:40:26ID: 26027230

Is this a commercial certificate, like from Comodo? If so, then you can setup your machines such that they will check check the timestamp of that certificate. A code signing certificate will expire every year, however if the certificate was valid when the app was installed, then checking the timestamp (using a timestamp server run by one of the certification authorities) will allow your app to run after your code signing cert is expired. I have an app running on about 500 workstations at a very large aerospace firm, and it's been running for about 7 years with the same certificate. Basically, you must write a few registry keys, which allows VBA to verify whether the certificate was valid AT THE TIME THE CODE WAS SIGNED. Here's the keys:

"Software\Microsoft\VBA\Security\TimestampURL" - mine is set to "http://timestamp.comodoca.com/authenticode"
"Software\Microsoft\VBA\Security\TimestampRetryCount" - mine is set to 3
"Software\Microsoft\VBA\Security\TimestampRetryDelay" - mine is set to 3

VBA will then use those values to validate your certificate.

Note that this in no way bypasses security issues - it simply allows VBA to validate whether your certificate was valid or not at the time the app was signed. This it the procedure recommended by all the major code cert authorities. Here's an MS whitepaper that provides details of this. It's for Office XP but is relevant for any version that can use code signing:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7E3EAB1F-B313-44F4-8900-3399ABB2001D&displaylang=EN

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...