Save Your SharePoint Online Tenant: The SharePoint Sandboxed Solutions Inspector

If you’ve been following the “code-based sandbox solutions on Office 365” saga, you know that there is little time left to fix your existing sandbox solutions in Office 365. See: Microsoft Is Removing Code-Based Sandbox Solutions in SharePoint Online – Be Prepared!

Last week, Vesa Juvonen (@vesajuvonen) released a script (New Script Available from Microsoft PnP: Generate list of sandbox solutions from SharePoint Online tenant) that can help you find your sandbox solutions. Surprisingly, what was missing from Vesa’s script was identification of the solutions that contain code. You’d get a list of all your sandbox solutions, but not specifically the ones that were going to cause you problems.

The Rencore Team

Some of the great looking folks at Rencore. Where’s Waldek?

My awesome friends at Rencore – the SFCAF folks – were kind enough to make a free tool available this week to help with even more with your diagnosis and even some of the cures. In Erwin van Hunen’s (@erwinvanhunenpost Introducing the Rencore SharePoint Sandboxed Solutions Inspector, you can learn more about the free tool and how it can help.

On August 31st, 2016 Microsoft is going to shut down support for Sandboxed Solutions with code.

Sandboxed Solutions containing code will be deactivated and this might impact your Office 365 tenant big time!

If you know you need it, just head right on over to the download page.

The SharePoint Sandboxed Solutions Inspector

They have already released several updates to the tool, and are keeping it current based on feedback from the folks using it. Now that’s service – and for a free tool!

But I think the best thing is that the Rencore tool can fix some of the most common issues – most notably the “empty DLL” issue that makes Office 365 think you have code in your sandbox solution when you don’t.

We heard “30 days” when all this started, and now people seem to be taking that as August 31. Don’t leave your users in the lurch – get going on handling this situation.

Oh, and if you’re a vendor or consultant who has written a sandbox solution with code over the last few years: reach out to your client and own it. Get them back on the right road and you’ll be the better for it.

Clean report!

Clean report!

New Script Available from Microsoft PnP: Generate list of sandbox solutions from SharePoint Online tenant

As I wrote on Monday, the decision to begin removing already deprecated code-based sandbox solutions on SharePoint Online took many people by surprise. Even though the news about the deprecation has been out there since 2014, the abrupt move – especially during a time when many people are on summer vacation – caused consternation for some. Others welcomed the move, in essence saying “good riddance” to a model that never really reached maturity.

But what if you manage a tenant on Office 365 that might have code-based sandbox solutions? Maybe you’ve used outside vendors to build solutions for you and you’re not sure what techniques they have used. Or maybe your own team built some things a few years back, you’ve had some turnover, and you source control isn’t so great. (Not so unusual, frankly.) How do you know what you have and what to do about it? You certainly don’t want functionality your users actually need to stop working unexpectedly. Some of these solutions could be InfoPath forms with code-behind, for example.

Office 365 Dev Patterns & Practices (PnP) Vesa Juvonen (@vesajuvonen) – one of my true Microsoft heroes for what he has done with the PnP set of tools – has come to the rescue, apparently with some help from Karine Bosch (@kboske). They have released a PowerShell script today that promises to “Generate [sic] list of sandbox solutions from SharePoint Online tenant“. (Far be it from me to correct Vesa’s Finglish!)

Generate list of sandbox solutions from SharePoint Online tenant

Generate list of sandbox solutions from SharePoint Online tenant

This script can be used to generate list of sandbox solutions in SharePoint Online tenant. You will need to use tenant administrator account to connect to SharePoint Online and script will generate a list of sandbox solutions to separate txt file, which can be imported to Excel for further analyses.

Note: This script is relatively simple and does not use multi-threading, so execution in larger tenants might take a while. We are looking for further enhancing the script with multi-threading support, if there’s demand for this. Also community contributions on this side are more than welcome.

Output file has following columns

  • URL of the site collection
  • Name of the sandbox solution
  • Author field from the sandbox solution – who uploaded the file
  • Created field from the sandbox solution – when solution was uploaded
  • Status field – 1=Activated, 0=Not activated

What seems to be missing here – at least to me – is the “and this one contains ‘code'” indication, but it’s still going to be very useful. Maybe it’s not simple to tell which solutions contain code? If you have ideas about this, it’s an open source project, so head over there and enhance it!

IMPORTANT: Please note that this script lists ALL sandbox solutions. But only code-based sandbox solutions have been deprecated and are being removed from Office 365. So don’t panic when you see all of your no-code solutions and site templates listed. This is a first step in inventorying your solutions.

The script requires – not surprisingly – that you have the SharePoint Online cmdlets installed. My bet is that there are plenty of Office 365 customers that have never really figured out how to download, install, and use PowerShell against Office 365. Many tenants are run by business users rather than technical types, as befits a powerful SaaS offering. In fact, in many cases, IT doesn’t need to be involved at all. That said, one would hope that those tenant administrators would know whether they have sandbox solution installed. However, see my mention of possible situations above, even if IT was in charge.

Here’s a quick tutorial on how to install those cmdlets in case you need it. In writing this section, I’m stealing the TechNet article Connect to Office 365 PowerShell. (I fear this article may not be available to everyone, as TechNet is part of subscriptions? I’m not really sure.) To do this, you have to be a tenant administrator. If you are, you’re probably the one wondering what you have in any case. It’s a pretty painless process, but if you haven’t used PowerShell – think batch files for servers – then it might be intimidating. I’m just copying the instructions from TechNet here, but I’ll add in some graphics and additional comments over the course of the day, so come back if you have questions. If you want to add any tips, please do so in the comments.

As Vesa always says: Sharing is Caring!

Step 1: Install required software

These steps are required once on your computer, not every time you connect. However, you’ll likely need to install newer versions of the software periodically.

  1. Install the 64-bit version of the Microsoft Online Services Sign-in Assistant: Microsoft Online Services Sign-in Assistant for IT Professionals RTW.

Microsoft Online Services Sign-In Assistant for IT Professionals RTW

2. Install the 64-bit version of the Windows Azure Active Directory Module for Windows PowerShell: Windows Azure Active Directory Module for Windows PowerShell (64-bit version).

Windows Azure Active Directory Module for Windows PowerShell (64-bit version)

Step 2: Open the Windows Azure Active Directory Module

  1. Find and open the Windows Azure Active Directory Module for Windows PowerShell by using one of the following methods based on your version of Windows:
    • Start menu   On the Start menu, enter Azure in the Search programs and files box.
    • No Start menu   Search for Azure using any of these methods:
      • On the Start screen, click an empty area, and type Azure.
      • On the desktop or the Start screen, press the Windows key+Q. In the Search charm, type Azure.
      • On the desktop or the Start screen, move your cursor to the upper-right corner, or swipe left from the right edge of the screen to show the charms. Select the Search charm, and enter Azure.
  2. In the results, select Windows Azure Active Directory Module for Windows PowerShell.
Here's what it looks like on my laptop running Windows 10

Here’s what it looks like on my laptop running Windows 10

Step 3: Connect to your Office 365 subscription

  1. In the Windows Azure Active Directory Module for Windows PowerShell, run the following command.

    In the Windows PowerShell Credential Request dialog box, type your Office 365 work or school account user name and password, and then click OK.

  2. Run the following command.

    Connect-MsolService -Credential $UserCredential - Success!

    Connect-MsolService -Credential $UserCredential – Success!

How do you know this worked?

After Step 3, if you don’t receive any errors, you connected successfully. A quick test is to run an Office 365 cmdlet—for example, Get-MsolUser—and see the results.

If the Get-MsolUser cmdlet runs successfully, you'll see a list of your users

If the Get-MsolUser cmdlet runs successfully, you’ll see a list of your users

If you receive errors, check the following requirements:

  • A common problem is an incorrect password. Run Step 3 again. and pay close attention to the user name and password you enter.
  • The Windows Azure Active Directory Module for Windows PowerShell requires that the Microsoft .NET Framework 3.5.x feature is enabled on your computer. It’s likely that your computer has a newer version installed (for example, 4 or 4.5.x), but backwards compatibility with older versions of the .NET Framework can be enabled or disabled. For more information, see the following topics:
  • Your version of the Windows Azure Active Directory Module for Windows PowerShell might be out of date. To check, run the following command in Office 365 PowerShell or the Windows Azure Active Directory Module for Windows PowerShell:

    If the version number returned is lower than the value 1.0.8070.2, uninstall the Windows Azure Active Directory Module for Windows PowerShell, and install the latest version from the link in Step 1.

  • If you receive a connection error, see this topic: “Connect-MsolService: Exception of type was thrown” error.

Item ‘Modified By’ Value Doesn’t Change: Fixing a Damaged Column Schema

I ran into an odd situation the other day at one of my clients.

Modified By ExampleWhen people edited existing list or library items in *some* lists (we couldn’t discern a pattern) the Modified By (Editor) column value did not change. The date/time stamp (Modified) was updated, but not the person who made the modification. We found several good posts out there on them Interwebz, but I though it would be useful to pull things together and outline what we tried in case it could help others.

There were two posts we found that got us started:

The first post led us to look at the Modified By column’s schema; the second led me to think that I could do the fixes using the SOAP Web Services.

I was able to pull the schema for the Modified By Site Column using the GetColumns operation in the Webs Web Service. (Of course I turned to SPServices first – it’s my hammer.)

$().SPServices({
  webURL: "/",
  operation: "GetColumns",
  completefunc: function(xData, Status) {
  }
});

I was able to look at the returned XML in Firebug, copy it out, and find the schema for the Modified By column.

Bingo. From Victor Butuza’s post, we know that the schema should look like this:

<Field ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}"
 ColName="tp_Editor"
 RowOrdinal="0"
 ReadOnly="TRUE"
 Type="User"
 List="UserInfo"
 Name="Editor"
 DisplayName="Modified By"
 SourceID="http://schemas.microsoft.com/sharepoint/v3"
 StaticName="Editor"
 FromBaseType="TRUE"/>

Instead, it looked like this (I’ve changed several of the attribute values to protect the guilty, but you should get the point):

<Field ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}"
 Name="Editor"
 SourceID="http://schemas.microsoft.com/sharepoint/v3"
 StaticName="Editor"
 Group="_Hidden"
 ColName="tp_Editor"
 RowOrdinal="0"
 Type="User"
 List="UserInfo"
 DisplayName="Modified By"
 SystemInstance="SQL_DB_Name"
 EntityNamespace="http://servername"
 EntityName="Partners"
 BdcField="OrganizationId"
 Profile=""
 HasActions="True"
 SecondaryFieldBdcNames="0"
 RelatedField="Partners_ID"
 SecondaryFieldWssNames="0"
 RelatedFieldBDCField=""
 RelatedFieldWssStaticName="Partners_ID"
 SecondaryFieldsWssStaticNames="0"
 AddFieldOption="AddFieldInternalNameHint"
 ReadOnly="TRUE"
 Version="1">
</Field>

Some way or other, the Modified By (Editor) column had been changed. We noticed quite a few odd things in the schema above, which I’ve highlighted. It looks like attributes from some BDC connection had made it into the schema, which wasn’t good. More importantly, the important FromBaseType="TRUE" attribute was missing.

I tried for a while to get the SOAP operation UpdateColumns to work in a test environment. (I was very careful to do this in a snapshot of a VM because messing with the Modified By column could be catastrophic.) Caution proved wise, as each time I tried to update the Modified By schema, the Site Column management pages (_layouts/mngfield.aspx and _layouts/fldedit.aspx) no longer worked properly. Even though I believe I had the syntax correct (based on Karthick‘s post above – note that the MSDN documentation is pretty sparse), I simply couldn’t get UpdateColumns to work without breaking things.

So I turned to Powershell. I’m no Powershell guy, but it’s a scripting language like any other and I found some good starting points out on the Web to get me going. What I ended up with was a script that repaired all of the attributes of the Modified By column in a single list.

$s = get-spsite http://servername
$w = $s.OpenWeb("/sitename/")
$l = $w.Lists["listname"]
$f = $l.Fields.GetFieldByInternalName("Editor")

write-host "BEFORE field at " $w.Url  " List "  $l.Title  "  is " $f.schemaxml

#add at the end of the schema the needed string and update the field and list
$f.SchemaXML = $f.SchemaXML -replace ' SystemInstance="SQL_DB_Name"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityNamespace="http://servername"',''
$f.SchemaXML = $f.SchemaXML -replace ' EntityName="Partners"',''
$f.SchemaXML = $f.SchemaXML -replace ' BdcField="OrganizationId"',''
$f.SchemaXML = $f.SchemaXML -replace ' Profile=""',''
$f.SchemaXML = $f.SchemaXML -replace ' HasActions="True"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldBdcNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedField="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldWssNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldBDCField=""',''
$f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldWssStaticName="Partners_ID"',''
$f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldsWssStaticNames="0"',''
$f.SchemaXML = $f.SchemaXML -replace ' AddFieldOption="AddFieldInternalNameHint"',''
$f.SchemaXML = $f.SchemaXML -replace '/>',' FromBaseType="TRUE" />'

$f.Update()
$l.Update()

write-host "FIXED field at " $w.Url  " List "  $l.Title  "  is " $f.schemaxml

$w.Dispose();
$s.Dispose();

I also wrote a Powershell script to find all of the lists where this aberration had occurred in the Modified By column:

$siteURL = "http://servername"
$site = Get-SPSite($siteURL)

$errors = 0
$thisWebNum = 0

foreach($web in $site.AllWebs) {

  $thisWebNum = $thisWebNum + 1
  write-host $thisWebNum " Web " $web.Url  " Created on "  $web.Created

  $listCounter = $web.Lists.Count

  for($i=0;$i -lt $listCounter;$i++) {

    $list = $web.Lists[$i]
    $thisListNum = $i + 1

    write-host "(" $thisListNum "/" $listCounter ") [" $list.Title "] Created on "  $list.Created

    $f = $list.Fields.GetFieldByInternalName("Editor")

    if ($f.SchemaXML -NotMatch 'FromBaseType="TRUE"')
    {
      $errors = $errors + 1
      write-host "  Issue in schema " $f.schemaxml
    }
  }
  $web.Dispose();
}
$site.Dispose();

write-host "TOTAL ISSUES: " $errors

This script told us that we had 283 lists with the issue. Clearly this has been going on for a long time and no one had caught it, though we still can’t see any patterns in the date/time stamps or which lists have the issue.

We’ve fixed the two lists where the client folks had noticed the Modified By issue as well as the Site Column itself. We’re pausing at this point just to make sure that we don’t see any oddities based on the fix, but we’re optimistic that we know what was happening and that we have a valid fix. After we let things settle for a little while, we’ll run the fix script on the rest of the lists with the issue by combining the two scripts to loop through all of the lists with the issue.

Have you ever run into a broken schema issue like this in your environment? If so, how did you fix it?

<UPDATE date=”2013-10-17″>

We hadn’t seen any issues since we’d applied the fix I described above, so today I ran through the rest of the messed up lists and applied the fix. The Powershell script below loops through all of the sites in the Site Collection, then through all of the lists in each site, and fixes the Editor column where it’s got issues.

$siteURL = "http://servername"
$site = Get-SPSite($siteURL)

$errors = 0
$thisWebNum = 0

foreach($web in $site.AllWebs) {

  $thisWebNum = $thisWebNum + 1

  $listCounter = $web.Lists.Count

  for($i=0;$i -lt $listCounter;$i++) {

    $list = $web.Lists[$i]
    $thisListNum = $i + 1

    $f = $list.Fields.GetFieldByInternalName("Editor")

    if ($f.SchemaXML -NotMatch 'FromBaseType="TRUE"')
    {
      $errors = $errors + 1

      # fix the schema and update the field and list
      $f.SchemaXML = $f.SchemaXML -replace ' SystemInstance="GivingData"',''
      $f.SchemaXML = $f.SchemaXML -replace ' EntityNamespace="http://servername"',''
      $f.SchemaXML = $f.SchemaXML -replace ' EntityName="Partners"',''
      $f.SchemaXML = $f.SchemaXML -replace ' BdcField="OrganizationId"',''
      $f.SchemaXML = $f.SchemaXML -replace ' Profile=""',''
      $f.SchemaXML = $f.SchemaXML -replace ' HasActions="True"',''
      $f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldBdcNames="0"',''
      $f.SchemaXML = $f.SchemaXML -replace ' RelatedField="Partners_ID"',''
      $f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldWssNames="0"',''
      $f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldBDCField=""',''
      $f.SchemaXML = $f.SchemaXML -replace ' RelatedFieldWssStaticName="Partners_ID"',''
      $f.SchemaXML = $f.SchemaXML -replace ' SecondaryFieldsWssStaticNames="0"',''
      $f.SchemaXML = $f.SchemaXML -replace ' AddFieldOption="AddFieldInternalNameHint"',''
      $f.SchemaXML = $f.SchemaXML -replace '/&gt;',' FromBaseType="TRUE" /&gt;'

      $f.Update()
      $list.Update()
      write-host "FIXED field at " $w.Url  " List "  $l.Title  "  is " $f.schemaxml
    }

    if ($errors -gt 0)
    {
      write-host $thisWebNum " Web " $web.Url  " Created on "  $web.Created  " had " $errors " errors"
    }
    $errors = 0;

    $web.Dispose();
  }
$site.Dispose();

</UPDATE>

Office365 SharePoint Online Portability Issues Strike Again

imageI had another situation today where the portability issues between Office365 and an on premises installation reared its ugly head. This is the reverse situation from the issues I had a while ago trying to move my demo sites from my VM to SharePoint Online.

Last week, I shared a WSP containing some of my demos with my friend Christian Ståhl (@cstahl) at the MVP Summit in Redmond. It was a WSP that I had saved from my SharePoint Online instantiation of my demo sites. When Christian tried to instantiate the solution in his on premises environment, he got the error:

clip_image001

The site template requires that the Feature
{232b3f94-9d6e-4ed6-8d55-04d5a44ac449} be installed in
the farm or site collection.

A little Binglage told me that the 232b3f94-9d6e-4ed6-8d55-04d5a44ac449 GUID represents the HelpCollectionRemoveDefault feature, which is specific to SharePoint Online. (Tip ‘o the beret to Rene Modery @Modery for that tidbit.) Unfortunately, there doesn’t seem to be any way to disable the feature if you actually need to, which might be a bad idea, anyway. That means that a WSP created in SharePoint Online can’t by hydrated on premises by a normal person. (AFAIK, it’s not possible to run PowerShell with SharePoint Online to deactivate the feature.)

The moral of the story seems to be that Office365 saved site templates (WSPs) can be instantiated in Office365 and on premises saved site templates can be instantiated in on premises installations and the twain can rarely meet, at least not easily. As I’ve said quite a few times before, getting this portability thing right is going to be absolutely imperative for a lot of people to want to use Office365, IMO. My guess is that many people may want to move their content to SharePoint Online and back again over time. This is just way too difficult given these feature mismatch issues.

Trials and Tribulations: Migrating My Demos Site to Office365 – Part Two

Office365

Office365Well, it took me a while, but I solve this problem. In my first post about it, I explained how I was having trouble figuring out what features installed in my local VM were causing the problems instantiating my demo site in Office365 – SharePoint Online.

I got stuck with the feature that has the GUID af6d9aec-7c38-4dda-997f-cc1ddbb87c92. When I wrote the post and tweeted about it, Chris Beckett (@sharepointbits) did some digging to find out what the feature was all about. It seemed that a forced deactivation was probably the right next step. (Check the comments on my original post for the details from Chris.) Then I got busy and didn’t get to it again until today.

Since I’m speaking at the first Office365 Saturday out in Redmond in a few weeks, I really needed to get my demos copied up into my Office365 site. What sort of talk would it be if I just waved my hands and said “Imagine that this demos is taking place in Office365?”

Today I finally got back to it. The first thing I did was look for some hints about how to work with features using PowerShell. I’ve done a little bit with PowerShell on and off, but not frequently enough to remember all the commands.

I found the perfect post from Corey Roth (@coreyroth) called Activating and Deactivating Features with PowerShell in SharePoint 2010. It had exactly the right examples to help me and it was written at exactly the right level (PowerShell newbie). Yeah, I know I should be loving PowerShell and doing all sorts of things with it every waking moment, but the way I work with SharePoint I rarely need it.

First, I wanted to see what the feature actually was, just to verify what Chris told me and to make sure that I wouldn’t be shooting myself in the foot by deactivating it. Easy as pie with Corey’s example:

What is af6d9aec-7c38-4dda-997f-cc1ddbb87c92

It was indeed something to do with Web Analytics Custom Reports, something which I don’t care about at all in my VM, so I was fine with getting rid of it.

On to the next PowerShell command to deactivate the feature:

Deactivating af6d9aec-7c38-4dda-997f-cc1ddbb87c92

That was pretty painless. I saved the site I wanted to move to Office365 as a template again and uploaded it to the Office365 Solution Gallery.

This time when I went to create a new site, it worked! Yippee!

I’m sticking with my original points on this, though. One shouldn’t need a Microsoft Certified Master on Microsoft SharePoint Server 2010‘s help (that would be Chris) to move a site from one environment to another. Clearly, this isn’t my forte in the SharePoint space, but I still say that the error messages should be clearer and also suggest a way to a resolution. And I still think that poor Sandie, my intrepid SharePoint administrator, would be stuck in this case unless she is very good at what she does. It shouldn’t be so opaque and difficult.

Enhanced by Zemanta