This project has moved. For the latest updates, please go here.
2
Vote

Trusted publisher for dynamic COM Helper (Custom Task Pane)

description

We digitally sign our Add-In to avoid the Excel blocking of unsigned Addins (Trust Center). However, the dynamically generated Com Helper for Custom Task Pane remains unsigned and gets blocked. Any suggestions?

file attachments

comments

govert wrote Jun 25, 2014 at 10:26 AM

This is a bit surprising, since the COM Helper is actually implemented by exactly the same binary - your .xll file. There is no separate, dynamically generated binary, though the registry entries for the COM loading are written on demand.

Could you post some detailed instructions on how I might recreate this, including the security settings I have to set up?

XBRLAnalyst wrote Jun 25, 2014 at 2:34 PM

In Excel 2013, in Trust Center -> Add-ins, check "Require application add-ins to be signed by Trusted Publisher". That's it.

I am attaching your ExcelDna.xll file renamed to CustomTaskPane.xll and signed by our signatures. It opens find and adds a Ribbon but when you click Show CTP, it gives an error about Unable to register Com Helper.

If you don't check "Require application...", CTP works just fine. All the other ExcelDna stuff works fine with signed add-in and the checkbox selected.

govert wrote Jul 7, 2014 at 12:00 PM

Just to follow up - I've not forgotten this. I think I am able to reproduce this, though I have to experiment with the settings a bit more. As I say - this is a bit surprising, since the .xll is registered as the COM server for the UserControl. I'll need to trace the process COM access or something, to see why Excel is not happy, but that's quite tricky... I also don't yet know anything about signing COM components in general.

XBRLAnalyst wrote Jul 9, 2014 at 3:06 PM

Looking forward to hear more updates. Thank you.

phrrngtn wrote Aug 7, 2014 at 9:06 PM

Govert pointed me to this workitem in https://groups.google.com/d/msg/exceldna/9OjQaTDtmj8/zQ_U7cMH2KUJ

I have exactly the same problem and can see that the Publisher information is not specified in the dynamically generated COM Helper (see the screenshot in the google groups thread). The exception message is being generated by line 157 of ExcelComAddIn.cs. Judging by the friendlyName I see in my problematic addin (see screenshot in the google groups thread referenced above), the addin is a COM AddIn (I will put in some debugging to print out the type name of addIn)

I don't have any experience with this but I would have thought that a much more workable approach would be to 'trampoline' our way out of the problem by having something within ExcelDNA implement ICustomTaskPaneConsumer and jump/delegate to the actual CTP code?

http://blogs.msdn.com/b/andreww/archive/2006/12/23/low-level-support-for-icustomtaskpaneconsumer-iribbonextensibility-formregionstartup-etc.aspx

What about foregoing the activation thing altogether and register the object into the ROT?

http://social.msdn.microsoft.com/Forums/vstudio/en-US/a076c88c-e25e-4575-aaa0-0c733411f1c2/registerring-a-vs-2010-addin-to-the-rot-running-object-table?forum=vsx

I have the ".NET and COM: The Complete Interoperability Guide" books somewhere at home.

I want this stuff to work and I would prefer to attack the COM complexity rather than go down the code-signing route.

govert wrote Aug 7, 2014 at 9:42 PM

Hi Paul,

[ I also have Adam Nathan's 1600 pages on .NET and COM, for emergencies. ]Excel gives us few options to avoid the COM stuff. Getting away from COM was one of the main motivations for starting Excel-DNA, and basing it on the Excel C API.

ICustomTaskPaneConsumer is exactly what the COM add-in we're loading is trying to expose.I'm not sure how you suggest the ROT might be used.

Can you check whether the publisher information from the .xll is displayed for the ribbon helper that is also registered and loaded dynamically?

It would help to make a plain COM add-in (not using Excel-DNA or VSTO), sign it, and check that the CTP stuff works using that signature.

Am I right that the Excel-DNA code path that registers and loads the ribbon is the same as that loading the CTP helper (and that the one works with the signing and the other does not)? Maybe I do something in one case that is different to the other?

Of course any other plans or discoveries about this would be very welcome.

-Govert

govert wrote Aug 7, 2014 at 9:50 PM

I need to clear up something.

Is the problem when loading the COM add-in (ExcelCustomTaskPaneAddIn), or when the UserControl is loaded.

Which of the following is the exact error you see: "The Ribbon/COM Add-in helper required by add-in {0} could not be registered.\r\nThis is an unexpected error.\r\nError message: {1}" "The CTP Helper required by add-in {0} could not be registered.\r\nThis may be due to restricted permissions on the user's HKCU\Software\Classes key.\r\nError message: {1}"* something else...

-Govert

phrrngtn wrote Aug 8, 2014 at 4:47 AM

Is the problem when loading the COM add-inThe problem manifests itself when the 'Show CTP' button is pressed which eventually runs this block. if (ctp == null) { // Make a new one using ExcelDna.Integration.CustomUI.CustomTaskPaneFactory ctp = CustomTaskPaneFactory.CreateCustomTaskPane(typeof(MyUserControl), "My Super Task Pane"); ctp.Visible = true; ctp.DockPosition = MsoCTPDockPosition.msoCTPDockPositionLeft; ctp.DockPositionStateChange += ctp_DockPositionStateChange; ctp.VisibleStateChange += ctp_VisibleStateChange; }
The catch is for Exception and produces the first error in your list above : "The Ribbon/COM ....".
Can you check whether the publisher information from the .xll is displayed for the ribbon helper that is also registered and loaded dynamically?
Very astute question! I checked and there is no publisher information for this addin but it does not seem to matter as that dynamically-generated add-inloads successfully irrespective of the Trust Center settings.

My suggestion about the ROT was a shot in the dark that maybe we could get around the authenticode stuff by just providing to Excel the object directly (so that it would not have to go through the normal DLL load process)

pjjH

XBRLAnalyst wrote Aug 8, 2014 at 12:55 PM

I can confirm too that it's the first error that Govert listed: "The Ribbon/COM Add-in helper required by add-in ..."

govert wrote Aug 10, 2014 at 8:36 PM

What does it say for the Error Message?

phrrngtn wrote Aug 11, 2014 at 5:30 AM

The Ribbon/COM Add-in helper required by add-in CustomTaskPane could not be registered.This is an unexpected error.Error message: Exception has been thrown by the target of an invocation.

kpc7335e wrote Oct 9, 2014 at 5:41 PM

I received the same error attempting to run the Table of Contents tutorial as I am new to ExcelDna. It works fine on my old laptop running Windows 7 and Office 2010. My new computer runs Windows 8 (64bit) with Office 2010 (x86). Has anyone had any success fixing the problem?

The Ribbon/COM Add-in helper required by add-in Table of Contents Add-in could not be registered.This is an unexpected error.Error message: Exception has been thrown by the target of an invocation.

govert wrote Oct 9, 2014 at 10:47 PM

Can you check whether the ribbon sample in the Excel-DNA distribution works?
  • Download and unzip Excel-DNA distribution. Copy the file Distribution\ExcelDna.xll to the Distribution\Samples\Ribbon directory. Rename it to "TestRibbon.xll"* Double-click to load in Excel and check if the extra tab is craeted.
-Govert

kpc7335e wrote Oct 10, 2014 at 1:01 AM

Govert thanks for your response. Unfortunately, I got the same error:

The Ribbon/COM Add-in helper required by add-in Ribbon Tests could not be registered.This is an unexpected error.Error message: Exception has been thrown by the target of an invocation.

Anything else I can try? I tried repairing Microsoft Primary Interop Assembly and office. No luck.

kpc7335e wrote Oct 10, 2014 at 2:48 AM

Okay so I narrowed down the culprit. Before running TOC addin and test ribbon addin, I created a different addin that didn't work. Even though it wasn't loaded or even active, it was still installed. In Excel, I had to go to COM-Addins section and remove the addin. Once I removed the bad COM Addin, the TOC and test ribbons work fine. Now I need to figure out what went wrong with that Addin. I'll keep you posted.

gv1955 wrote Oct 12, 2014 at 5:57 PM

Any updates on this issue? I've spent the entire morning trying to make a VSTO ribbon to work in Excel-DNA and I finally got it. However, after running a few times I started getting the errors above ("the Ribbon/COM Add-in helper", etc.). I rebooted the machine and reverted everything that I changed since it worked the last time and still cannot get it to work. I even cleared the Excel 2010 registry entries so I get a plain vanilla Excel and still nothing. Please help!

gv1955 wrote Oct 12, 2014 at 6:29 PM

UPDATE! In my quest to figure out what was the matter, I ran the Test Excel-DNA ribbon files and I was still getting the erros. I then found that the "test" XLL was flagged as Disabled so I cleared the Add-Ins from the COM Add-In list and the Test XLL is now loading. I then proceeded to my project and now it is loading. Something flaky about this whole "add-In" concept. Not necessarily an Excel-DNA issue but Excel itself.

Anyway, any words of wisdom will be greatly appreciated.

kpc7335e wrote Oct 16, 2014 at 12:25 AM

I narrowed down my issue a bit further. The aforementioned ribbon error was caused by an add-in that I created via ExcelDnaPack that referred to xml files outside of the packed file. ExcelDnaPack did not let me pack xml files so I put them in the same directory as the packed XLL file and attempted to access them. The packed XLL did not like that and caused errors for any dynamically loaded COM Add-ins that I created. I had to remove that packed XLL from Excel completely. I did the same as gv1955 and removed from the COM Add-In menu.

I understand that ExcelDna does not support mixed assemblies. Are there specific file types other than images and DLLs that I can reference in the dna file and pack? It would be helpful to pack other files. For now, I am not using the ExceDnaPacked XLL to get my add-in working.

govert wrote Oct 16, 2014 at 6:38 AM

Failure in your GetCustomUI() callback does cause trouble.

Instead of using the ExcelDnaPack to pack extra files, you can use regular resources in your managed library. This should be a start: http://msdn.microsoft.com/en-us/library/7k989cfy(v=vs.90).aspx.

kpc7335e wrote Oct 16, 2014 at 3:14 PM

Thanks govert I'll look into later today. Will MS Visual Studio Express pack the resources in the dll? So instead of having to copy additional files to the client machine, the resource is packed in the dll?

GarethHayter wrote Apr 28, 2015 at 10:40 PM

Hi Govert. I'm having exactly the same issue: calls to CustomTaskPaneFactory.CreateCustomTaskPane() fail when File > Options > Trust Center > Trust Center Settings > 'Require Application Add-ins to be signed by Trusted Publisher' is checked, and the XLL is actually signed with an Authenticode certificate.

The NullReferenceException referred to in the comments above is thrown here:
ExcelDna.Integration.CustomUI.CustomTaskPaneFactory.CreateCustomTaskPane(string controlProgId, string title, object parent) on this line:
CustomTaskPane newCTP = factory.CreateCTP(controlProgId, title, parent);

However, this masks the base exception, which occurs earlier here:
ExcelDna.Integration.ExcelComAddiInHelper.LoadComAddIn(ExcelComAddIn addIn) on this line:
comAddIn.GetType().InvokeMember("Connect", BindingFlags.SetProperty, null, comAddIn, new object[] { true }, ci);

The exception thrown on the line above is a TargetInvocationException. It seems that the 'Connect' property cannot be accessed.

Something else unusual is that the exception is caught and a message is written to the LogDisplay, but never gets displayed.

This base exception is preceeded by two earlier exceptions that are caught and handled:
1) ExcelDna.ComInterop.ComRegistration.ProgIdUacRegistration.ProgIdUacRegistration: writing to HKCR fails, so we write to HKCU.
2) ExcelDna.ComInterop.ComRegistration.ClsIdUacRegistration.ClsIdUacRegistration: writing to HKCR fails, so we write to HKCU.

Note: I call ExcelComAddiInHelper.LoadComAddIn() directly in two other places in the code before CreateCustomTaskPane is called, and it works flawlessly in both instances.

GarethHayter wrote Apr 29, 2015 at 1:45 AM

Something else that's interesting: I stepped through the code line by line, and when setting the HKCU value, the corresponding HKCR value also gets set automatically. ie: setting the HKCR directly fails with an UnauthorizedAccessException, but setting the HKCU values causes the HKCR value to get created as well. Is this standard behaviour for registry class entries? If yes, then maybe we don't even need to bother setting the HKCR value....just set the HKCU value and wait for it to propagate ;-)

phrrngtn wrote Apr 29, 2015 at 5:47 AM

This is really interesting! One of the things that I have used in to help out with registration stuff is RegOverridePredefKey which allows one to use registration code which insists on writing to HKCR but you can fake out to write to HKCU

pjjH
@contextmanager
def diddle_HKCR():
    try:
        hkey = RegOpenKey(HKEY_CURRENT_USER, r'''Software\Classes''')
        RegOverridePredefKey(HKEY_CLASSES_ROOT, hkey)
        yield
    finally:
        RegOverridePredefKey(HKEY_CLASSES_ROOT, None)

GarethHayter wrote Apr 29, 2015 at 7:09 AM

By the way Govert, I don't define my ribbon XML in the .dna file, I created a COM-visible class that inherits from ExcelDna.Integration.CustomUI.ExcelRibbon, and this class resides inside a DLL which I don't pack into the XLL file - I refer to it like this:

<Reference AssemblyPath="FormulaDesk.dll" Pack="false" />

Then I sign the XLL and DLL with an Authenticode certificate. Not sure if this setup has any bearing on the problem.

govert wrote Apr 29, 2015 at 8:08 AM

Thanks for giving more details on this issue.

I made some changes to the COM registration in the latest source versions on GitHub. The various COM bits now work the same way (in the past the class factories were handled differently for RTD servers and some COM add-ins in some context). Now the registration always sets the .xll as the LocalServer32 and uses the .xll entrypoint to get the class factory.
Also, the registration now always first tries agains the machine hive, before falling back to the user hive.

So I suggest you start any source investigation by checking out the project from GitHub https://github.com/Excel-DNA/ExcelDna

However, I don't think any of this will sort out the CTP control not loading when signing is required. In theory Excel should look for a certificate on the .xll, but apparently does not in that context.

A start might be to put together a sample VSTO project that does load a CTP under the same Excel setting.
But I think the VSTO registration always needs admin rights, and gets put in the (possibly redirected) machine hive. Excel might just not accept certificates from libs registered under the user hive.

-Govert

govert wrote Apr 29, 2015 at 8:15 AM

@Gareth - for the ribbon, signing the .dll should have no effect ( it would be nice to confirm) and Excel should pick up the certificate from the .xll. It might be different in the new code on GitHub, since the ribbon registration is now always putting the .xll as the COM server before loading.

Whether the ribbon .xml is stored in the .dna file or returned from your GetCustomUI should make no difference.

-Govert

phrrngtn wrote May 30, 2015 at 12:13 AM

When RequireAddinSig is 1, the CTP does not display; when 0, it does.
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security
    ExtensionHardening    REG_DWORD    0x0
    RequireAddinSig    REG_DWORD    0x1
    VBAWarnings    REG_DWORD    0x3
I should note that all of these tests were carried out with the AppSense user virtualization s/w installed.

Govert, I would be interested to hear if you are able to get the CTP to show when RequireAddinSig is set to 0x1 and the XLL is signed.

phrrngtn wrote Jun 1, 2015 at 11:12 PM

I am unable to reproduce the problem with running against 07ff7d53b8fcf324087a32a31a38ec2fddb9bd4c in github. This is really excellent news!

govert wrote Jun 2, 2015 at 12:07 PM

Hi Paul - Just to confirm: do you mean your the post from May 30 about RequireAddinSig was still with the old version, and works correctly with the current source check-in?

-Govret

phrrngtn wrote Jun 2, 2015 at 2:11 PM

Yes! The only revision of ExcelDNA I have thus far got a CTP to work with trust-center settings of RequireAddinSig=1 is the git revision, 07ff7d53b8fcf324087a32a31a38ec2fddb9bd4c. I am running Windows 7 and Excel 32-bit 14.0.7147.5001

Sorry about leaving out so much important identifying information from the bug report. It has just been so difficult to figure out what has been going on due to the complexities of the environment (especially AppSense).

I am running the addin unpacked and with references to external DLLs such as NLog (for logging). I have a User Control in the CTP that I drew some controls onto within Visual Studio. I put in code-signing as a post-build step and have full F5 debugging with Excel. The solution is integrated into git (internal gitlab-hosted repo).

I intend to test out the following features:
1) RTD server
2) RX extensions (fed by ZMQ event-stream from Python apps on Linux.)
3) Implementing Research Pane and Smart Tag interfaces within ExcelDNA. I have some win32-Python based implementations (spyne for the Research Service, pythoncom for Smart Tag) that might be fun to port to ExcelDNA.
4) packing the extension (codesign is not yet working for me with the packed extension. This may be due to references to mixed-mode assemblies in the solution)
5) attempt to get matplotlib (Python) plotting window embedded within CTP window (very much a stretch goal!)

I will attempt to use the https://github.com/Excel-DNA/Samples as a starting point (where possible) to make it easier to submit patches, open issues etc.

If all COM registration-related stuff now just works within admin access then this is a huge step forward and removes many barriers to the widespread deployment and maintenance of ExcelDNA-based packages. I am very grateful for your work in this area. Thanks very much!

pjjH

govert wrote Jun 2, 2015 at 2:44 PM

OK great. The fix was probably then the stuff I did in April - so anything since git revision ce941f5ec9a6fa10ba72287cea5c689b4dce29fd should work the same.

Mixed-mode assemblies are still not supported in the ExcelDnaPack packing.

You'd need to check the RTD stuff in the restricted security environment, though I would expect it to work fine.

I would like to work on some kind of Add-in Manager for Excel, allowing Excel-DNA add-ins to be deployed and managed in various ways, either from a central internal location or via Internet feeds, like NuGet. But it's not currently a high priority for me.

-Govert

phrrngtn wrote Jun 3, 2015 at 5:16 PM

Govert,
I have everything both building and working cleanly from generic workstations (with .NET 4 and git installed). See below for some snippets on configuration. Everything is way more robust and deterministic than at any point in the past.

pjjH

I added (via VS 2013) a nuget referece to Excel-DNA.Interop to the solution but my references to ExcelDNA itself are to the Distribution sub-directory of my git checkout. The 'embed interop types' is set correctly for the reference within the solution.
<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Excel-DNA.Interop" version="14.0.1" targetFramework="net45" />
  <package id="NLog" version="3.2.1" targetFramework="net45" />
</packages>
I am able to do a git clone of my addin repo and the ExcelDNA github repo to any machine and build, pack and sign my extension via msbuild (I have copies of the executables and the ExcelDNA artifcats in a directory that is on my path. Note that this is different from running via F5 in Visual Studio where I have the addin run against development copies of the ExcelDNA assemblies):
PostBuildEvent:
  ExcelDnaPack.exe .\DemoCTP.dna /O .\DemoCTP.xll /Y
  signtool sign /a DemoCTP.xll
Notes:
  1. I had to be very careful with the command-line arguments to ExcelDnaPack. My previous comment about packing failing was due to omitting leading '.\ ' from the paths and had nothing to do with mixed-mode assemblies (I incorrectly thought it had something to do with this as I had a reference to a mixed mode assembly in the solution but was not referencing it in the anywhere else in the code nor in the .dna)
  2. It so happens that I have one one certificate so signtool sign /a works fine as is. If you want to sign with a particular certificate, you can be more specific with the /sha1 flag
  3. The .xll.config file is packed by ExcelDnaPack so you can do a single-file install. I have tested this on a number of workstations at my place of work and it works perfectly.
  4. configuration of, and logging by, NLog is working correctly. See configuration file below.
  5. I am using Microsoft.Office.Interop.Excel for the addin to consume Excel SheetSelectionChange events. I found it hard to locate any C# examples of what I wanted to do so have included a snippet below in case it may be of some use to others.
here is the NLog configuration
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="nlog" type="NLog.Config.ConfigSectionHandler, NLog"/>
  </configSections>
  <nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <targets>
      <target name="logfile" xsi:type="File" fileName="${basedir}/DemoCTP_log.txt"/>
    </targets>
    <rules>
      <logger name="*" minLevel="Info" writeTo="logfile"/>
    </rules>
  </nlog>
</configuration>
is this the correct way to consume Excel-level events? in any case, it seems to be working for me.
            // based on example in:
            // https://groups.google.com/forum/#!searchin/exceldna/SheetSelectionChange$20/exceldna/_z9i7NMZz6c/xqlwH4NkRDgJ
            if (xlApp == null)
            {
                xlApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
            }

            // install the handler
            xlApp.SheetSelectionChange += xlApp_SheetSelectionChange;
It took me a long time to get this code working so recording it here in case anyone else comes across it while searching. I have left in as a comment a hack of finding a particular control within the CTP ContentControl.
        // a stub was generated for us by Visual Studio with the right signature by typing in
        // the xlApp.SheetSelectChange += <TAB>
        private static void xlApp_SheetSelectionChange(object Sh, Range Target)
        {
            // need to figure out some way of interfacing between the events and the CTP controls.
            // HACK: experiment to make sure that COM events from Excel are being consumed by the ctp object and
            // that we have some way of communicating with the controls.
            //DateTimePicker p;
            //p = (DateTimePicker)(((MyUserControl)ctp.ContentControl).Controls.Find("dateTimePicker1", true).First());
            //xlApp.StatusBar = p.Value.ToString();
            xlApp.StatusBar = "Selection is at " + Target.Address;
        }

govert wrote Jun 3, 2015 at 9:10 PM

Are you saying that it makes a difference to ExcelDnaPack whether put the arguments as ".\XXX.dna" vs. just "XXX.dna"? Do you have any idea why that would be?

phrrngtn wrote Jun 24, 2015 at 10:32 PM