Signing Access 2003 Projects
Signing Access 2003 Projects
If you've used Access 2003, you've probably seen several security warning messagesAccess 2003 cares about your security. An important part of Access 2003 security is digitally signing your code. As Rick Dobson shows, you can do it, but preparing for digital signing is critical.
A digital signature acts like shrink-wrap on your project: Clients know that they're getting a copy directly from you that no one else modified. Clients will also know that they're working with "your" code and not any version of it modified by a third party. As computing moves forward into a "security conscious" era, learning how to acquire and use a digital certificate is also important for interfacing with organizations that adopt policies of only running digitally signed Access 2003 projects: Your users may refuse to accept software from you that isn't shrink-wrapped.
This article focuses on Access 2003 security for VBA macros. You'll learn how to obtain and use certificates that let you digitally sign Access projects. By using these tools, you tell your Access 2003 clients that you care enough to declare unambiguously that you wrote the code that your client is running. This kind of declaration is particularly important for VBA solutions distributed via the Internet, where your client may not know you personally.
While Access 2003 retains all of the features that make Access so special to us, it adds some new benefits too. For example, a couple of security prompts may pop up when you try to open new Access projects (either .mdb or .adp files). Some users and organizations may consider these prompts a blessing, while others may seek ways to make them go away without addressing the underlying security threats that the notices are flagging. Microsoft added these warning as part of making Access 2003 more secure than earlier versions of Access when running Access applications automated by VBA macros (and COM add-ins). Some organizations may find the appeal of these new security features a good reason to move to Access 2003 from earlier versions of Access.
One of the two new security features addresses who a computer user trusts to write the VBA macros and COM add-ins running on his or her computer. For example, Access 2003 lets the user completely block opening Access projects with VBA macros that aren't digitally signed. By opening only digitally signed projects, individuals and organizations can avoid projects that include malicious code (for example, viruses) from rogue publishers. A second security measure blocks selected commands in queries run by Jet.
Access 2003 macro security levels
Access 2003 makes available three Security settings that control the ability to open and use Access projects with VBA macros. These levels are High, Medium, and Low. By default, Access 2003 installs with a High security level. However, Access 2003 users can select the Tools | Macro | Security command to change the setting from High to either Medium or Low. Security level settings persist until a computer user modifies them (users may need to reopen Access 2003 for a changed security level to take effect). Organizations can enforce policies that restrict the permissible security level settings (for example, to use High only).
Here's a brief summary of each security level:
- High permits a computer user to open without a prompt a digitally signed Access project from a trusted publisher and blocks opening signed Access projects from untrusted publishers as well as unsigned Access projects. A dialog box opens for a project signed by an untrusted publisher that allows computer users to view the certificate for a publisher and to trust the publisher so that the project can be opened. Unsigned projects cannot be opened.
- Medium permits a computer user to open without a prompt a digitally signed project from a trusted publisher. In addition, you can also designate the publisher of a signed project as trusted so their projects will open without a prompt in the future. Unsigned Access projects are always prompted with a reminder that the file may contain potentially harmful code, but users can elect to open them anyway.
- Low permits a computer user to open an unsigned Access project without a prompt. When users make a Low security setting, they're reminded that they aren't protected from potentially unsafe macros.
A digital certificate for code signing has two parts: a public key and a private key. Publishers use the private key to sign a project, which encrypts the VBA macro code in a project. The private key must be installed on any computer that will edit the code, for reasons I'll discuss shortly. The act of signing a project attaches the public key to the project. A computer user can open the project and run the code without the public key. However, a computer user cannot view the code without the public key because the code listing is encrypted.
This distinction between running code and viewing code is important to understanding how signed Access 2003 projects work in Access 2002 and Access 2000. As you'll recall, all three versions of Access share a common file formatnamely, the one for Access 2000. However, signed Access projects aren't available in versions before Access 2003. Therefore, signed Access 2003 projects can be opened and run in either Access 2002 or Access 2000, but the code in signed Access 2003 projects cannot be viewed by users of earlier versions.
A signed project can lose its digital signature if the code is edited on a computer running Access 2003 without the private certificate installed. When this happens, the project becomes unsignedthis is what protects users against running modified versions of your code. The original publisher can sign the project again from a computer with the private key to restore the signature for a project. For instance, when the original publisher edits the code for a signed project, Access 2003 automatically signs the project again if the edit takes place on a computer with the certificate's private and public keys.
There are three sources for digital certificates:
- You can use the self-signed method for generating a digital certificate. Microsoft Office 2003 installs a menu item for invoking this method that runs the selfcert.exe tool. This approach allows you to test the operation of digital certificates, but it has limited practical use because no independent party verifies the authenticity of the certificate. Therefore, Access 2003 has no way of knowing from a trusted third party who a publisher really is.
- You can use Windows Certificate Services within an organization to create certificates recognized by those within the organization and other groups, such as suppliers or consultants that work closely with an enterprise-based issuer of certificates. These certificates are trusted by those in the organization as well as close associates.
- You can use certificates issued by a widely recognized trusted certifying authority. The two certifying authorities that I'm most familiar with are VeriSign and Thawte. This article demonstrates the steps for acquiring, installing, and using a Thawte certificate to sign Access 2003 projects.
Getting and registering a Thawte certificate
Applying for a digital certificate requires you to ready yourself in multiple areas. In the process of obtaining and installing my certificate, I experienced several problems because I didn't adequately prepare for obtaining and installing the certificate. The preparation, request, and installation steps that I'm going to walk you through will allow you to avoid the difficulties that I encountered (as well as other bullets that I managed to dodge). These preparation steps are critical to success!
You need to prepare your computer environment, gather appropriate documentation for the identity that you use to request a certificate, and prepare your finances to pay for the certificate. The identity for the certificate will typically be the name of your consulting practice or business. A certifying authority needs to be able to validate your identity through a mechanism I'll describe shortly. In addition, the certificate application process includes multiple steps to get and install a certificate after you become ready to apply.
Getting your computer ready is relatively straightforward. You need a computer that connects to the Internet and has Access 2003 installed. I also found it useful to have all of the recent Windows updates installed on the computer that I used for getting and installing my Thawte certificate. You must also create a folder to store the special files for your certificate as well as the registration on your computer. Because Thawte will send feedback on the status of your application for a certificate via e-mail, you need an e-mail account. One last computer-related item that you'll want is a Web site associated with the identity for which you request a digital certificate. For example, my practice, CAB, Inc., sponsors the Web site at www.programmingmsaccess.com.
Besides these computer details, you'll also find it valuable to gather some information to verify the identity for which you request a certificate. I do business as CAB, Inc. My practice is literally a mom and pop operation, but I did obtain a DUNS number from Dun and Bradstreet for my businesswhich was fortunate because the Thawte application process requests a DUNS number. See the sidebar "Getting Your Own DUNS Number" for more information on that topic. You also need to provide other information, such as corporate, technical, and billing contact persons (these can be the same).
In addition to preparing your computer and gathering information that confirms the identity for which you request a certificate, you'll need a method of payment. You can pay by credit card, by International InterBank Transfer (SWIFT), or by check through the mail, which slows order processing to allow for the check to arrive and clear. The initial fee for one year is $199 and for two years is $399. You can renew a certificate at the end of a year for $159.
Once you've completed all of your preparations, you can start the process of applying for a certificate at the Thawte site (https://www.thawte.com/buy.html). Choose either the one-year or two-year code signing certificate. Before proceeding, you can download a step-by-step guide for enrolling for a certificate. Next, specify that you want a VBA Developer Certificate. At the time that I applied, Thawte hadn't updated the wording for its site to reflect that this option was appropriate for Office 2003. Therefore, you must recognize that the reference to Office 2000 also applies to Office 2003.
After making these navigational selections, you commence a three-step application process:
- First, you submit the name of the identity for which you seek a certificate, any related information, a method of payment, and a location on your computer and name for the private key (for example, c:\mykey.pvk). You're prompted for an installation password with a popup dialog. Save this password because you'll need it for registering your certificate on a computer.
- Next, you confirm the identity information provided in Step 1 and you submit your DUNS number. Make sure that the spelling for your organization's name, which you submitted during Step 1, exactly matches the name that Dun and Bradstreet has on record for your DUNS number. Submit corporate, technical, and billing contact information during Step 2.
- Finally, designate a privacy protection password for administrative tracking of your application for a certificate. This password is different from the one that you gave for installing your certificate, which you designated earlier in the application process. The certificate request application process concludes with the issuance of a request code that your can use along with your privacy protection password to track the approval process for a certificate from the Thawte site.
During this process, the private key will be installed on your computer.
Thawte will send you a couple of e-mail messages confirming the receipt of your application. Next, you wait while Thawte proceeds to verify the identity for which you request a certificate. This can take up to several days depending on when you make a request. Thawte runs a 24x5 operation, and the company is located in South Africa, which is seven hours ahead of Eastern Time. If your request is received during their weekend, it will take longer to process than if it's received during the work week.
After Thawte confirms an identity, you receive another e-mail message that directs you to download the public key for the private key deposited on your computer during the certificate application request. You need your privacy protection password to initiate this process. I recommend you download your public key to the same folder as your private key. The default file name for the public key is mycert.spc.
The final step in preparing your certificate for use is to register your private and public keys. Without this final step, Access 2003 won't be able to recognize your certificate even though its private and public keys reside on your computer. Use pvkimprt.exe, a Microsoft command line tool, for registering your certificate. Up until this point, you've been interacting with Thawte (as well as possibly Dun and Bradstreet).
Now you switch over to working with Microsoft products. You can download pvkimprt.exe from http://office.microsoft.com/downloads/2000/pvkimprt.aspx. You need to install this tool on your computer in a folder. I recommend the same folder that holds your private and public keys. The format for the command that registers your certificate keys is:
pvkimprt import mycert.spc mykey.pvk
Generating a selfcert certificate
While a selfcert certificate isn't authorized by a certifying authority, it gives you some feel for what it's like to sign Access projects. In addition, a selfcert certificate is free because the selfcert.exe program ships with Microsoft Office 2003. You can invoke selfcert.exe from the Windows Start menu (Microsoft Office | Microsoft Office Tools | Digital Certificate for VBA Projects) or by invoking the program from its default location in the OFFICE11 folder of the \Program Files\Microsoft Office\ path. If the selfcert.exe file isn't on your computer, re-run the Office 2003 setup program, select Digital Signature for VBA projects, and click Run from My Computer.
No matter which way you start selfcert.exe, the Create Digital Certificate dialog box opens. Type a name for your certificate in the text box on the dialog box. Click OK to complete the creation of your certificate. That's all there is to it. I created a selfcert certificate named RickDobson for evaluation purposes (see Figure 1).
There's at least one important difference between a selfcert certificate and a certificate from a trusted certifying authority. The Access 2003 interface permits you to trust certificates from trusted certifying authorities and selfcert certificates on the computer with the private key. However, the Access 2003 interface only permits you to trust certificates from trusted certifying authorities on a computer without the private key. This means that computer users can't easily elect to trust a selfcert certificate on any computer without the matching private selfcert key.
Using a Thawte or a selfcert certificate
You sign an Access project with a digital certificate from the VBA Editor. You should only sign a project after completing all code development for it. Code signing can expand the size of an Access project. If you regularly re-sign an Access project because of updates to the VBA code in a project, you should compact the project periodically to restore the project to its smallest possible size.
To start the process of signing your code, select Tools | Digital Signature from the VBA Editor menu. Click the Choose button on the Digital Signature dialog box to display a list of the signatures on your computer. Select a certificate with which to sign the project. On my test computer for signing projects, I have two certificates: One of these is a Thawte code signing certificate, and the other is a selfcert certificate. I also had one computer available without the private keys for either certificate, which I could use to test opening signed projects.
Figure 2 shows the Digital Signature dialog box before and after the selection of the Thawte certificate named CAB, Inc. While in the Select Certificate dialog box, I highlighted the Thawte certificate and clicked OK. This closes the Select Certificate dialog box and populates the Digital Signature dialog box with the choice made in the Select Certificate dialog box. The signature isn't assigned to the project until the developer clicks OK in the Digital Signature dialog box with a designated signature showing (see the bottom pane in Figure 2).
To illustrate the operation of digital certificates, I created three Access 2003 projects that are identical, except for their signature. The db1_Unsigned project has no digital signature. The db1_SignedByRickDobson project has a signature based on a selfcert certificate. The db1_SignedByCABInc project has a signature based on the CAB, Inc. certificate selected in Figure 2. All three projects included a form named Form1 that contains a Command0 CommandButton with a caption of Say Hello. The Click event procedure for the button opens a message box with a greeting followed by the name of the application, such as "Hello from db1_SignedByCABInc.mdb".
Opening signed and unsigned Access 2003 projects
Your experience in opening each of the three Access 2003 projects depends on the security level on your computer and the type of code signing for a project. The most basic case is Low security. With this security setting, Access 2003 opens files in the same way as earlier versions of Access. There's no sensitivity to whether an Access project is signed, and there's no Sandbox prompt for blocking potentially unsafe expressions in queries. All three projects open without any security prompts. The downside of using a Low security setting is that computer users expose their computers to harmful code from rogue developers.
Microsoft introduced the Medium and High security level settings to guard against this kind of threat. Whether your organization or your client's organization cares to take the risk associated with Low security is up to you and your clients. Clearly, Microsoft recommends against taking the risk.
With a Medium security setting, you'll have different results for each of the three Access 2003 projects. For db1_Unsigned, you'll be greeted with a Security Warning dialog box. The dialog box, which appears in Figure 3, asserts that the file contains potentially harmful code. The user sees three buttons in the prompt. One of these lets the user abandon the attempt to open the file, but another lets him or her open the file in spite of the security warning. The third button offers more information about the warning.
By the way, you may see a security warning about blocking unsafe expressions before viewing the VBA macro security prompt in Figure 3. This is the other new security feature that I mentioned in my opening. It pertains to potentially unsafe expressions in queries. Access 2003 requires you to block these potentially unsafe expressions when the security setting is either Medium or High. Therefore, reply Yes to the prompt if it appears. You can configure your computer to not present the prompt by requesting the automatic blocking of potentially unsafe expressions.
Attempting to open either db1_SignedByRickDobson or db1_SignedByCABInc presents two variations of the Security Warning dialog box you saw before. Figure 4 shows the variation for opening db1_SignedByCABInc, which, you'll recall, is signed with a Thawte certificate instead of the selfcert certificate used to sign db1_SignedByRickDobson. The dialog box shows the name of the file it's trying to open. Depending on the screen resolution, there may be some truncation of the file name (as in Figure 4). You can also see the name of the digital certificate used to sign the project. By clicking the Details button followed by the View Certificate button on the Digital Signature Details dialog box, you can view the origin of a certificate, including who issued the security.
With a project signed by a certificate from a trusted certifying authority, you receive the same warning about potentially unsafe code as for an unsigned project, but the dialog box shows a check box that lets you always trust the publisher of the file. The publisher is the identity associated with the digital certificate used to sign the project. By selecting this check box, all future attempts to open files from this publisher will open automatically without a prompt in the form of a dialog box. Access 2003 doesn't enable this check box for the project signed with the selfcert certificate, db1_SignedByRickDobson (this rule applies to computers without the private selfcert key). Only projects signed by publishers with a certificate from a trusted certifying authority, such as Thawte or VeriSign, can be opened without a Security Warning dialog box under Medium security.
If a user chooses to trust a publisher and later changes his mind, he needs to remove the certificate from the list of trusted publishers. This is done from the Security dialog box from the Tools | Macro | Security command. The user must select the Trusted Publishers tab and then highlight the certificate and click Remove.
With a High security level, a computer user is unable to open an unsigned Access project. If a user does attempt to open the file for an unsigned project, a Microsoft Office Access dialog box reminds the user that the file cannot be opened because it's not digitally signed. Even if you attempt to open an Access project signed with a certificate from a trusted certifying authority, such as db1_SignedByCABInc, Access won't open the file unless you indicate that the publisher is trusted. If you indicated this previously, the file opens automatically. Otherwise, a Security Warning dialog box appears with the check box for trusting a publisher enabled but the Open button disabled. Selecting the check box enables the Open button. Click this button to open the project. Users can't open a project signed with a selfcert certificate when the security level setting is High. However, users do view a Security Warning dialog box that explains why the file can't be opened.
Security in your future
Access 2003 presents two new security-related prompts to help protect Access application users from potentially harmful code. This article briefly discussed both of these, but my focus was on digitally signing Access 2003 projects with VBA macros. In particular, you saw how to obtain your own digital certificate from Thawte for signing Access 2003 projects. For you, right now, this may be an optional step. In an increasingly security-conscious world, the material in this article may become an essential part of your development process.
Sidebar: Getting Your Own DUNS Number
A DUNS number is a nine-digit sequence that maps to a single business entity (for example, your consulting practice). DUNS numbers are widely used both for confirming that a business name is unique and for credit reporting.
You can get a DUNS number by going to www.dnb.com/us and clicking the D&B D-U-N-S Number link. Clicking the D-U-N-S Number Only link on the site only starts the registration process. You must then complete the multi-page form, which requests information such as company name, address, and phone number, as well as your title and how to contact you. In addition, the application requests the legal structure of your business, the year it started, the primary line of business, the number of employees, the primary place of business (residence or commercial), and the number of active accounts. The Dun and Bradstreet firm requests that the application be completed by a senior company officer (for example, an owner).
If you're willing to wait up to 30 days for the processing of your application, there's no charge to obtain a DUNS number. For a fee of $69, you can obtain expedited processing of your application in one to five days.
To find out more about Smart Access and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/
Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.
This article is reproduced from the April 2004 issue of Smart Access. Copyright 2004, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.