I have some macro-containing Excel sheets that will be distributed to all the employees in my office. I don't want the users to get the warning about macros when they open the file. Signing my code with a certificate seems like the way to accomplish this. I've created a certificate with the Certificate Request Wizard (I'm using Windows 2000) and signed my code with it. This works, but only on the computer that I created the certificate on. What I'd like is to designate my certificate as trusted on all the computers in out lan. Is this possible? I know we can buy a "real" certificate from someplace like Thawte, but this seems like overkill since it's strictly for use within our office and we have no intention of distributing the Excel file to the outside world. I'm no expert on certificates or network administration, so feel free to point out the obvious. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Do I have to buy a certificate in order to sign my code? 13
- Thread starter Jerrycurl
- Start date
-
12
- #2
I have run into this frustrating problem many times!!! If you don't want to purchase a digital signature from Thawte or Verisign then your only other option is to use the selfcert tool from microsoft:
When you create a dig sig using the selfcert tool you can then add it to a VBA project by selecting that signature in the VBA editor under the Tools menu.
When a user opens your application, the security warning they see depends upon their security settings for that application. If their security is set to 'medium' then they see a warning that asks them to disable or enable macros (both disable and enable buttons are enabled). (VBA code is treated as macros by the security features of Office) There will also be an unchecked check box that says 'always trust macros from this source'. If the user checks the box and then clicks 'enable', they will never again be prompted with a security warning for that application.
If the user's security settings for the application are set to 'high', then the 'enable macros' button is disabled and in order to enable the 'enable macros' button they must first check the box. Again, from then on the user won't be prompted with a security warning for that applications again.
When Office is installed, the default security setting for Word is HIGH and for Excel is MEDIUM. Word can really give you problems becuase many users don't bother to read the security warning and don't understand that they first need to click the check box and then click the 'enable macros' button. It's even worse if you have added VBA code to a Word document and don't sign it at all because if the user's security setting is 'HIGH' (the default) the doc will open with no security warning in design mode with all code disabled. So you HAVE to sign Word automation apps.
The only way to change the security setting for an Office application is from the Visual Basic toolbar which isn't visible unless the user right clicks in the toolbar area and selects it. You can't change the user's security settings via code in your project because that would obviously defeat the purpose of preventing malicious code from running.
So your best bet is to send an email to all the users of your app and tell them how to accept your signature the first time they run your application otherwise they won't figure it out - trust me I been through this many times. Also, when you use the self cert tool to sign your project the security warning will state that the publisher (i.e., you) hasn't been authenticated and shouldn't be trusted so make sure when you create the signature using the selfcert tool you include your name and the company name (there is only one input line so you will have to combine them) so users will have some level of comfort. When you self sign a project, I think the security warning looks much worse from a 'creating fear in the user' standpoint than unsigned projects. From the programmers standpoint this is frustration because you know your code is safe, but from Microsoft's standpoint they don't know you or your intentions. But again if you can get your users to accept your signature, they won't see any more security warnings.
Really a dig sig from Thawte (they're cheaper) or Verisign is the best solution, but they are expensive and require a yearly fee and you can't just call them up and request a signature - you have to go through a process and verify your company and your validity to them. If you happen to work for a software company someone in your company already probably has a authenticated signature your could use.
Sorry for the long reply, but I have felt your pain before. It's hard enough getting your code to work right across multiple machines and OS versions without having to deal with the security issues.
When you create a dig sig using the selfcert tool you can then add it to a VBA project by selecting that signature in the VBA editor under the Tools menu.
When a user opens your application, the security warning they see depends upon their security settings for that application. If their security is set to 'medium' then they see a warning that asks them to disable or enable macros (both disable and enable buttons are enabled). (VBA code is treated as macros by the security features of Office) There will also be an unchecked check box that says 'always trust macros from this source'. If the user checks the box and then clicks 'enable', they will never again be prompted with a security warning for that application.
If the user's security settings for the application are set to 'high', then the 'enable macros' button is disabled and in order to enable the 'enable macros' button they must first check the box. Again, from then on the user won't be prompted with a security warning for that applications again.
When Office is installed, the default security setting for Word is HIGH and for Excel is MEDIUM. Word can really give you problems becuase many users don't bother to read the security warning and don't understand that they first need to click the check box and then click the 'enable macros' button. It's even worse if you have added VBA code to a Word document and don't sign it at all because if the user's security setting is 'HIGH' (the default) the doc will open with no security warning in design mode with all code disabled. So you HAVE to sign Word automation apps.
The only way to change the security setting for an Office application is from the Visual Basic toolbar which isn't visible unless the user right clicks in the toolbar area and selects it. You can't change the user's security settings via code in your project because that would obviously defeat the purpose of preventing malicious code from running.
So your best bet is to send an email to all the users of your app and tell them how to accept your signature the first time they run your application otherwise they won't figure it out - trust me I been through this many times. Also, when you use the self cert tool to sign your project the security warning will state that the publisher (i.e., you) hasn't been authenticated and shouldn't be trusted so make sure when you create the signature using the selfcert tool you include your name and the company name (there is only one input line so you will have to combine them) so users will have some level of comfort. When you self sign a project, I think the security warning looks much worse from a 'creating fear in the user' standpoint than unsigned projects. From the programmers standpoint this is frustration because you know your code is safe, but from Microsoft's standpoint they don't know you or your intentions. But again if you can get your users to accept your signature, they won't see any more security warnings.
Really a dig sig from Thawte (they're cheaper) or Verisign is the best solution, but they are expensive and require a yearly fee and you can't just call them up and request a signature - you have to go through a process and verify your company and your validity to them. If you happen to work for a software company someone in your company already probably has a authenticated signature your could use.
Sorry for the long reply, but I have felt your pain before. It's hard enough getting your code to work right across multiple machines and OS versions without having to deal with the security issues.
rmikesmith
Technical User
VBABoy,
This is a great tutorial on a subject I'll bet many of us who don't create commercial Office apps have scant knowledge of. Thanks, and have a star!
Regards,
Mike
This is a great tutorial on a subject I'll bet many of us who don't create commercial Office apps have scant knowledge of. Thanks, and have a star!
Regards,
Mike
- Thread starter
- #4
Thanks for the reply. Another question: Is there no way for the network admin to add a certificate made with selfcert to the trusted certificate pool? The article at
says "...Administrators can specify trusted sources through system or group policies...". I'm not sure what that means, or if that can happen at any time or if it must happen before Office is installed. What a pain for such a stupid little problem.
says "...Administrators can specify trusted sources through system or group policies...". I'm not sure what that means, or if that can happen at any time or if it must happen before Office is installed. What a pain for such a stupid little problem.
Jerrycurl,
I'm not an IT guy so I don't know a lot about Windows administration, but I have looked into this some before.
I don't see how an IT admin could 'push out' a Trusted Source to an Office installation after Office has already been installed. The article you reference mentions Active Directory so maybe it can be done, but this knowledge is beyond my meager realm. Also, the article is written from an Office XP standpoint and I have only worked with Office 97 and 2k. I also read that the default setting for Excel in Office XP is now HIGH so the problem is getting worse.
I have found that IT departments are usually stretched to the limit already so unless your app is mission critical, getting corporate help can be challenging. Second, adding you as a trusted source may deviate from IT policy and required approval from the CTO to change the way end user systems are set up (this is especially true in large companies). Thirdly, few Office users even know you can do such cool things as automating applications and accordingly IT staffs don't deal with the issues surrounding distribution of VBA applications very much. It's quite possible your IT support person won't know how to add you to the list of Trusted Sources (if it can be done on a group basis) and will have to take the time to figure out how to do it(see point above).
If you know someone in IT that is helpful, by all means see if they can help you out. If you figure out a way to add yourself as a Trusted Source to existing Office installations please respond as I would be thrilled!
By the way, if you plan on having your application accessible via the Web or corporate Intranet, you should publish them in Zip format. This is because if the user opens your app with Internet Explorer, the application will actually open in IE with a Word interface. While this would be really cool if it worked, I have found that VBA apps are very unstable and unpredictable when opened with IE. You essentially have to write and test your code as being written for two different platforms and IE basically revs on a constant basis because of all the security threats associated with the Web. The easier solution I have found is to just publish in Zip format which will force the app to open in an actual instance of the Office application.
Now aren't you glad you have joined the wonderful (frustrating?) world of Office automation? Good luck!
VBABoy
I'm not an IT guy so I don't know a lot about Windows administration, but I have looked into this some before.
I don't see how an IT admin could 'push out' a Trusted Source to an Office installation after Office has already been installed. The article you reference mentions Active Directory so maybe it can be done, but this knowledge is beyond my meager realm. Also, the article is written from an Office XP standpoint and I have only worked with Office 97 and 2k. I also read that the default setting for Excel in Office XP is now HIGH so the problem is getting worse.
I have found that IT departments are usually stretched to the limit already so unless your app is mission critical, getting corporate help can be challenging. Second, adding you as a trusted source may deviate from IT policy and required approval from the CTO to change the way end user systems are set up (this is especially true in large companies). Thirdly, few Office users even know you can do such cool things as automating applications and accordingly IT staffs don't deal with the issues surrounding distribution of VBA applications very much. It's quite possible your IT support person won't know how to add you to the list of Trusted Sources (if it can be done on a group basis) and will have to take the time to figure out how to do it(see point above).
If you know someone in IT that is helpful, by all means see if they can help you out. If you figure out a way to add yourself as a Trusted Source to existing Office installations please respond as I would be thrilled!
By the way, if you plan on having your application accessible via the Web or corporate Intranet, you should publish them in Zip format. This is because if the user opens your app with Internet Explorer, the application will actually open in IE with a Word interface. While this would be really cool if it worked, I have found that VBA apps are very unstable and unpredictable when opened with IE. You essentially have to write and test your code as being written for two different platforms and IE basically revs on a constant basis because of all the security threats associated with the Web. The easier solution I have found is to just publish in Zip format which will force the app to open in an actual instance of the Office application.
Now aren't you glad you have joined the wonderful (frustrating?) world of Office automation? Good luck!
VBABoy
Jerrycurl,
In my post above, I meant to say that Internet Explorer will open the app in the browser with an interface appropriate for that app. So if a user clicks on a Word document with IE, it will open it with a Word interface. If the user clicks on a workbook, the browser will open it with an Excel interface, etc.
VBABoy
In my post above, I meant to say that Internet Explorer will open the app in the browser with an interface appropriate for that app. So if a user clicks on a Word document with IE, it will open it with a Word interface. If the user clicks on a workbook, the browser will open it with an Excel interface, etc.
VBABoy
-
1
- #8
I just stumbled across this thread. This is FAQ material (hint hint, VBABoy)!
I should've read this sooner. I could've used this a while back when I started sending out VBA apps to coworkers. Now, however, my company has upgraded to XP and set all the Offie default macro security levels to Low, so I don't have to worry about it anymore. But I will definitely keep this information put aside for future use--I have a feeling in will come in handy some day.
You've already got 5 stars, but heck, I'm giving you another anyway for this top rate info.
I should've read this sooner. I could've used this a while back when I started sending out VBA apps to coworkers. Now, however, my company has upgraded to XP and set all the Offie default macro security levels to Low, so I don't have to worry about it anymore. But I will definitely keep this information put aside for future use--I have a feeling in will come in handy some day.
You've already got 5 stars, but heck, I'm giving you another anyway for this top rate info.
What's to stop someone else from making digsig with the same display name as an already accepted digsig and distributing harmful apps? (Let's assume the worst case that the intent is to make any harmful effects appear to be caused by the owner of the original digsig)
If you're aware of a good digsig FAQ, please feel free to redirect me.
If you're aware of a good digsig FAQ, please feel free to redirect me.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question