locked
How to renew self created digital VBA certificate(will expire) for 50 workstations in silent RRS feed

  • Question

  •  

    I have signed VBA 2003 code with self created digital certificate. It will expire in next month.

    I have > 50 users for <50 computers.

    I want to avoid instalation of new certificate and Security Warning dialog box  regarding the digitally signed macro for every user in the computer. 

    *It will confuse users using this app for years,

    *How this could be done in same time for >50 users(24/7) on <50 computers ? I'm looking for an idea for script that will automatcly renew this certificate and/or  check " Alwayes trust macros from this publicher".

    Note: I understand that all this manualy settings are created to protect form viruses/hackers but if I'm creator of this certificate it should have some way to renew it. in other way this app will stop for days and will confuse users.   

     


    Monday, December 12, 2011 5:49 PM

Answers

  •  

    Hello Eleonora

    You haven’t said how you originally distributed the signed VBA code nor how you would renew the certificate if you couldn’t do it with script, so general statements and leads must be considered in context with your own situation. You apparently are aware that you can’t trust macros using automation.* The same consideration applies to scripting the setting for " Always trust macros from this publisher"

    Alternatives include creating a new certificate and mark Ii as ‘can be exported’ then write instructions for all the end-users on how to import it, and distributing a newly signed version of the macro library with instructions on how to remove the old and install the new, or to scan the content in the links below to see how certificates are renewed in other environments.

    There is a wealth of available content in the cloud, most of which is directed to servvers Using Microsoft bing you can query with the argument {“renew certificate” and self-signed. }Consider starting with this one:

    SCOM (Certificate Installation)
    http://social.technet.microsoft.com/forums/en-US/systemcenterrom/thread/cb1eec52-218e-4fa1-88b2-580e92b4b086/

    While the next link is for Small business server, it does describe using a network share from where everyone can import the certificate:

    How Do I Distribute the SBS 2008 Self-Signed SSL Certificate to My ...
    http://blogs.technet.com/b/sbs/archive/2008/09/30/how-do-i-distribute-the-sbs-2008-self-signed-ssl-certificate-to-my-users.aspx

    Look at the following content to see scripting examples:
    Creating Certificate Requests Using the Certificate Enrollment ...
    http://msdn.microsoft.com/en-us/library/ms867026.aspx

    This is general information about creating your own digital certificate extracted from a blog

    1) First you need to create your own digital certificate. I recommend downloading makecert here:
    http://www.source-code.biz/snippets/vbasic/3.htm
    Direct link:
    Download makecert.zip

    2) Then go to Windows command line prompt and issue this command (as described on that page)

    makecert -r -pe -n "CN=Your Name" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -ss My



    3) (OPTIONAL) If you want to, you can look at your new certificate:
    In the "Control Panel", open "Internet Options", then in the middle click on "Certificates".
    Your new, self-signed certificate will show up there, along with the expiration date of 2099
    This is the place to EXPORT the certificate so that you can import it onto your other computers.

    4) start Excel and open the Excel workbook that contains your VBA macro. Open menu item "Tools" --> "Macro" --> "Visual Basic Editor".

    5) In
    the Project Explorer window, select the VBA macro project that you want to digitally sign.

    6) In the VBA menu bar, open menu item "Tools" --> "Digital Signature"

    7) the
    Digital Signature dialog will show. Simply select your own certificate and sign your macro.

    8) Next time you open Excel, you can chose "Always trust this publisher"

    If you use this workbook on more than one computer, you should export your certificate from your first computer and import it onto each of the other computers. See step 3 where you can export. Save the file to your other computers (email it maybe).
    Then on the other computers, go to the control panel, same location, and select "Import certificate".

    End of extraction

    Here are links to more content on the subject – some is pretty general but might be helpful:

    Renewing Certification Authorities - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/cc962077.aspx

    Renewing certificates: Public Key - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/cc738405(v=WS.10).aspx

    Renewing a certification authority: Public Key
    http://technet.microsoft.com/en-us/library/cc740209(v=WS.10).aspx

    How do you renew certificates issued by Standalone CA
    http://social.technet.microsoft.com/Forums/en/winserversecurity/thread/768f3352-8830-4d41-846c-bdf2727da080

    Signing a VBA Project - MSDN – Explore Desktop, Web, Cloud, and ...
    http://msdn.microsoft.com/en-us/library/aa141471(v=office.10).aspx

    Which advises:
    Depending on how Office 2000 digital-signature features are being used in your organization, you may be able to obtain a digital certificate from your organization's internal certification authority. Your organization's publication process may not allow you to sign documents containing macros yourself. In this case, an administrator would sign a document that contains macros for you by using an approved certificate. For more information about your organization's policy, contact your network administrator or IT department.

    Enable Client Certificate Renewal - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/dd252804.aspx

    *Sometimes customers want to be able to programmatically digitally sign VBA macros in Office documents. Office does not provide a way to do this programmatically, because it would constitute a security hole. Architectural restrictions prevent a "command line" tool from being created to perform this process.

    There would actually be two ways to exploit a hypothetical method or property for digitally signing VBA macros.

    1) Malicious code could make a change to VBA code that had been digitally signed and then resign it with the same signature, so the user would never know a change had been made. The danger in this should be obvious. (What if the macro calculates exchange rates, or opens connections to a secure server?)

    2) Malicious code could inject a macro into an Office document using code like in:
    Q219905 - HOWTO: Dynamically Add and Run a VBA Macro from Visual Basic
    http://support.microsoft.com/support/kb/articles/q219/9/05.asp
    It could then digitally sign that code and distribute it. If the recipient had trusted the signature, then the macros would run when opened in Office without any warnings, even if macro security was set to High.

    There is no tool available that can take an Office file that contains VBA and digitally sign it. For the file itself, this is not possible because certain parts of the file like the document properties change each time the file is opened. Since a digital signature depends on each and every bit in the file remaining the same, this would invalidate the signature every time the file was used.

    Office files contain their VBA macros in a special structured storage stream within the file. When digitally signing an Office document, this stream is the only part of the file that is actually digitally signed. When the document is created, the Office application creates a stream and gives it to VBA. VBA saves whatever information it wishes in this stream, digitally signs the bits in the stream, and then passes the stream back to the application to be saved with the rest of the file. Whenever the document is opened, VBA checks the digital signature on the stream against the bits.

    In this scenario, the Office application doesn't care what VBA puts into the structured storage stream, and VBA doesn't care where the application saves the stream. This allows a great deal of flexibility in designing VBA hosts, but it makes creating a generic tool that could sign the VBA structured storage stream impossible. Any tool to do this would have to know where the application stores the VBA stream within the file, as well as where the digital signature for that stream is stored (they're not in the same place). There's no way to do this generically, which is why Microsoft didn't create a tool.

    It would be very difficult for a third-party to create such a tool as well, since the tool would have to understand the details of the VBA structured storage stream format and how VBA digitally signs that stream. As mentioned before, it would also need to know where in the host file to put the digital signature and how to indicate to the host that the file had been signed. There are no plans to develop such a tool.

    Please “Mark as answer” if the information in this post helps you solve your problem

    Regards,
    Chris Jensen
    Senior Technical Support Lead

    • Edited by cjatms Wednesday, December 14, 2011 7:38 PM
    • Marked as answer by Rex Zhang Tuesday, December 20, 2011 12:54 AM
    Wednesday, December 14, 2011 7:37 PM

All replies

  • Hello Eleonora Hadzhiyska,

     

    Thank you for your post.

     

    This is a quick note to let you know that we are performing research on this issue.

     

     

    Sincerely

    Rex Zhang


    Rex Zhang

    TechNet Community Support

    Wednesday, December 14, 2011 3:37 AM
  •  

    Hello Eleonora

    You haven’t said how you originally distributed the signed VBA code nor how you would renew the certificate if you couldn’t do it with script, so general statements and leads must be considered in context with your own situation. You apparently are aware that you can’t trust macros using automation.* The same consideration applies to scripting the setting for " Always trust macros from this publisher"

    Alternatives include creating a new certificate and mark Ii as ‘can be exported’ then write instructions for all the end-users on how to import it, and distributing a newly signed version of the macro library with instructions on how to remove the old and install the new, or to scan the content in the links below to see how certificates are renewed in other environments.

    There is a wealth of available content in the cloud, most of which is directed to servvers Using Microsoft bing you can query with the argument {“renew certificate” and self-signed. }Consider starting with this one:

    SCOM (Certificate Installation)
    http://social.technet.microsoft.com/forums/en-US/systemcenterrom/thread/cb1eec52-218e-4fa1-88b2-580e92b4b086/

    While the next link is for Small business server, it does describe using a network share from where everyone can import the certificate:

    How Do I Distribute the SBS 2008 Self-Signed SSL Certificate to My ...
    http://blogs.technet.com/b/sbs/archive/2008/09/30/how-do-i-distribute-the-sbs-2008-self-signed-ssl-certificate-to-my-users.aspx

    Look at the following content to see scripting examples:
    Creating Certificate Requests Using the Certificate Enrollment ...
    http://msdn.microsoft.com/en-us/library/ms867026.aspx

    This is general information about creating your own digital certificate extracted from a blog

    1) First you need to create your own digital certificate. I recommend downloading makecert here:
    http://www.source-code.biz/snippets/vbasic/3.htm
    Direct link:
    Download makecert.zip

    2) Then go to Windows command line prompt and issue this command (as described on that page)

    makecert -r -pe -n "CN=Your Name" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -ss My



    3) (OPTIONAL) If you want to, you can look at your new certificate:
    In the "Control Panel", open "Internet Options", then in the middle click on "Certificates".
    Your new, self-signed certificate will show up there, along with the expiration date of 2099
    This is the place to EXPORT the certificate so that you can import it onto your other computers.

    4) start Excel and open the Excel workbook that contains your VBA macro. Open menu item "Tools" --> "Macro" --> "Visual Basic Editor".

    5) In
    the Project Explorer window, select the VBA macro project that you want to digitally sign.

    6) In the VBA menu bar, open menu item "Tools" --> "Digital Signature"

    7) the
    Digital Signature dialog will show. Simply select your own certificate and sign your macro.

    8) Next time you open Excel, you can chose "Always trust this publisher"

    If you use this workbook on more than one computer, you should export your certificate from your first computer and import it onto each of the other computers. See step 3 where you can export. Save the file to your other computers (email it maybe).
    Then on the other computers, go to the control panel, same location, and select "Import certificate".

    End of extraction

    Here are links to more content on the subject – some is pretty general but might be helpful:

    Renewing Certification Authorities - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/cc962077.aspx

    Renewing certificates: Public Key - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/cc738405(v=WS.10).aspx

    Renewing a certification authority: Public Key
    http://technet.microsoft.com/en-us/library/cc740209(v=WS.10).aspx

    How do you renew certificates issued by Standalone CA
    http://social.technet.microsoft.com/Forums/en/winserversecurity/thread/768f3352-8830-4d41-846c-bdf2727da080

    Signing a VBA Project - MSDN – Explore Desktop, Web, Cloud, and ...
    http://msdn.microsoft.com/en-us/library/aa141471(v=office.10).aspx

    Which advises:
    Depending on how Office 2000 digital-signature features are being used in your organization, you may be able to obtain a digital certificate from your organization's internal certification authority. Your organization's publication process may not allow you to sign documents containing macros yourself. In this case, an administrator would sign a document that contains macros for you by using an approved certificate. For more information about your organization's policy, contact your network administrator or IT department.

    Enable Client Certificate Renewal - Microsoft TechNet: Resources ...
    http://technet.microsoft.com/en-us/library/dd252804.aspx

    *Sometimes customers want to be able to programmatically digitally sign VBA macros in Office documents. Office does not provide a way to do this programmatically, because it would constitute a security hole. Architectural restrictions prevent a "command line" tool from being created to perform this process.

    There would actually be two ways to exploit a hypothetical method or property for digitally signing VBA macros.

    1) Malicious code could make a change to VBA code that had been digitally signed and then resign it with the same signature, so the user would never know a change had been made. The danger in this should be obvious. (What if the macro calculates exchange rates, or opens connections to a secure server?)

    2) Malicious code could inject a macro into an Office document using code like in:
    Q219905 - HOWTO: Dynamically Add and Run a VBA Macro from Visual Basic
    http://support.microsoft.com/support/kb/articles/q219/9/05.asp
    It could then digitally sign that code and distribute it. If the recipient had trusted the signature, then the macros would run when opened in Office without any warnings, even if macro security was set to High.

    There is no tool available that can take an Office file that contains VBA and digitally sign it. For the file itself, this is not possible because certain parts of the file like the document properties change each time the file is opened. Since a digital signature depends on each and every bit in the file remaining the same, this would invalidate the signature every time the file was used.

    Office files contain their VBA macros in a special structured storage stream within the file. When digitally signing an Office document, this stream is the only part of the file that is actually digitally signed. When the document is created, the Office application creates a stream and gives it to VBA. VBA saves whatever information it wishes in this stream, digitally signs the bits in the stream, and then passes the stream back to the application to be saved with the rest of the file. Whenever the document is opened, VBA checks the digital signature on the stream against the bits.

    In this scenario, the Office application doesn't care what VBA puts into the structured storage stream, and VBA doesn't care where the application saves the stream. This allows a great deal of flexibility in designing VBA hosts, but it makes creating a generic tool that could sign the VBA structured storage stream impossible. Any tool to do this would have to know where the application stores the VBA stream within the file, as well as where the digital signature for that stream is stored (they're not in the same place). There's no way to do this generically, which is why Microsoft didn't create a tool.

    It would be very difficult for a third-party to create such a tool as well, since the tool would have to understand the details of the VBA structured storage stream format and how VBA digitally signs that stream. As mentioned before, it would also need to know where in the host file to put the digital signature and how to indicate to the host that the file had been signed. There are no plans to develop such a tool.

    Please “Mark as answer” if the information in this post helps you solve your problem

    Regards,
    Chris Jensen
    Senior Technical Support Lead

    • Edited by cjatms Wednesday, December 14, 2011 7:38 PM
    • Marked as answer by Rex Zhang Tuesday, December 20, 2011 12:54 AM
    Wednesday, December 14, 2011 7:37 PM