Connecting on-premise SharePoint to Exchange Online

My company is in the process of migrating our on-premise email system (Exchange 2003!) to BPOS/O365/Exchange Online/other acronyms here. Since we use a slightly non-standard email routing system with SharePoint, I'd been having some difficulties figuring out how all the pieces would come together to make this happen. O365 Support has been...well...there.

Before I get into the particulars, here's the basics of our configuration:

Web-frontend: PORTAL.company.com
Index/app server: INDEX.company.com

We currently receive mail at @company.com, which has been floated up to Office 365. Our email-enabled lists receive mail at @portal.company.com, and we create Contacts for those addresses in Exchange, but we don't publish them in the Global Address List. Instead, we create a Distribution Group and put the Contact in there, and then provide the Distribution Group to the users, so that if we need to change the address in SharePoint, we can do that somewhat painlessly.

PORTAL doesn't actually process the mail. As a performance measure (probably a very tiny one, but I digress), we actually process inbound mail on INDEX. PORTAL receives the mail, but a scheduled job on PORTAL shuttles the mail into the Drop folder on INDEX every minute, where the Microsoft SharePoint Foundation Incoming E-Mail service runs. Alerts and other notifications are also sent from INDEX.

Now that this is all out of the way, here's the situation. We don't publish an MX record for PORTAL.company.com - instead, our on-premises Exchange server would handle all mail for *.company.com and route PORTAL.company.com messages appropriately. This way, nobody from the outside world can spam our SharePoint farm directly.

In order to set this up, we had to set up a new inbound connector and outbound connector for SharePoint in FOPE (Forefront Online Protection for Exchange). If you're the SharePoint monkey and haven't really gotten into the depths of Office 365, you can access FOPE by logging into your O365 account, then in the top-bar (assuming you're an Office 365 Administrator), click Home, then Admin (Admin is not directly accessible from the mail window).

On the Admin Overview page, in the main content area under the header "Microsoft Office 365", look for the Exchange section, and click Manage. This opens up a new screen which is the Exchange Online administration screen. In the menu on the left, you should be on "Users & Groups". Click "Mail Control". After the page loads, on the far righthand side, you should see, under the header "Additional Security Settings" the link for FOPE. Click this link and you'll be in FOPE.

You may get a notification that your session has expired. This happens a lot. If so, *close Internet Explorer completely*. You'll need to log into Office 365 all over again, even if you just started your Office 365 session from scratch. It appears that FOPE tracks its sessions independently and doesn't clear them on exit, so when you load FOPE and it finds an expired session, it forces you to load all over again.

Anyway. Now you're in FOPE.

In the context of FOPE, "inbound connectors" mean mail inbound TO the server. So, from a SharePoint context, you'll configure an "inbound connector" for your outbound mail, and for your mail incoming to SharePoint, you'll configure an "outbound connector".

Let's do inbound first. A wrinkle, however.

In the past, SMTP on INDEX didn't need to authenticate itself to Exchange by virtue of already being inside the network. This is probably bad practice in a large organization, but since we're a small shop and only have  one Exchange server, it's easily monitored for nefarious activities.

Microsoft will obviously not be so trusting, so we need to configure a secure connection to allow for our server to deliver messages to Office 365. This actually requires us to add an additional user to Office 365 for the purposes of being the delivery proxy for SharePoint's email. So...one E1 license later, and I now have a username and password for use with the connector (I also configured this account to route all of its incoming mail to the SharePoint Administrators distribution group so that if anyone replies to an alert or such, we'll all see it).

On INDEX, IIS 6.0 Management, right-click on your SMTP server, select Properties. Go to the Delivery tab, and we'll be changing settings on all three of the buttons at the bottom.

First, Outbound Security.

If you aren't already using TLS, you will need to be now, so be sure to enable that. You'll also enter the credentials for your new Exchange Online user.

Next, Outbound Connections.

Make sure to change the TCP port since you'll now be using TLS. If you need to punch holes in facesfirewalls, now is the time to do so.

Last, Advanced Delivery.

The Smart Host field will have your client-specific smarthost. You'll probably recognize it with the "pod" prefix, but if that changes in the future, you can locate the smarthost information through the Office 365 page in the POP/IMAP/SMTP settings shown in the connection information.

 

Once you've made the SMTP changes, you'll also need to change SharePoint's mail settings to match.

Now that you've done things on the SharePoint side, time to set up FOPE. Note that FOPE doesn't have to be done last, I just saved it for last.

First, the Inbound Connector (remember - outbound from SharePoint!)

And then the Outbound Connector:

Once these connectors are configured, you will now need to Enforce them in FOPE. Enforcing the connectors means that the changes you've set up will take effect.

After you do this, test your mail setup, because you should be done!

On proper care and feeding of content databases

This one will be quick.

Good SharePoint administrators know not to let their databases autogrow. (Note that this is not the same thing as saying "turn off autogrowth"!)

Good SharePoint administrators also know not to grow their databases during regular hours. (The mysterious "w" at SQLTact does a fabulous job of explaining why.)

Presumably, good SharePoint administrators also know not to do large-scale emptying of the recycle bin during regular hours.

I suppose this means I'm not a good SharePoint administrator...because I didn't know about this until just now!

I moved a project site into its own site collection last week as part of a broader effort to try and partition our monolithic SharePoint environment. That went fairly well, and I deleted the original site, but in looking at my space reporting this morning, I see that egads-We only have 2.5 GB free in the root content database! It's growing even faster than I had planned for. I ask myself..."Did I forget to empty the second-stage recycle bin in order to dispose of the deleted site?"

Apparently I did. So, I proceed to clean out my contributions to the bitbucket. Now I see free space is ticking back up in the database, so that's good to see.

However...the DB appears to be locked during this operation, and so now our main site won't load anymore. Yikes! And I can't exactly stop it, lest I break something in the process of trying to get the site to respond again. Thankfully I know ahead of time that this content is about 10 GB, and I can tell when the deletions will finish based on that figure, so it's almost over.

Content approval for folders

Due to unfortunate necessity, I'm having to take our SP2010 branding apart bit-by-bit, redo the solution, and get dragged screaming and kicking into the 21st century of SP2010 development (IE - VS2010 on a server, no more STSDEV). I've certainly had my share of frustrations learning what everyone already knows here, but I discovered a new item that I'd only seen one other person mention anything about. I'm adding masterpages to the site's Master Page Gallery in the solution. Nothing big there. As someone with branding experience might already know, sometimes the masterpages won't be content-approved when you upload them, meaning that your event handler needs to be able to check them in on-the-fly. Nothing big there.

                using (SPSite site = (SPSite)properties.Feature.Parent)
                {
                    SPList masterPageGallery = site.GetCatalog(SPListTemplateType.MasterPageCatalog);
                    SPFolder masterPageTargetFolder = masterPageGallery.RootFolder.SubFolders[themeName];

                    foreach (SPFile file in masterPageTargetFolder.Files)
                    {
                        SPListItem listItem = file.Item;

                        if (!listItem.HasPublishedVersion)
                        {
                            listItem.File.CheckIn("Automatically added by installation feature.", SPCheckinType.MajorCheckIn);
                            listItem.File.Update();
                            listItem.File.Approve("Automatically added by installation feature.");
                            listItem.File.Update();
                        }
                    }
                }

Now, it turns out that since I'm putting the masterpages for this theme in their own folder, the folder also is subject to content type approval (not that this is relevant to the masterpages being accessible). So, let's say we want to be good citizens and have that folder automatically checked in when the code runs. Well, how do you do that? There's no .CheckIn() method on SPFolder or SPListItem...and if you think you'll be clever and try masterPageTargetFolder.Item.File.CheckIn(), the File member is null, so you'll find yourself quickly dashed. I found this post by Jay Noirfalise that got me part of the way there (using SPModerationInformation), but it didn't quite work.

                    SPList masterPageGallery = site.GetCatalog(SPListTemplateType.MasterPageCatalog);

                    SPFolder masterPageTargetFolder = masterPageGallery.RootFolder.SubFolders[themeName];
                    SPListItem masterPageTargetFolderItem = masterPageTargetFolder.Item;
                    SPModerationInformation moderationInformation = masterPageTargetFolderItem.ModerationInformation;
                    moderationInformation.Comment = "Automatically added by installation feature.";
                    moderationInformation.Status = SPModerationStatusType.Approved;
                    masterPageTargetFolderItem.Update();

For starters, it looked a little off to me. If I'm instantiating a SPModerationInformation object and then setting properties there, don't I need to save that object back to the SPListItem to effect the change? I tried to verify this in PowerShell and my suspicions were confirmed - nothing changed when I did this. Maybe it worked like that in WSS/MOSS? I dunno. Further, the SPListItem.ModerationInformation member is read-only. So, you just need to drill into the properties and set them inside the ModerationInformation member, like so:

                    SPList masterPageGallery = site.GetCatalog(SPListTemplateType.MasterPageCatalog);

                    SPFolder masterPageTargetFolder = masterPageGallery.RootFolder.SubFolders[themeName];
                    SPListItem masterPageTargetFolderItem = masterPageTargetFolder.Item;
                    masterPageTargetFolderItem.ModerationInformation.Comment = "Automatically added by installation feature.";
                    masterPageTargetFolderItem.ModerationInformation.Status = SPModerationStatusType.Approved;
                    masterPageTargetFolderItem.SystemUpdate(false);

But it still doesn't work. After running the matching code in PowerShell, my folder is still "Pending". You might notice one change I made which breaks this whole thing. It makes a little sense if you think about it - the approval's supposed to be done by a human, so we can't use SystemUpdate(false) to effect the change, because then there's no record of who did the change.

                    SPList masterPageGallery = site.GetCatalog(SPListTemplateType.MasterPageCatalog);

                    SPFolder masterPageTargetFolder = masterPageGallery.RootFolder.SubFolders[themeName];
                    SPListItem masterPageTargetFolderItem = masterPageTargetFolder.Item;
                    masterPageTargetFolderItem.ModerationInformation.Comment = "Automatically added by installation feature.";
                    masterPageTargetFolderItem.ModerationInformation.Status = SPModerationStatusType.Approved;
                    masterPageTargetFolderItem.Update(true);

Use Update() or SystemUpdate(true), and you're all set.

Adventures in SP2013

I've finally gotten around to setting up a demo VM of SharePoint 2013. Of course, it was necessitated by looking forward to see if we could utilize it to solve a problem a user is having now, so I haven't exactly gotten to dance around in the snowflakes and stare in awe and wonder at the freshness of it all... That said, I'm very much liking what I see so far.

I've had a couple of minor gotchas arise during the testing. For starters, I'm using CriticalPath's SP2013 VM setup guide (free registration required). It's been really good so far, though I got briefly thrown off track due mostly to a dumb mistype on my part and a red herring in the guide. When setting up your new users in Active Directory, the CriticalPath guide includes a PowerShell script and annotations in the document which state that it will handle SQL permissions.

This is not accurate. It's also not a problem, because the SP installer does it for you (provided the account you run the SP installer under has permissions to write to the database to begin with). Just provide the account/credentials for your would-be farm account, and SP will set all the privileges accordingly. That's nice.

When SP offers to create your first site collection, I was thrown off slightly by the fact that the installer defaults to the /sites/ managed path. I had assumed that the root site collection (eg - "/") was going to be created by default, and so after the configuration wizard completed and I couldn't visit http://mynewvm/ but http://mynewvm/sites/test worked, I was a little puzzled.

Part of my testing scenario involved setting up some metadata terms for working with refiners. It was easily done in the site settings for my test site, but then when I was nosing around in the Managed Metadata Service application, I couldn't seem to edit the term store at all. After a short time browsing the Internet unsuccessfully, the reason finally dawned on me - I didn't have permissions to edit the global term store because nobody is defined as a Term Store Administrator! I added myself here, saved the changes, and voila - I can now start creating termsets.

More later.

Sorting calculated fields in SharePoint

Some people might ask why I even maintain a blog if I don't write in it regularly or advertise it to followers. Of course, I don't have enough followers for anyone to even ask that - as of right now, I have one! If you're the guy following me, thanks!

Anyway, the main reason I do this is because I want to write about the weird cases - the things that happen in a 1-in-1000 chance that I'm unlikely to remember again (but inevitably have to revisit within a couple of years). The things that I spend more than 10 minutes on Google trying to even find something relevant to my problem, let alone a solution.

The things like I'm writing about today.

User has a list of tasks with due dates. Not all the due dates are populated. He's not worried about those tasks yet, he only wants to know about the ones that are actually due. Fair enough. How do we change the list sorting so that SharePoint puts the blanks in the back?

The normal solution would be to create a calculated field that references that date field and substitutes a sufficiently high/low value for the blank to make a surreptitious field that does what I want.

The only problem is - while it produces the calculated result I want, SharePoint still sorts however it feels like doing!

Thanks to this post by Wesley Bakker, I am reminded that regardless of the output type you select for the calculated field, SharePoint sorts before displaying the value (this makes sense if you think about it, since stuff like date and currency formatting is locale-sensitive and shouldn't impact the sorting order). So, how so we effect the sorting order?

To the existing formula, at the end, add "+ 0". This will work for any of the numeric-based output formats (number, currency, or date, since dates are stored internally as numeric offsets from an epoch anyway). So, a formula such as:

=IF(ISBLANK([Due Date]),"12/31/2099",[Due Date])

becomes

=IF(ISBLANK([Due Date]),"12/31/2099",[Due Date])+0

Dealing with orphaned user solutions

I'm surprised this problem isn't more common. It seems easy enough to trigger by accident, and fixing it was a puzzle.

No doubt most of us have at some point in using SharePoint 2010 created a site template, and then after gazing upon it in the Solution Gallery, determined that we've named it wrong, need to change one small thing, or any number of different issues. Normally, the correct thing to do in this circumstance is to deactivate and then delete the solution.

What if you don't deactivate it?

Create a site.
Save it as a template to the Solution Gallery.
Download the WSP to your desktop.
Delete the solution from the Solution Gallery *without* deactivating it. To do this, click the Edit button and then click Delete from there.
Upload the WSP back to the Solution Gallery.

You'll find that you're not able to activate the WSP you just uploaded, or deactivate it. And now you're just kinda stuck with a site template that's visible when you try to create a new site, but nothing that can be done about it!

So, first off, user solution WSPs still contain features. We *can* deactivate the feature that installs this site template (via PowerShell).

$site = Get-SPSite http://my.site.com
$site.Features | Where-Object {$_.FeatureDefinitionScope -eq "Site"}
Disable-SPFeature $feature -url http://my.site.com

But that's only hiding the problem, it's not actually fixing it. So, we should try to identify the user solution and remove it instead.

Uninstall-SPUserSolution -Identity "BasicProjectSite.wsp" -Site http://my.site.com

But this tells me that there is no solution with that name. But when I list $site.Solutions, it's there. Even running Get-SPUserSolution lists 0 solutions. What to do? Wait, does the object model still work?

Name                           SolutionId                           Status
----                           ----------                           ------
BasicProjectSite.wsp           af9fc924-26b1-4c4f-9250-57b26c81316f Activated

$solution = $site.Solutions[[guid]"af9fc924-26b1-4c4f-9250-57b26c81316f"]
$site.Solutions.Remove($solution)

And it's finally gone!

Odd issue with security trimming and global navigation

Just figured this one out.

So, right now, we use the SharePoint-managed nodemap in our layout, which is managed via /_layouts/AreaNavigationSettings.aspx and does automatic security trimming.

Recently, I'd noticed peculiar behavior in a couple of places. We would give a deep link to some resource to an external party, and when verifying the security on the new accounts we'd created for those individuals, most of the nodes in the tree would disappear...but not everything. The ones that remained were still not accessible to the user - clicking one would give Access Denied, but still, security trimming should remove these anyway, right?

The tricky bit is partially my fault.

Whenever I create a hyperlink target in SharePoint, I always strive to remove any unnecessary cruft from URLs. I strip off the hostname and the URI specifier, and if the target is a library or list, I remove the view name and just allow SharePoint to fall down to the default view, unless the target is supposed to be a particular list. So, when I have a link target of http://my.server.com/division/office/Lists/Contacts/AllContacts.aspx, I will usually replace that with /division/office/Lists/Contacts. Similarly, in a navigational node (where all the targets are generally subsites), I'd replace http://my.server.com/division/office with /division/office.

Usually.

Sometimes, I would inadvertently use /division/office/. That trailing slash would cause the security trimming to fail, but of course, the security is re-checked on pageload (thankfully!), so while I was violating security by accidentally exposing resources, I wasn't actually granting access to those resources. And in my current job, that's not a big big deal, but we're about to grant site access to a few companies who collaborate with us in some markets and compete in others...and one of the visible links was the name of a moderately high-profile project!