When a user takes an external list offline to either SharePoint Workspace or Outlook, by default the user gets a dialog box like this one. To take the external list offline successfully, the user has to click on the Install button, which means that he or she trusts the package, even if the publisher of the package cannot be verified.
Essentially, when a user takes an External List offline, SharePoint generates a VSTO ClickOnce package for a client side solution. Every VSTO package should be signed by an Authenticode certificate to specify who published this package. The user installs the package on his or her client through the VSTO Installer and goes through the trusting process to verify if the package was published by a trusted publisher. For more information please see Publish Office Solution for VSTO ClickOnce packages, Granting Trust to Office Solutions for granting trust to general office solutions, and Trusted Application Deployment for granting trust to general ClickOnce Applications. By default, SharePoint does not have a certificate that can be used to sign the package. So, SharePoint generates a self-signed certificate and signs the package with it. Since this is a new self-signed certificate, the client has trusted neither this certificate nor its issuer before and hence it cannot verify the publisher of the package. That is why VSTO installer prompts the user with the message that the publisher cannot be verified. To get rid of the unfriendly prompt, an Authenticode certificate that was issued by a trusted Certification Authority must be provided to SharePoint for signing the VSTO ClickOnce package. In this post, I will show you how to provide such certificate to SharePoint. We are going to need administration privileges on each of the Web Front Ends (WFEs) of the farm.
First, we need an Authenticode certificate that is issued by a trusted Certification Authority, e.g. VeriSign. For details on how to obtain a certificate for signing, see ClickOnce and Authenticode. The certificate MUST contain a private cryptographic key so that it can be used for signing. For this demonstration, we generated two certificates and saved them as ContosoRoot.cer and ContosoBCS.pfx by using MakeCert.exe. The certificate contained in ContosoBCS.pfx has a private key and will be used to sign the VSTO ClickOnce package. The certificate in ContosoRoot.cer serves as the certificate of a Certification Authority and was used to sign the certificate contained in ContosoBCS.pfx. Note that the certificate in ContosoRoot.cer does not have a private key.
In the following steps, we are going to import those two certificates to the certificate stores on both WFEs and clients. Please note that you should never import a certificate to a production machine unless you trust the certificate.
Second, we need to create a certificate store named BusinessConnectivityServices for Local Computer on each of the WFEs of the farm. The BusinessConnectivityServices certificate store for Local Computer is where SharePoint looks for the certificate used to sign a VSTO ClickOnce package. You can create the certificate store by adding the key “BusinessConnectivityServices” under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SystemCertificates. Alternatively, you can copy and paste the following text to a notepad, save it as a .reg file, and then double click the file to create the registry key.
Windows Registry Editor Version 5.00
Third, we need to import the Authenticode certificate, e.g. ContosoBCS.pfx, to the certificate stores for Local Computer on all the WFEs, which can be done through Microsoft Management Console (MMC). On a WFE open the Start menu, click on Run…, and type mmc. This will launch MMC. Add a certificate snap-in for the Local Computer to the MMC. The details on how to add a certificate snap-in can be found in the MSDN article How to: View Certificates with the MMC Snap-in. Make sure that the certificate snap-in is for Local Computer, not for Current User. This can be verified by checking if the entry under Console Root in MMC is Certificates (Local Computer). If the certificate snap-in is for Current User, the entry is Certificates – Current User. In MMC, under Console Root\Certificates (Local Computer), there is an entry BusinessConnectivityServices, which was created when we added the registry key in last step. Right click the entry, then from the pop-up menu, point to All tasks and then click on Import. This will launch the wizard to import certificate. Follow the wizard and import the ContosoBCS.pfx to the store BusinessConnectivityServices. If the certificate of the Certification Authority is not trusted by a WFE, import the certificate of the Certification Authority, e.g. ContosoRoot.cer, to the store Trusted Root Certification Authorities in the same way.
Fourth, on a client machine where a user will take an external list offline to, if the Certification Authority that issues the certificate for signing is not in the Trusted Root Certification Authorities certificate store for current user yet, the user needs to import the certificate for the Certification Authority, e.g. ContosoRoot.cer, to the certificate store Trusted Root Certification Authorities for current user on the client machine. Note that the certificate only needs to contain a public key. Actually, you should not import the certificate for the Certification Authority with a private key to a client machine. Also note that in an organization that has PKI infrastructure in place, the Certification Authority’s certificate may have already been deployed to the Trusted Root Certification Authorities certificate store of the client machines. If this is the case, the fourth step can be skipped. Since in this example we generated the ContosoRoot.cer, our client does not have the certificate in the store and we should import the certificate. Again, we can import this certificate through MMC, as shown in the following figure. Note that at this time, the MMC has a certificate snap-in for Current User.
Now, if the Authenticode certificate used to sign the VSTO ClickOnce package has been imported to the Trusted Publishers certificate store for current user on the client machine, the package will be installed without prompting. Otherwise, when a user takes an external list offline, he or she will see the following friendly prompt with the publisher name.
If the external list has been taken offline before you imported the Authenticode certificate to the WFEs, you may have to remove the old package from the server so that a package signed with the new certificate will be generated. You can do this through SharePoint Designer. For example, if you want to remove the old package for external list “Product List” on site http://contoso, you can open http://contoso in SharePoint Designer, click on All Files under Site Objects in the Navigation pane, and an All Files tab will show up. Click on Lists in the All Files tab then Product List, you should see a folder named ClientSolution. Remove this folder by selecting the folder name and then clicking on the Delete button in the ribbon. From this point on, if a user takes this list offline, he or she will not see the unfriendly prompt shown at the beginning of this post.
Note that, if any of the following is true, taking an external list offline may fail.
- The certificate imported into the certificate store BusinessConnectivityServices for Local Machine on a WFE does not contain a private key;
- There is more than one certificate in the store BusinessConnectivityServices.
To fix those issues, you have to clean up the certificate store and import a certificate with a private key.
After the certificate is imported, if you get the error “Failed to publish Solution <external list name> because keyset does not exist”, please make sure that the certificate imported to the certificate store LOCAL_MACHINE\BusinessConnectivityServices contains a private key. If the certificate contains a private key, but you still get the error, the security account for the application pool used for the hosting Web application for the SharePoint site may not have access to the private key of the certificate imported from ContosoBCS.pfx. You can examine who has access to the private key by running the following command on WFEs, where ContosoBCS is the subject of certificate:
winhttpcertcfg.exe -l -c LOCAL_MACHINE\BusinessConnectivityServices –s ContosoBCS
This command will show a list of accounts and groups with access to the private key. If neither the local group WSS_WPG nor the security account for the application pool is in the list, the security account for the application pool does not have access to the private key.
To fix this issue, run the following command:
winhttpcertcfg.exe -g -c LOCAL_MACHINE\BusinessConnectivityServices –s ContosoBCS –a <application pool account>
For more information about winhttpcertcfg.exe please see WinHttpCertCfg.exe, a Certificate Configuration Tool.
– Bin Zhang