Code Signing

[NeoPa]

Introduction:

Macro Security Levels in MS Office applications are recommended to be set to High. This stops any VBA code associated with a project from running, unless it is signed (with a certificate). A trusted signature will allow the code to run normally, whereas an un-trusted one will prompt the user either to trust the issuing CA (Certificate Authority) and enable the code, or simply to disable the code.

This is all very well, but supposing you develop Excel, Word, Access etc projects to be used at your place of work, and you don't want to spend lots of money paying for an expensive certificate from one of the main issuing CAs? You also want your user-base to be protected from potentially malicious code from elsewhere, but to run your official smoothly without continuous prompting.

It's possible to self certify, using selfcert.exe, but when a certificate is created that way, it's private key cannot be exported. The export wizard of the Windows certificate console says "the associated private key is marked as not exportable". This effectively means that it will only work on the PC where the certificate is used. This seems woefully inadequate.

This article explains how that can be achieved without too much hassle. Most of the details from which this was built came from http://www.source-code.biz, so my gratitude to them for that.

Creating the Certificate Files:

To create a certificate file (.PFX) that can be used to sign MS-Office VBA projects (Excel/Word macros) on multiple computers, there are three executable files that are required :
MakeCert.Exe
Cert2Spc.Exe
PVKImprt.Exe

NB. PVKImprt.Exe is the name of the download, AS WELL AS the name of the file INSIDE the download. The one inside is the important one. It's easy to get this wrong, as it is doubly compressed for some reason.

I have also included copies of these executables as an attachment (CodeSigning.Zi p) in case the links die. PVKImprt.Exe in this file is the actual one required and needn't be re-extracted.

Solution:

Parameters:
The following commands can be used to create a PFX file (PKCS #12) that contains the self-signed certificate together with the associated private key, but before we start we need to explain / define some parameters :
%Name% = The name that you want the certificate to show as.
%File% = The filename (without extension) to be used.
%PW% = Determine a password to be used for your certificate.

Certificate Creation:

MakeCert -r -n "CN=%Name%" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -sv %File%.pvk %File%.cer
******** You will be asked for a password (%PW%) 3 times.

Cert2Spc %File%.cer %File%.spc

PVKImprt -pfx %File%.spc %File%.pvk
******** Enter password (%PW%) to start the Wizard.
******** Select to export the private key.
******** Select to "include all certificates in the certification path" & "to enable strong protection".
******** Enter password (%PW%) again, twice.
******** Enter, or browse to, the name of the export file required (path\%File%.pfx).
******** Finish if / when you have checked the details on the final screen.

The last command (pvkimprt -pfx ...) creates the file %File%.pfx. This PFX file can then be imported into the Windows certificate store and used for code signing.
(MakeCert.Exe and Cert2Spc.Exe are part of several Microsoft SDKs, e.g. the Platform SDK or the DotNet SDKs, which can be downloaded from microsoft.com).

Certificate Installation:
With the .pfx file available, take the following steps to install the ability to sign a project on to a PC :

1. Open Control Panel.
2. Select Internet Options.
3. Select the Content tab.
4. Click on Certificates.
5. Click on Import...
6. Click on Next.
7. Click on Browse.
8. Select Files of Type=Personal Information Exchange (.pfx).
9. Select %File%.pfx.
10. Click on Next.
11. Enter %PW% again and select Enable strong private key protection if required.
12. Select Mark this key as exportable.
13. Click on Next.
14. Select Automatically select the certificate store.
15. Click on Next then Finish.

Sign a Project:
With the certificate now installed you need to sign a project with it.
If you have none available :

1. Open Excel.
2. Type something into cell A1 (anything).
3. Use Alt-F11 to switch to the VBA editor.
4. From the Project Explorer pane (Ctrl-R) double-click on ThisWorkbook.
5. In the Code pane paste in the following short piece of code :
   

   Option Explicit
    
   Private Sub Workbook_Open()
       Call MsgBox("Hello World")
   End Sub

6. Select Tools / Digital Signature / Choose.
7. Select the certificate.
8. Click on OK.
9. Use Alt-F11 to switch back to Excel and save the file (EG. as Test.Xls).

Trusting a Signature:
Anyone wishing to trust this signature (using a version of Access prior to 2007) should :

1. Open Excel and ensure that the security level is set to High (Tools / Macro / Security / High).
2. Open a file containing a signed project (EG. Test.Xls).
3. When the Security Warning window pops up, select Always trust macros from this publisher if it is not greyed out
4. Click on Enable macros.
5. If it IS greyed out :
   1. Click on Details... / View Certificate / Install certificate...
   2. Go through and "Finish" the wizard as before.
   3. Close the Security Warning window (X at top ensures file doesn't open).
   4. Re-open the file. Select Always trust macros from this publisher (no longer greyed out)
   5. Click on Enable macros.

Anyone wishing to trust this signature (using Access 2007) should :

1. Open a database which you know to have been signed by the certificate whose publisher you wish to trust.
2. A Security Warning message appears near the top of the window with an Options button. Click this.
3. Select Show Signature Details.
4. Select View Certificate.
5. Click on Install Certificate...
6. Click Next when the wizard shows.
7. Select the Automatically select the certificate store based on the type of certificate radio button.
8. Click Next.
9. Click Finish.
10. To the question Do you want to install this certificate, respond Yes.
11. Click on OK.
12. Click on OK.
13. Click on OK.
14. Select "Trust all documents from this publisher"
15. Click on OK.

[JustJim]

Thank you NeoPa for this concise run-through. As a freelance I've been looking for a solution like this for a while. It will make my and my clients' lives simpler and that is always a good thing.

Jim

[NeoPa]

I'm glad to help Jim (and you no longer need worry about being son-of satan as your post count has moved on now).

[NeoPa]

I've recently had an issue where the certificate I was signing with failed to work. As I've now fixed the issue I don't still have access to the exact error message, but it was on the lines of "Unable to sign project. Certificate dropped.".

The solution I found for this was to remove the certificate, which effectively leaves you with a clean slate, then re-add it in exactly the same way as originally. This clearly requires the same password be entered, so it's important to keep this available.

To remove an existing signing certificate simply :

1. Open Control Panel.
2. Select Internet Options.
3. Select the Content tab.
4. Click on Certificates.
5. Select the certificate to be removed.
6. Click on Remove.
7. Click on Yes to confirm.

[NeoPa]

As the procedure for ensuring a client trusts the provider of the signature (and thus the code signed by the signature itself) is fundamentally different for Access 2007, I've updated the original article to give specific instructions for use with Access 2007.