Posts tagged as:

code+signing

Code-Signing Microsoft Office Files with VBA Macros

by Sue on June 13, 2010

in digital products,security

I have one client, outside the technology industry, who thinks of me as their go-to person for all matters relating to technology.  Even for things that are not really up my alley.

This client wanted to sell over the Internet an Excel spreadsheet that contained several VBA macros.  No problem, I thought. I help people set up shopping carts, payment processing, and so on, all the time.  Well, not so fast.

Unexpectedly, the tricky part turned out to be preparing the Excel workbook for download as a product by “code-signing” it with a digital certificate from an official certification authority (such as Verisign, Thawte, Comodo, etc.)

Code-signing is necessary because, without it, Windows security settings would automatically disable the VBA macros when customers open the Excel workbook.  It is a measure to prevent users from unknowingly infecting their machines with viruses, but places a burden on the software publisher.  On later versions of Excel and Windows, it is not even possible for the user to then choose to enable the macros; s/he has to completely change his/her system’s security settings to do so.

The process of code-signing the Excel workbook sounded simple enough:

  1. Purchase a Code-Signing certificate for Microsoft Authenticode (multi-purpose). The client bought the certificate from Thawte, but you could also go with Verisign, Comodo, or others. I don’t think there is really much difference.  We needed an Authenticode certificate to sign Visual Basic macros, and we got a “multi-purpose” certificate that we could use to both sign Visual Basic macros and EXE files, important because most users install new software by downloading and running a “setup.exe” program.
  2. Install the code-signing certificate on my laptop.
  3. Using my laptop (with the certificate installed on it), modify the Excel workbook’s VBA properties to use the just-installed certificate.
  4. Ta-da!  Users should now be able to use the Excel workbook without incident!

Well, that is the high-level process, but the devil is in the details.  And the whole thing is poorly documented.  What I thought would take an hour tops ended up taking a lot more.

DETAILED PROCESS:  INSTALLING A CODE-SIGNING CERTIFICATE

Here’s  the process you’ll likely follow, in a lot more detail.

  1. Use Thawte’s website to purchase a Code-signing certificate for Microsoft Authenticode (multi-purpose).  When you are making this first request a Private Key, a .pvk file, is downloaded to your machine (possibly without your realizing it is happening).  On Windows XP, you’ll be able to find the .pvk file.  But if you are on Windows Vista or Windows 7, the system will hide the file from you and you won’t be able to access it.  So, work on Windows XP if you can.
  2. Thawte will then take some time to verify that your business is who it claims to be.  This will take anywhere from a few hours to a few days, will involve your talking on the phone, etc.
  3. After Thawte is satisfied, they will issue the digital certificate and will email you instructions on how to download it from their website.  The email we received ALSO contained an attachment with Thawte’s Intermediate Code Signing Certificate.  This attachment is NOT your company’s code-signing certificate.  To get yours, you need to log into Thawte’s site using the email’s instructions.  You must do the login from a machine that has  the .pvk file from step 1.  The certificate that you then download is a file with an .spc extension.
  4. Now, go back to that email attachment with the Thawte Intermediate Code Signing Certificate.  Cut/paste the lines between (and including) “Begin Certificate” and “Save Certificate” and save as a file with a .cer extension.
  5. So, to recap, now you have THREE files.  Your private key (.pvk file), your code signing certificate (.spc file), and Thawte’s certificate (.cer file).
  6. In Windows, right click on the .cer file and select “Install Certificate” from the menu.  The Import Certificate wizard pops up: step through it and accept all defaults. This will import the Thawte Intermediate certificate into the machine’s Windows Registry and the machine’s digital certificate store.
  7. Verify that the Thawte Intermediate Certificate is in the Certificate store:
    • i. Internet Explorer –> Tools–> Internet Options –>Content–> Certificates
    • ii. Check the “Intermediate Certification Authorities” tab to find a “Thawte Code Signing CA” certificate that expires in a year or two.
  8. To import your certificate using the .pvk file and the .spc file, you need to use a tool called. pvkimprt.  This utility will place both the private key and certificate into your machine’s “My” certificates store, making them visible and usable by Office XP to sign macros. It does this by importing info into the Microsoft Windows registry.
    • i.    Download the pvkimprt tool from the Microsoft site to the desktop.
    • ii.    Rename the downloaded file (pvkimprt.exe) to pvkimprt-zip.exe
    • iii.    Run pvkimprt-zip.exe.  It extracts to files: a readme.txt and another pvkimprt.exe.  This pvkimprt is ALSO an installer, not the actual utility. (Why an installer within an installer!?!? Argh!)
    • iv.    Rename this pvkimprt.exe to pvkimprt-selfextract.exe
    • v.    Run pvkimprt-selfextract.exe, and specify c:\MyCerts as the destination directory.  It will produce a file PVKIMPRT.EXE that is at last the actual utility.
    • vi.    At DOS prompt: cd \MyCerts
    • vii.    At DOS prompt:
      pvkimprt -IMPORT <Certificate-FILE>.spc <PrivateKey-File>.pvk
      (note, replace <Certificate-FILE> with the name of YOUR file.  Same with <PrivateKey-File>.  There should not be any “>” or “<” in the real file names.)
    • viii.    The Certificate Import Wizard pops up.
    • ix.    Go through the wizard, selecting “automatically select the certificate store based on the type of certificate
  9. View the imported cert and key in Internet Explorer > Tools > Internet Options > Content > Certificates
    • i.    Under the Personal tab, there should now be a certificate “Issued To” your company.
    • ii.    Click on the certificate and then the “View button”
    • iii.    A dialog appears.  Toward the bottom it should say “You have a private key that corresponds to this certificate”

At this point, the certificates and keys are all imported into the machine’s Windows Registry.

Now, we need to do some extra configuration to allow us to Time-Stamp as part of the code-signing process.  Whenever you do code-signing you should use time-stamping (why it is not the default process, I do not know).   Without time stamps, your users will receive errors and warnings when using your Excel workbooks with VBA and installing your programs once your code-signing certificate expires.  Time stamping basically verifies that the code-signing certificate was indeed valid when this Excel file (or EXE, etc) was signed.  And thus, Windows will allow it to continue to run, even after the certificate expired.

OFFICE FILES: ENABLE TIME-STAMPING

To enable time-stamping of Office files with VBA macro, you have to manually edit the Windows registry.  Ugh, what a nightmare, and one that is not documented well anywhere on the Internet (but is why I opted to write this post):

  1. First, make sure that your computer is connected to the Internet.  You cannot timestamp a digital signature unless you are connected to the Internet.
  2. Second, start up” regedit” to edit the Windows registry manually.  Windows –> Run –> regedit.
  3. Navigate the Registry to HKEY_CURRENT_USER\Software\Microsoft\VBA\
  4. Create a “Security” key.  (New Key).  It should now be at HKEY_CURRENT_USER\Software\Microsoft\VBA\Security
  5. Select the “Security” key in the left Window pane.
  6. To the Security key, add a String value Item to the ‘Security’ key named ‘TimeStampURL’  with the value set to: http://timestamp.verisign.com/scripts/timstamp.dll
  7. To the Security key, add a DWORD value item to the ‘Security’ key named ‘TimeStampRetryCount’ with the value data set to ’3′ (In my case I used 3 but you can pick a different number).  This is the number of times the code signing process should attempt to contact the time stamp server.
  8. To the Security key, add a DWORD value item to the ‘Security’ key named ‘TimeStampRetryDelay’ with the value data set to ’3′ (In my case I used 3 but you can pick a different number).  This is the number of seconds the Visual Basic Editor will delay before retrying a connection to the timestamping server.
  9. Here’s a screenshot of regedit after all these changes:

Now, at long last, we are ready to finally code-sign the Excel file.

  1. Open Excel workbook
  2. Open Visual Basic Editor (Excel menu –> Developer toolbar –> Visual Basic)
  3. In the Visual Basic Editor: Tools –> Digital Signature.
  4. Click “Choose”. This displays the Select Certificate dialog box.
  5. Choose  your company’s certificate (probably under the “Personal” tab), making sure text appears that says “you have a private key corresponding to this certificate”.
  6. Click OK twice.
  7. Save the workbook and quit Excel.

Now, test it.  Open the Excel workbook and see if there are warnings about the Macros.  If there are, make sure that the warnings display your company’s name as the publisher, and you can also click on your company’s name to view the actual Digital Certificate, see its expiration date, etc.

Now, test the timestamping by setting the computer’s system clock to a date after the code-signing certificate’s expiration.  Then, open the Excel workbook again, see if there are any errors, etc.  Hopefully all is well.

DON’T FORGET TO SET YOUR SYSTEM CLOCK BACK!

SIGNING EXE FILES

Above, I described how to code-sign Office files with VBA macros.  You might also want to use the same Code-Signing digital certificate to sign EXE files, CAB files, etc. that you might distribute to customers.

  1. Download the signtools from Thate: http://www.thawte.com/dynamic/en/images/support/inetSDk5.zip
  2. Extract the inetSDk5.zip to c:\MyCerts\inetSDk5
  3. Your private key (.pvk file), your Certificate (.spc file) should already be in the c:\MyCerts folder.  If not, copy them there.
  4. Copy the EXE file you want to sign into the c:\MyCerts folder
  5. Using signcode, please sign the .exe file using the following command at the DOS prompt.
  6. The .exe file is now signed and timestamped. Please run Checktrust.exe to ensure that the .exe file has been signed correctly:
    • chktrust  c:\MyCerts\MyExecutable..exe

Now, test this by uploading this EXE file to a website.  Use your web browser to navigate to it and download it.  Your EXE is correctly signed if:

  • The browser’s “open file” security warning shows your company’s name as the publisher, and if you click on the name you see the Digital Signature Details – including the time the EXE was code-signed.
  • If you then click on “View Certificate”, a dialog pops up displaying more details including the code-signing certificate’s expiration date.

That’s it!  Hopefully, this bit of documentation helps somewhat out there who might be struggling with the same issue and Thawte’s poor documentation.

{ 2 comments }

wordpress hit counter