Sign an Excel macro with a certificate issued by your enterprise PKI
This article describes how to digitally sign an Excel VBA project with a certificate issued by your ADCS PKI. You can use the same method to sign any Office VBA project but in this post we will focus on Excel. For an introduction on how to sign Office macros you can read these KB and MSDN articles.
You can deduce from both articles that you need to use the “code signing” certificate template to perform the digital signature. So we will create a new certificate template named “Excel” which will be a duplicate from the existing “code signing” template. It will have the following characteristics:
- Autoenrollment is set up for the ldap389-excel-dev group which contains the users authorized to sign Excel macros, the user ldap389-dev is a member of this group.
- Private key: length 2048 bits, not exportable.
- Certificate validity period: 1 year.
Once the Excel certificate template is configured, the certificate will be automatically installed via autoenrollment in the ldap389-dev user’s personal store:
You may want to lock your project before signing it, under the visual basic editor right click on your project, select “Properties”, on the “Protection” tab select the “Lock project for viewing” check box and enter a password. To sign your project navigate to “Tools\Digital Signatures”, click “choose”, the “code signing” certificate installed in your ldap389-dev user’s personal store should appear:
You just signed your VBA project, the certificate validity period is one year. You will need to resign and redeploy the macro before the expiration date
(the 16th of August 2012). After that date the signature will be considered expired and invalid, unless you use a TimeStamp server (RFC 3161 compliant) when signing your VBA project. To achieve that, you should edit the ldap389-dev user’s registry: Edit the key HKEY_CURRENT_USER\Software\Microsoft\VBA\Security as described in the “Office files: enable time-stamping” chapter of this post. This registry setting can be deployed via GPO for the ldap389-excel-dev group. In our case we will use the following registry values:
- TimeStampURL (REG_SZ): http://timestamp.verisign.com/scripts/timstamp.dll.
- TimeStampRetryCount (REG_DWORD): 2.
- TimeStampRetryDelay (REG_DWORD): 2.
Once all your Excel macros are signed by your developers you can enforce the Excel security level with a GPO for all Office versions deployed in your domain, for that you will need to download the “administrative templates” of each Office version:
- Office 2003 SP3 Administrative Template Files (ADM).
- Office 2007 Administrative Template Files (ADM,ADMX/ADML).
- Office 2010 Administrative Template Files (ADM,ADMX/ADML).
You can now setup a GPO that disables all except digitally signed macros for each Excel version:
The above setting is the default one, but it is better to enforce it with a GPO in case it is modified…
This post is also available in: French
5 Comments
Other Links to this Post
RSS feed for comments on this post. TrackBack URI
By RichardJC, February 10, 2012 @ 4:40 pm
Hi,
Thanks for this post. We are currently working through the process of signing macros in our environment using our enterprise PKI. What isn’t clear to me is ‘how’ you get a ‘timestamp’ for your certificate. If you have any more detailed steps you could add that would really helpful.
Thanks,
RichardJC.
By ldap389, February 10, 2012 @ 6:55 pm
Hello,
You do not get a timestamp for the certificate, there is no attribute on the certificate with that information. The certificate is used by the developer for signing one or more Excel Macros. The timestamp is set for the macro/document you sign. When you assign the certificate to your macro/document, Excel contacts the timestamp server and checks that the certificate you sign the document with has not expired and add a timestamp to the document. Btw things changed with Office 2010, you might check this post for more information.
Hope this helps
By RichardJC, March 5, 2012 @ 8:08 pm
Hi ldap389,
Thanks for the reply it has helped me understand my issue better and I appreciate it!
RichardJC…
By John, September 21, 2012 @ 6:50 pm
MS is so full of it. After all these Excel version they finally have a way to digitally sign, but no WAY to add a timestamp URL.
I checked that post mentioned, what an amount of work including a root cert of the timestamp URL provider, and what have you. They’re crazy!
Signcode for EXE f.e. just wants a URL and does the job.
Cheers,
John
By Matt, August 22, 2016 @ 2:37 am
Note that this may not work under Windows 10 as is. I found that the above worked when Excel 2010 was run as a local admin but failed otherwise (trust centre logs will say “Certificate: None” despite the certificate being there and valid).
Found the answer at:
http://stackoverflow.com/questions/31746786/digital-signing-of-vba-project-under-windows-10
(see comment from AZMoosie). That works on one PC – to get it working across domain edit the certificate template and add ‘Client Authentication’ as an extension – as soon as this was done it all worked. Took me ages to find and as this page was high on the search results thought the info may be useful to someone else.