Office 365 Pricing for Nonprofits Released


… well, kind of. We’re still waiting on some Office 365 for Small Business Nonprofit pricing, but it shouldn’t be too long. Complete details can be found here. Here are the highlights (prices are in CAD):

  • Office 365 Small Business for Nonprofits – Price coming soon, limit 25 users (Retail $5.10/user/month annual subscription)
  • Office 365 Small Business Premium* for Nonprofits – Price coming soon, limit 25 users (Retail $13.25/user/month annual subscription)
  • Office 365 Enterprise E1 for Nonprofits – Donation, unlimited users (Retail $8.20/user/month annual subscription)
  • Office 365 Enterprise E3* for Nonprofits – $5.20/user/month, unlimited users (Retail $23.20/user/month annual subscription)

*Note: These plans include desktop installations of Microsoft Office 2013.

Not-for-profits / charities involved in the following activities are eligible (if you’re eligible for TechSoup, you should be eligible for this):

  • Providing relief to the poor
  • Advancing education
  • Improving social welfare
  • Preserving culture
  • Preserving or restoring the environment
  • Promoting human rights
  • Establishment of civil society

The usual organization are not eligible such as government, education, health care, etc.

Complete pricing and plan details can be found here.

Who has forwarding enabled in their Exchange Online mailbox??


How do I know, without looking at the properties of every Exchange Online mailbox, which users have setup mail forwarding on their mailbox?  That is a very good question.  If you Google around you’ll find lots of interesting answers, particularly around using LDAP queries to identify those mailboxes – but how do you do that in Exchange Online???  PowerShell is the answer!

First, here is how you connect to your Exchange Online tenant:

Second, run this command:

Get-Mailbox -Filter {ForwardingSmtpAddress -ne $null}

That will give you a list of all the users that have enabled (or have a not null) forwarding address configured.  Taking this one step further you can grab the user and the destination address by using this command:

Get-Mailbox -Filter {ForwardingSmtpAddress -ne $null} | foreach {$recipient = $_; $forwardingsmtp = (Get-Recipient $_.ForwardingAddress).PrimarySmtpAddress; Write-Host $recipient.Name, $_.ForwardingSmtpAddress }

Ultimately you’d probably want to put this in a script of it’s own an pipe the results to a text file for further analysis.

Lastly, don’t forget to disconnect your PowerShell session – remember, you can only have 3 open sessions to Exchange Online.

Creating a Site-to-Site Connection between Azure and pfSense


This was a big of a tricky endeavour and obviously a topic that I I don’t typically cover on this blog.  The whole reason for the post actually directly relates back to my Moving to Office 365 post as I haven’t get succeeded in moving enough of my operations to the cloud such that I am not dependent on my main internet connection any more.

I was able to find a few resources on this topic which were helpful with my initial configuration:

How you can connect an Azure cloud to a pfSense network over IPSec – Excellent how-to article to get you started!

After repeatedly not successfully establishing a connection between the two networks and only seeing ERROR: invalid flag 0x08 in my IPsec log I concluded that something had changed after the articles were written.  After lots of digging I found couple changes which were required:

1) The first thing I found in this article which indicated that the encryption algorithm had moved to AES 265 from AES 128.  Change made, still saw the same error.

2) The second obvious thing missing from the above article is after step 12 (Create Gateway).  Along with the Create Gateway function now, you have the choice of creating a Static Routing or Dynamic Routing Gateway.  Doing a bit more research I came across this (same as issue 1) article which recommends that you create a dynamic routing gateway.  Fair enough, it sounds like it would be the easiest for me to maintain.  WRONG! Scrolling further down that article, you find the ‘Key exchange’ property, on a static routing gateway it is IKE v1, on a dynamic routing gateway it is IKE v2.  What is the significance of this you ask? I refer you to this discussion on the pfSense form.  IKEv2 is not supported by racoon which is the foundation of the pfSense IPSec implementation.  A quick removal of my current Azure gateway and creation of a static routing gateway worked beautifully!  Connection established!



SharePoint Online – Content Viewing Audit Logs


Little important thing to note when evaluating SharePoint Online “Wave 15”: SharePoint Online does not have the components to enable View audit log reports.

What does this mean to me you ask… Well, within the View Auditing Reports screen, there is a link named ‘Content viewing’ (see below).

View Audit Reports menu

In full-instance of SharePoint, this would give you a report basically containing who clicked what and when.  In SharePoint Online you will always get “Report contains no data.” (see below for example).

Report contains no data.


For more information see: View audit log reports, under the ‘Events available for audit log reports’ section, ‘Opened and downloaded documents, viewed items in lists, or viewed item properties’ bullet.

Suggestion to Microsoft, hide that report in SharePoint Online. Having that link visible fairly misleading.



Moving to Office 365


With the full GA release of Office 365 Wave “15”, I thought it was about time I started to really see what I could do with this platform.  I have been an avid SkyDrive and user for my personal email for sometime now, so why not see what else I can do with the cloud & Office 365 with my little experimental company.  I should also mentioned that my little company is a Microsoft registered partner and I have enrolled in the Cloud Essentials program to make this endeavour a bit more cost effective.

My objectives for this experiment:

  1. Enable Office 365 for my company and federate authentication with my on-premise Active Directory
  2. Federate my on-premise Active Directory with Azure Active Directory
  3. Leverage Windows Intune to decommission my on-premise System Center deployment

My primary reason behind federating with Azure Active Directory for is really for the challenge – just to see if I can do it.  However, secondary to that is that I am normally working remotely and of course, I would not be very happy if my my company internet connection was down and I could not log into my Office 365 account.  I am aware that I could use the Access Control Services that come with Office 365 and DirSync, but realistically my company may want to authenticate more than just Office 365 against my on-premise Active Directory.

Here is a nice video that explains how this federation works.

Here is a little diagram of my current state:


And here is one of my end-state goal:


Thank you to Buck Woody for the very nice Azure Visio shapes!

I’ll be honest – I think this plan is going to work based on what I have read, but I really don’t know fore sure.  I will continue to update this post with my full experience as I plug away at this experiment.


Update 1 (March 6, 2013 8:55 AM MT):

Currently provisioning an new Windows Server 2012 VM using Hyper-V.  I will be adding  the Active Directory role to this server and joining it to my existing domain.  This server will be used to federate with Azure Active Directory for authentication.

Office 365 account is setup and running with my domain. Just waiting to finish with Active Directory before adding user accounts.


Update 2 (March 6, 2013 10:50 AM MT):

Server 2012 deployed with Active Directory and Active Directory Federated Services running.  Server is joined to my existing domain and has been promoted to a domain controller.  ADFS has been configured and after getting myself a trial SSL certificate, I have been able to add it to my Azure Active Directory service.  This part was surprisingly easy, just ran through the wizards that came with Server 2012 and it appears to be working.  Don’t forget that ADFS has to have port 443 open on your firewall.

Next steps: Prove that my Azure AD is working / provide authentication services and figure out how to connect it to Office 365.


Update 3 (March 6, 2013 1:45 PM MT):

There seems to be a very distinct difference between the ‘Active Directory’ service you can use via and the Active Directory that is found at  As far as I can tell, they are both based on the same under-lying service – ACS – but they both seem to offer very different interfaces.

Best I can figure right now, federation was not the correct route.  I should have gone down the DirectorySync (DirSync) route from the bigging.  Now to demote my newly promoted DC and turn it into a DirSync box.  More info here.

And a good article on demoting a Server 2012 Domain Controller.


Update 4 (March 6, 2013 3:20 PM MT):


Directory Sync is up and running… and syncing all my user accounts and service accounts.  Given that this is really an experimental Active Directory, there are a lot of service accounts.  DirSync really wasn’t too bad to get going.  Just took time reading through the guides and waiting for components to install.

Next tasks: Try to filter the user accounts that are sync’d via DirSync and take another crack at SSO.

One good thing to remember: DirSync cannot be on a Domain Control or server running ADFS.


Update 5 (March 6, 2013 9:10 PM MT):

After lots of research and testing, I have determined that because I signed up for Windows Intune, I am stuck on an Office 365 Wave 14 tenant for the time being.  Service request is open with Microsoft to see if I can do anything about this.  Haven’t found a way to force an upgrade yet either.

Still working on SSO.


Update 6 (March 6, 2013 10:10 PM MT):

A very helpful post from Sean Deuby seems to be debunking my theory about using Azure Active Directory as an authentication mechanism for my Office 365 tenant:

“If you’re running Office 365 with the federated identity + directory synchronization option, you’re already running a hybrid Active Directory where your user’s on-premises AD identity is authenticated to Office 365 via federation and their accounts are provisioned or de-provisioned in your own little cloud AD via the dirsync process.”

I may need to take a closer look at using an Azure VM if I want to achieve this type of authentication distribution as highlighted in this StackOverflow post.


Update 7 (March 11, 2013 7:30 PM MT):

Well, this sure is proving to be an adventure. After 5 days, numerous emails and phone conversations, the closest I am on getting my tenant either upgraded to Wave 15 from Wave 14 or just simply getting it deleted so I can associate a new tenant with my partner account is being told to contact the partner support group.  I did attempt that today. Tried giving them a call at 6:00 PM PT – the referral I got said that their hours were until 6:30 PM PT time – no luck.

Will update again soon.


Update 8 (March 12, 2013 10:10 AM MT):

Success! If you are registering as a Microsoft Partner and did not have a Wave 15 tenant – deal with partner support.  I had to end up giving up my original domain, but I also had nothing in my tenant so it didn’t really matter to me.  If you don’t want to give up your domain or you have content that you don’t want to lose, you have to wait for the upgrade email.

On to doing what I started!


Update 9 (May 17, 2013 12:30 PM MT):

Well, I have managed to get a Wave 15 tenant all set up (got busy of course and this little initiative has taken a bit of a backseat).  I have spend some time researching cloud authentication strategies and I *think* password sync with Azure Active Directory is possible, but only with Windows Server 2012 Essentials.  Here is my current evidence for this.  Hopefully I have more time in the coming weeks to to dig more into this.

On the flip side, I do have DirSync running and only synchronizing a subset of my user accounts (have lots of service accounts that certainly don’t need to be in Azure AD).  That was fairly easy to set up.  Haven’t gone for SSO yet due to the high risk of auth failures if my on-prem connection is down.  Going to take another look at the VPN options from Azure VMs as well.


Key Learnings:

  • If you’re going to integrate Office 365 with your on-premise environment, start here.
  • If using Azure Connect to an on-premise DC, be sure to populate the Azure VM’s IPv6 DNS address with your on-prem machines Azure Connect IPv6 address.


MySPC Calendar Syncing


Now that SharePoint Conference 2012 is just a couple days away, I am sure everyone is busy selecting all the sessions they are wanting to attend via MySPC.  As I was admiring my completed calendar, I began to wonder: How I am I going to get this on my phone.  I attempted to import it into my corporate Outlook but that was futile as my iTunes is not on my company laptop.  Back to the drawing board…  What about  Eureka!  I can add a public calendar to my!  *Update: Gmail instructions follow instructions.

Here’s how to do it:

  1. Log into your MySPC – probably easiest with IE.
  2. On your Calendar, click Export.
  3. In the little Do you want to allow… window, Copy the Address value to your clipboard.

    Calendar Export Image

    Calendar Export Image

  4. Log into your account and navigate to your calendars.
  5. Click on the Subscribe link.

    Subscribe Link Image

    Subscribe Link

  6. Complete the form:
    1. Make sure that ‘Subscribe to a public calendar is selected.
    2. Paste the Url from your clipboard in ‘Calendar URL’
    3. Give this calendar a name.  I used MySPC.
    4. Click ‘Subscribe to calendar’
  7. Now you should have your MySPC calendar connected to your To sync it with my phone, all I had to do was refresh my calendars associated to my account, but you may have to disconnect and reconnect your calendar sync.

A few things to make note of:

  • Pre-req is that you have your account connected to your phone.
  • Calender is read-only – just a one way sync is possible.
  • Calendar appears to only refresh every 24 hours in your  This could be problematic if you’re changing sessions a lot on the fly, but it’s still better than having to carry around a print out.

Gmail:  You must be using an Exchange profile on iPhone or ActiveSync on other devices.

To Sync with Gmail:

  1. Follow steps 1-3 from above.
  2. Log into your Gmail and open your calenders
  3. Under Other Calendars, click Add by URL
  4. Once the calendar is added, you can use the edit menu on it to change the name.
  5. On your device connected to Gmail by Exchange / ActiveSync, navigate to (cannot be on a computer).
  6. Select the device you want to configure.
  7. Under Shared Calendars, select the calendar you added in Step 3.
  8. Click Save.
  9. Now you should have your MySPC calendar connected to your Gmail. To sync it with your phone, try refreshing your calendars associated to your Gmail account, but you may have to disconnect and reconnect your calendar sync.

A few things to make note of:

  • Pre-req is that you have your Gmail account connected to your phone.
  • Calender is read-only – just a one way sync is possible.
  • Calendar appears to only refresh every 24 hours in your Gmail.  This could be problematic if you’re changing sessions a lot on the fly, but it’s still better than having to carry around a print out.

Happy SharePoint Conference Everyone!!

Useful SharePoint 2010 KB Articles


This will be a collection of articles or tools I find useful when it comes to potential deployment issues in a SharePoint 2010 environment. I’ll continually update it as I find new things!


  • SQL Database ‘<contentDBName>’ on SQL Server instance ‘<SQLServer>’ not found. Additional error information from SQL Server is included below. Cannot open database “<contentDBName>” requested by the login. The login failed. Login failed for user ‘<excelServicesAppProcessAccount>’.:

CalSPUG Meeting – November 17, 2011


Another month means another CalSPUG meeting! (Web site should be up soon… sorry for the dead link.)

This meeting should be a good one – and a non-developer topic as well. However… most developers should be interested in this!

Keep Your Portal Governance Simple!

Start Time
Doors open at 5:00pm. Presentation starts at 5:30pm.
*Note: Elevators up will lock at 6:00pm if you arrive late.

Dan McCleary (@DanMcCleary)

Many people seem to dread the word governance. Dread of the amount of work involved. Dread of not knowing how to proceed. Dread of unnecessary bureaucracy. Unfortunately, this hesitance is what kills a lot of good portal governance planning. Yet, if some basic elements are put in place, a model can be built that can be understood by all portal users and is relatively easy to maintain.

Speaker Bio
Dan McCleary has worked in the internet industry for over 15 years holding many positions including designer, developer, project manager and instructor. Between 2006 and 2010 Dan held the position of Consulting Director at Ideaca, one of Canada’s leading IT and Management Consulting companies, in charge of the consulting staff for their largest office. He has recently ventured back into the consulting world, focusing his efforts on portals, collaboration and social media. A goal of his management consulting approach is to provide clarity by striving for simplicity.

Food and drinks provided. SharePint location TBD.

Location & Registration

Controlling Database Growth in SharePoint 2007


One of the many problems when developing for SharePoint 2007 is database growth. This can be especially troublesome when your production environment has caching and auditing enabled. Here are some tricks to dealing with the growth.

  1. Disable Auditing when restoring your production environment in a development environment. This is highly important as the content database can very quickly grow far beyond the capacity of your development server. This does have to be done on a per site collection basis and can be found at _layouts/AuditSettings.aspx in each.
  2. Dump the tempdb. This is most easily achieved by restarting the offending SQL service in SQL Management Studio.
  3. Convert the offending database recover mode to Simple.
  4. Set up a detail maintenance plan and make sure that a Shrink Databases operation is in that plan.
  5. Trim the audit log. This is done with the trimauditlog stsadm command. An example for its use is “stsadm -o trimauditlog -date 20110930 -url http://locahost:8080” Depending on how out of control the growth is, you may have to run this in small date ranges and then restart SQL after to dump the tempdb (suggestion 2).
  6. Dump the eventcache history. This suggestion deals directly with the DB which is a no-no in the Microsoft SharePoint world, however this is a development server and if things go sideways a new restore is a very valid option. Here are some queries to help out:
  7. Use this to figure out which tables are the largest (in terns of number of rows):
  8. GO
    SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
    FROM sys.dm_db_partition_stats st
    WHERE index_id > 2
    ORDER BY st.row_count DESC
  9. Use this to figure out which event types in the eventcache table have the most occurrences:
  10. GO
    SELECT EventType, COUNT(*) as Total
    FROM [databasename].[dbo].[EventCache]
    GROUP BY EventType
  11. Use this to clear the offending event types from the eventcache table (source):
  12. GO
    While exists (SELECT TOP 1 * FROM eventcache where eventtype In(8192,8194,1048576) AND EventTime < DATEADD(day, -5, GETUTCDATE()))
    DELETE eventcache
    FROM (SELECT TOP 100000 * FROM eventcache where eventtype In(8192,8194,1048576) AND EventTime < DATEADD(day, -5, GETUTCDATE()) ) AS e1
    WHERE =
  13. Shrink the database files themselves. This can be done in SQL Management Stuido. Often it’s best to set the space just slightly above the suggestion Management Studio gives you.
  14. Create a batch file that restarts the offending SQL service nightly. This will help manage the growth of your tempdb.