Control OneDrive for Business syncing to prevent data copies on non-domain-joined PCs

One of the recently announced changes to Office 365 is the ability to better control OneDrive for Business. Specifically, it’s now possible to control OneDrive for Business syncing to prevent data from being copied to non-domain-joined PCs, based on a list of approved domains, as well as to change the storage limit for users (perhaps 1TB is just too much data and something more restrained might reduce the impact on your network). There are also some changes around the “Shared with Everyone” folder, which used to be created by default but isn’t anymore.

The full details are in an Office Mechanics video, linked from a Microsoft blog post but I recently had the chance to try them out for real.

Step 1 was to determine the ObjectGuid for each of the domains in my customer’s Active Directory Forest, using Active Directory PowerShell:

$domains = (Get-ADForest).Domains; foreach($d in $domains) {Get-ADDomain -identity $d | Select ObjectGuid}

Step 2 is to connect to Office 365 using PowerShell:

$cred=Get-Credential
connect-sposervice –url https://<em>tenantname</em>-admin.sharepoint.com/ –credential $cred

Step 3 is to take the ObjectGuid from step 1 and use the Set-SPOTenantSyncClientRestriction cmdlet to restrict synchronisation:

Set-SPOTenantSyncClientRestriction -enable -DomainGuids "a0083dbb-e136-4f48-a048-2ec3a4c40cab"

It’s worth noting that, initially, this failed for me – SetSPOTenantSyncClientRestriction wasn’t a valid command in the version of the SharePoint Online Management Shell I had installed. I checked the version with Get-Module -ListAvailable | Format-List version, name and found I had version 15.0.4569.0 of Microsoft.Online.SharePoint.PowerShell. After updating to the latest version, I was at version 16.0.4316.0, which worked a treat:

TenantRestrictionEnabled AllowedDomainList

———————— —————–

True {a0083dbb-e136-4f48-a048-2ec3a4c40cab}

It’s important to understand how the restrictions are enforced though:

  • Not only will OneDrive for Business Sync client requests originating from a domain that is not on the safe recipients list be blocked but all OneDrive for Business Mac Sync client requests will be blocked. This also means that a sync relationship will not be established unless they are joined to an allowed domain.
  • However:
    • Mobile clients are not blocked (there are separate MDM controls for this) and any files that have been previously been synced to the computer will not be deleted.
    • New or existing files added to the client will still be uploaded to the server and will not be blocked.
    • OneDrive for Business sync client prior to version 15.0.4693.1000 will stop syncing existing libraries.

Controlling the storage quota was a little more tricky. I found that I could use Get-SPOSite -Identity https://tenantname-my.sharepoint.com/personal/firstname_lastname_tenantname_onmicrosoft_com to view the properties of a users’ OneDrive for Business site, but attempting to set the quota on the same site presented an error:

Set-SPOSite -Identity https://tenantname-my.sharepoint.com/personal/firstname_lastname_tenantname_onmicrosoft_com -StorageQuota 2048

Set-SPOSite : Cannot get site https://tenantname-my.sharepoint.com/personal/firstname_lastname_tenantname_onmicrosoft_com.
At line:1 char:1
+ Set-SPOSite -Identity
https://tenantname-my.sharepoint.com/personal/firstname_lastname …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Set-SPOSite], ServerException
+ FullyQualifiedErrorId : Microsoft.SharePoint.Client.ServerException,Microsoft.Online.SharePoint.PowerShell.SetSite

I haven’t fixed that yet, so I’ll be returning to the topic again soon, no-doubt…

The OneDrive that’s really two drives…

Jamie Thomson and I have long since lamented the challenges of Microsoft’s two directories for cloud services and it doesn’t stop there. Take a look at cloud storage:

  • OneDrive is Microsoft’s cloud-based storage offering, accessed with a Microsoft Account (formerly a Windows Live ID, or a Passport if you go back far enough…)
  • OneDrive for Business is Microsoft’s cloud-based storage offering, accessed with an Organizational Account (which lives in Microsoft Azure AD)

Similar names, similar purpose, totally different implementation – as the OneDrive for Business product is still Groove (which later became SharePoint Workspace) under the covers (have a look at the filename when you download the client).

And look what happens when you have both products with the same email address used to access them:

Still, at least the site detects that this has happened and gives you the choice. And there is some hope for future convergence as Jamie highlights in this blog post from earlier in the year.

Earlier this week, I was helping a customer to get ready for an Office 365 pilot and they were having challenges with the OneDrive client. The version available for download from the Office 365 portal is a click-to-run installation and it didn’t want to play nicely with their .MSI-based Office 2013 installation (which should already include the client anyway). Actually, that didn’t really matter because the OneDrive client is also included in Windows 8.1, which was the operating system being used.

The confusion came with setting up the connected services inside Office:

  • To set up a OneDrive account, click on OneDrive – but that will only accept Microsoft Account credentials and, after configuration it will show as something like “OneDrive – Personal”.
  • To set up OneDrive for Business, don’t click OneDrive but select SharePoint instead. After logging on with your Organizational Account credentials, that will be displayed as “OneDrive – organisation name” (with SharePoint sites appearing as “Sites – organisation name”).

Some illustration might help so, below is a shot of my connected services. Because I’m connected to multiple Office 365 tenants, you can see that I have multiple OneDrive [for Business] and Sites entries:

If you’re trying to get hold of the OneDrive for Business sync client for SharePoint 2013 and SharePoint Online, Microsoft knowledge base article 2903984 has the links for the click-to-run install.  If you want an MSI version, then you’re out of luck – but you can create a customised Office 2013 installation instead as OneDrive for Business (formerly SkyDrive Pro) was originally released as part of several Office 2013 suites (as described in Microsoft knowledge base article 2904296.

Finally, if you’re trying to work out how to get a OneDrive for Business app on Windows Phone, the OneDrive app can connect to both OneDrive and OneDrive for Business.

Confused?

Short takes: Symbols in Office applications and converting numbers to text in Excel

A few snippets I found on scraps of paper whilst sorting out my office this week…

Shortcuts to symbols in Office applications

Many people will be familiar with typing (c) to generate a © symbol in Microsoft Office applications but you can also use (R) or (TM) for trademark symbols ® and ™. One more that’s useful to know is (e) for the European currency symbol € (at least, it’s useful if your keyboard doesn’t recognise the Euro!).

Another useful code to know is the shortcut to create the symbol used to denote “therefore”, which is ? (and doesn’t appear in any dialogs I’ve seen to insert a symbol/special character). In Office applications running on a Windows PC, it’s possible to type ALT+8756 to generate the symbol.

I’ve tried these in Word and OneNote but see no reason why it shouldn’t work in other Office applications.  Unfortunately the functionality is limited to Office rather than part of the operating system – it doesn’t seem to work in a browser, or in NotePad for example.

Converting numerical data to text in Excel, or SharePoint, or something like that…

A few months ago I was creating a SharePoint list and wanted to display a unique ID for each entry but couldn’t use calculated values in the title column to base it on the actual ID for the list item (at least not when provisioning via the GUI). I can’t remember the exact circumstances but, looking back at my notes it appears I used the following formula in Excel to create a text version of a numerical cell:

=TEXT(A1,"0000")

I probably then uploaded that to SharePoint as a list and messed around with the columns displayed in a particular view… although it’s all a bit vague now. I no longer have access to the list I was working on, but it might jog my memory if I have to do something similar again…

Authentication issues with SharePoint in Windows Explorer mode resolved with browser proxy settings

Every now and again I get infuriated by our Microsoft Office SharePoint Server (2007) platform as it prompts for credentials (before failing to authenticate and repeating the process) when I go to open a document library in Windows Explorer mode.  Today I found the cause of that issue.

I’d been working at Microsoft’s offices yesterday and had disabled the proxy server settings in my browser.  After returning home and VPNing to our network, I was able to access both Internet and intranet resources as normal and I forgot about the proxy server change. Only when trying to work out why I was being asked for authentication as I tried to use SharePoint in Windows Explorer mode did I remember to turn it back on again – after which everything worked as it should.

It may be peculiar to our infrastructure, or it may be a wider issue that’s worth mentioning so, if you experience authentication issues when trying to open a SharePoint library in Windows Explorer mode, double-check your browser’s proxy server settings!

Some SharePoint tips/tricks for editing list forms and hiding standard page elements

Over the last couple of years, I’ve written a few SharePoint-based blog posts as I’ve hacked my way around the company’s infrastructure, trying to get it working the way I’d like it to (creating dashboards, adding the odd bit of workflow and custom columns to pre-populate list data, embedding video, etc.).  There’s a rumour that we might get SharePoint 2013 soon but, for now, all of my hacks/tweaks have been based on 2007.

In the last few weeks, one of the projects I’ve been involved in has taken a major step forward, engaging a real designer and a real web developer, instead of li’l ol’ me.  From my perspective, this has been great news, although there have been some times when I’ve questioned the maintainability of custom code used in situations that have caused problems in the past (e.g. to edit the default forms used for displaying list items).

As you might expect, I’ve also picked up a couple of tips, working alongside some experts – and I thought I’d make a quick note of them here, for future reference.

Editing list forms without using SharePoint Designer

First up, a handy little trick to edit list forms (like DispForm.aspx) without having to resort to using SharePoint Destroyer Designer, which is little more than Microsoft FrontPage (just about OK in it’s day but long past its sell-by date now). Just add ToolPaneView=2 into the query string, for example http://sharepoint.intranet.com/00001/Lists/ListName/DispForm.aspx?ToolPaneView=2.

Unfortunately, this doesn’t seem to work on my Office 365 SharePoint site, so it might be 2007-only but useful nevertheless. In our case, this was used to add a (hidden) Content Editor Web Part with some jQuery and CSS to style up the form.

Hiding page elements

Sometimes, there are standard elements of a page that you don’t want to display and, without access to the templates used on a corporate site, that might be difficult. As it happens, all that’s required is a little in-line CSS, included in a (hidden) Content Editor Web Part:

<style type="text/css">.ms-webpartpagedescription {display:none}</style>

Just use the Developer Tools in Internet Exploder (or similar in your choice of browser – although, if you’re using SharePoint 2007, it probably will be IE) to find the class of the element to be hidden. In this case, I removed some standard text that was inserted below the breadcrumb trail at the top of the page but I’ve also seen it used to hide the page title and it could be applied to other elements too.

Credits: Thanks to Steve Haxell for sharing these methods with me.

Embedding Windows Media in a SharePoint website

A few weeks ago, I found myself standing in front of a green screen in a meeting room that had been “converted” into a temporary film studio, recording a video for internal communication on the technology standardisation initiatives I’ve been running for the last few months.  After all the edits and final approvals, the videos are now coming online and, as “Chief SharePoint Officer” for our team (I jest), it was up to me to hack our portal and get them online.

I figured that the guys in our internal studio must have done this before and, sure enough, the advice I received was to use JWPlayer for Flash content or to embed a media player for Windows Media files. We went with Windows Media (I can play the WMVs offline too), so I used a method described on Stack Overflow to embed an object inside a SharePoint Content Editor Web Part.

I’m sure that there are alternatives that provide better cross-browser support but as this is a SharePoint 2007 website, the only browser that will be used is some variant of Internet Exploder (and our corporate browser is Internet Explorer 8) so not too much to worry about.

I needed just one slight variation.  The videos I used were 480×270 pixels so, with the controls, I needed the player to be slightly taller. Playing around until I had no black bars around the video got me to the following code:

<object id="mediaplayer" classid="clsid:22d6f312-b0f6-11d0-94ab-0080c74c7e95" codebase="http://activex.microsoft.com/activex/controls/mplayer/en/nsmp2inf.cab#version=5,1,52,701" standby="loading microsoft windows media player components..." type="application/x-oleobject" width="480" height="315">
<param name="filename" value="http://mystreamingvideoserver/myfile_480.wmv">
     <param name="animationatstart" value="true">
     <param name="transparentatstart" value="true">
     <param name="autostart" value="true">
     <param name="showcontrols" value="true">
     <param name="ShowStatusBar" value="false">
     <param name="windowlessvideo" value="true">
     <embed src="http://mystreamingvideoserver/myfile_480.wmv" autostart="true" showcontrols="true" showstatusbar="1" bgcolor="white" width="480" height="270">
</object>
<p><a href="http://mystreamingvideoserver/myfile.wmv">A full-quality download of this video</a> is also available.</p>

Internet Explorer crashes when editing lists in SharePoint datasheet view

Recently, I found I was experiencing issues when editing lists in SharePoint.  After switching to datasheet view, Internet Explorer displayed a message which said:

Internet Explorer has stopped working

Windows is checking for a solution to the problem…

then:

A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available.

IE then attempts to reload before presenting a page that gives more information:

It seems that Windows Data Execution Prevention (DEP) detected an add-on trying to use system memory incorrectly, so I took a look at the DEP settings but everything seemed in order.

Eventually I tracked the problem down to cells that contained HTML code (for a dashboard that I created in SharePoint), being mis-interpreted as a malicious exploit.  I created a new view, minus the columns containing HTML and was able to edit without any browser crashes.  It’s a bit of a nuisance but it seems to work…

Given the choice, I wouldn’t be using Internet Exploder anyway but, as this is SharePoint 2007, I don’t have a lot of choice (I understand that cross-browser support is a lot better in later SharePoint releases).

SharePoint datasheet mode crashes Internet Explorer

Back in the summer I wrote about creating dashboards in SharePoint using some borrowed JavaScript in a webpart to display calculate columns of HTML.  I needed to create another dashboard recently, so I reused my old technique but then, today, I found that I could no longer edit my list in SharePoint’s Datasheet mode.  Each time I tried, Internet Exploder crashed, blaming the problem on the Data Execution Prevention (DEP) functionality that is meant to prevent malicious code from being executed in memory.

Of course, being SharePoint (well, on 2007 at least), I couldn’t use an alternative browser but I was pretty sure the issue was related to the HTML generated an placed in a calculated column in my list. By creating a new view that excluded the problematic column (i.e. the one containing  the HTML), I was able to edit as normal, without a browser crash.

Export to spreadsheet from SharePoint 2007 with Excel 2010

I frequently have to export data from a SharePoint list to Excel but earlier today I found it no longer works since I’ve upgraded to Office 2010.  Our SharePoint infrastructure is based on SharePoint 2007 and each time I attempted to Export to Spreadsheet from the Actions menu on the Toolbar, Excel would hang.

Luckily, I found Gustaf Lindqvist’s post on synchronising data between Excel 2010 and a SharePoint 2007 list. He suggests installing the 2007 Office System Driver Data Connectivity Components (I wasn’t getting an error message to help me find the problem – just a “Not Responding” application and the “doughnut of death”.

It’s still a bit flaky – and I’m not sure I have the complete answer (in fact, I suspect there may even be issues with some of the views in SharePoint) – but at least I can export data now…

Setting a blank value in a SharePoint workflow

I spent far too much of yesterday fighting with SharePoint workflows… including trying to cancel some that have run away on a list with almost 400 items in it…

Some idiot (initials MW) created a workflow to auto-populate a column based when a record was created or updated. Then, thinking that was successful, he created another one to update a different column. He was so pleased with himself that he didn’t stop to think of the consequences of one workflow updating an item, which would then trigger another workflow because the item had been updated… the resulting event from which would then trigger another workflow… and so on…

The long and short of it, is that I found SharePoint 2007 doesn’t have the ability to bulk remove errant (or otherwise) workflow tasks. Not from the user interface, at least – and I didn’t want to do it, twice, for each one of 377 items (there weren’t quite that many runaway workflows but it was well into three figures). I found some code on a blog post from SharePoint MVP Brian Farnhill (@BrianFarnhill) to cancel a workflow programmatically but I’ll need to put the code  into an app and I’m not really sure how to do that (my limit is probably opening Visual Studio). Besides which, I figure that the administrators of our SharePoint platform, who have probably spent the last 3 weeks wondering why it was running out of memory (or running more processes, or something similar) won’t appreciate me running unsupported code against their servers – even if it is client side…

Although it would be gratifying to create the app, in the end I settled for removing the workflow from the list, which has the side effect of ending all running workflows (unless you select the “no new instances” option). Unfortunately I still have a bunch of items that generate an error message when I try to open them – goodness knows why.

I decided to fight fire with fire and given that I’m clearly some sort of SharePoint Destroyer Designer workflow deity (not), I created another workflow to nuke all the column settings I’d created with the first two workflows (since neutered).

The logic goes like this:

Condition: If columnname is not empty
Action: Set columnname to null

Unfortunately it’s not possible to leave a null/blank value when setting field values but, like so many things in SharePoint, there is a workaround. That workaround is to set a workflow initiation parameter (imaginatively, I called mine Blank) of type single line of text, with an empty default value.

Now, the workflow action can use the value Initiation: Blank (i.e. null) to clear the contents of a column.

Unfortunately, it still hasn’t fixed the problem with the items that will not open. And, in case my manager is reading this post (which I doubt), hopefully this now puts to bed the running joke whether it’s he or I that is our team’s “SharePoint guru”. Clearly my infrastructure background does not lend itself to being a “guru” in an application platform (as I have always maintained!)…