Follow me on Twitter @AntonioMaio2

Tuesday, December 29, 2015

SharePoint Basics: MailTo Link with New Lines in the Email Body

I was recently asked about how to implement a MailTo link on a SharePoint site page, which is pretty straight forward.  The challenge for the person asking was about how to pre-populate the email body, so that it provided a template for users to fill out when sending that email, and how to have that email body contain multiple lines of text, with carriage returns and new lines between the lines.

There are several methods of doing this which do not work because SharePoint will actually remove the carriage returns and new lines.  There are other methods which do work. 

So, here is a quick post describing one method for accomplishing this.  I'm going to use JavaScript to setup the link so that the script is centralized in one place.

When including carriage returns and new lines in a link, we need to escape those characters so that web browsers can interpret them correctly. 
  • The carriage return is escaped with %0D
  • The new line is escaped with %0A
  • If you want to have text start on one line, then move to the next line, you need to include: %0D%0A
  • If you want to have an empty line between two lines of text, you would simply double the pattern: %0D%0A%0D%0A

In order to create a MailTo link which includes a pre-populated email subject, email addresses and body pre-populated with multiple lines, you can do the following:
  • Create a JavaScript file which contains the following:
Click <a class="email" title="My Link Title" href="#" onclick="javascript:window.location=' Is My Subject Line&body=Here is the start of the email body %0D%0A%0D%0A email body continuing after a new line %0D%0A%0D%0A email body continuing after a second new line %0D%0A%0D%0A email body continuing after a third new line %0D%0A%0D%0A email body continuing after a forth new line %0D%0A%0D%0A email body continuing after a fifth new line.' + window.location;">here</a> to send an email template using a predefined template.

Notice how the link title field, email subject, email addresses and email body are populated.  Notice also how the carriage return and line feed characters are included in the email body.
  • Upload the JavaScript file to the Site Assets SharePoint library
  • On the site page add a Content Editor web part
  • Edit the web part you just added
  • In the Content Link property in the web part editor, add the path to the JavaScript file that you just uploaded to your Site Assets library.
Refresh your page and test your link.


Monday, October 12, 2015

Securing Office 365 with Activity Monitoring

Thanks to everyone that attended my webinar last week on Securing Office 365 with Activity Monitoring.  We had a great turn out and the slides presented can be found here:

Securing information systems is a very broad topic.  Monitoring and auditing these systems, and in particular the activities of users, is just one important aspect of securing our corporate IT environments. 

In July of this year, Microsoft announced some new capabilities around this within Office 365 – these are new Activity Monitoring and Reporting features.  These capabilities are designed to help organizations that are continually facing challenges with security, privacy and compliance.  In running and supporting the Office 365 service themselves, Microsoft has found that that they're capturing large amounts of data on which activities end users and administrators are performing.  They typically refer to this data as telemetry, and they've built great mechanisms into Office 365 to allow them to efficiently capture (and now share) this telemetry data.

These new capabilities provide greater visibility for administrators, and ultimately compliance and risk officers, into the actions taken by users on corporate content. They also allow us to apply greater access control over data, and if needed, they give us the capability to now investigate (at a very detailed level) user actions that might be against corporate or regulatory policies.

Why is Monitoring Activity and Auditing our Systems Important?

Monitoring user activity and auditing our information systems is important for many reasons.  

Regulatory Compliance

Regulatory compliance requirements are one key driver.  For example, many financial institutions often deal with MNPI, or Material Non-Public Information. Generally, this is information that’s not distributed to the public that an investor would likely consider important in making an investment decision.  Many institutions must put up Compliance walls to ensure that specific aspects of the business don’t communicate with each - this helps to avoid conflicts of interest and helps to ensure that they don’t inappropriately exchange MNPI.  

In particular, this is required in institutions which have both a corporate-advisory unit and a brokering unit, in order to separate those people giving corporate advice on takeovers from those advising clients about buying shares.  The wall is thrown up to prevent leaks of internal corporate information, which could influence the advice given to clients making investments

Detailed monitoring and auditing of user activity allows us to have a detailed view into which users are accessing sensitive content along who they’re sharing it with, and it provide assurances that our regulatory compliance obligations around in these business scenarios are being met.

Investigating Data Breaches
We've heard a lot about data breaches in recent years.  Data breaches can be small or they can be very large. They can be malicious or they can be accidental.   As well, data breaches can be caused by external actors like cyber criminals, or by insiders like system administrators or employees with broad levels of access.  Generally, we tend to see data breaches caused more often by external actors, but we see data breaches by insiders to involve larger quantities of data or more significant data. When data breaches do occur, it’s important for organizations to investigate and find the root cause so that they can both measure the scale of a breach (ie. how much data was leaked) but also to put in place measures to prevent these breaches in the future.  

When data breaches occur as a result of an insider threat, monitoring user activity at a detailed level allows us to perform investigations and root cause analysis to determine exactly who accessed data, when was it accessed and which actions were taken on that data - like who it was shared with.

Audit Access to Sensitive Information
In many organizations it’s important to audit the current access controls in place for sensitive content. This is sometimes referred to re-certifying permissions, or getting data owners to review and sign off that permissions are accurately set for data that they are responsible for.  In large organizations with large diverse information systems it can be really difficult to identify who is responsible for different data repositories.  

Monitoring user activity at a detailed level allows us to gain insight into who is accessing data on a regular basis, along with the level of access that they have.  This can greatly help us in identifying data owners to ultimately review and re-certify permissions.

Office 365 Activity Monitoring and Reporting

The new activity monitoring and reporting capabilities include:
  • Office 365 Activity Report (built into the Office 365 experience)
  • Comprehensive Event Logging
  • Search PowerShell Cmdlet
  • Management Activity API (in preview)

1. Office 365 Activity Report

You can access and run the Activity Report by:

  • Logging into your Office 365 tenant
  • Navigating to Admin in the App Launcher > Compliance Center > Reports > Office 365

[Activity Report Screen Shot]

You can use the Office 365 activity report to view detailed user and administrator activity in your tenant.  It contains data across SharePoint Online, One Drive for Business, Exchange Online and Azure Active Directory.  You can use this report to search and investigate user activities by searching for a user, a file or folder or even a site.  You can filter based on a date range or type of activity.  And within the report window you can view details of each activity in the Details Pane. The report is available to run on demand as needed.

When you find what you're looking for, you can either review activities and details right within this window or you can download the list of activities to a CSV file.

With each event captured there are up to 37 different properties logged.  Not all properties apply to all Office 365 services.  Some only apply to SharePoint Online and OneDrive for Business, whereas others only apply to Exchange.  The list of properties captured is shown here, with my favorites highlighted in red – my favorites are data like:

  • Actor - The user that performed the action; can be a service principle
  • ClientIP - The IP address of the device that was used when the activity was logged. The IP address can be either IPv4 or IPv6.
  • EventSource – Identifies that an event occurred in SharePoint, OneDrive for Business or the ObjectModel.
  • LogonType – Applies to Exchange only; this is the type of user who accessed an Exchange mailbox: mailbox owner, administrator, delegate, the Exchange Transport Service, a service account or a delegated administrator.
  • Subject – Applies to Exchange only; this is the subject line of the message that was accessed.
  • UserSharedWith – The user that a resource was shared with.
  • UserType - The type of user that performed the operation: a regular user, an administrator in your Office 365 tenant or a Microsoft data center administrator.

You can see documentation on the full list of properties here:
2. Comprehensive Event Logging
In order to enable the Activity Report and make it really useful, events related to user and administrator activities are logged as users work across SharePoint Online, One Drive for Business, Exchange Online and Azure Active Directory.  

Currently there are over 150 events that are logged, and these are divided into 9 categories:

  • Exchange admin events
  • Exchange mailbox events
  • File and folder events (SharePoint and OneDrive for Business)
  • Invitation and access request events (SharePoint and OneDrive for Business)
  • Sharing events (SharePoint and OneDrive for Business)
  • Site administration events (SharePoint and OneDrive for Business)
  • Synchronization events (SharePoint and OneDrive for Business)
  • Azure Active Directory events (Admin Activity and User Login)

You can view documentation on the full list of events here:

The events logged are diverse and very comprehensive, with Microsoft continually working to log more events.  When it comes to investigating data leaks, this gives administrators very detailed investigation capabilities to determine how leaks occur and how to prevent them.

3. Search Powershell Cmdlet

You can also search for events in the activity logs that we’ve been looking at using Powershell.  This is a new Powershell cmdlet to search all the event logs based on date range, the user who performed an action, the type of action, or the target object.

Examples of using this cmdlet are:

Search-UnifiedAuditLog -StartDate September 1, 2015 -EndDate September 30, 2015

Search-UnifiedAuditLog -StartDate 9/1/2015 -EndDate 9/30/2015 -RecordType SharePointFileOperation -Operations FileViewed -ObjectIds docx

With this capability we can script our searches of the event logs.  We can also have these searches output the results to a file.  And ultimately, this can allow us to schedule our reports to occur automatically on a regular basis so that administrators or infosec people can get insight into specific activities either every morning, every week or whenever the business schedule demands.

4. Management Activity API (in limited preview)
The final capability provided with this release is a new Management Activity API, which allows developers to integrate Office 365 activity and event data with either internal tools or with 3rd party monitoring and reporting solutions.

Full documentation on the Management Activity API can be found here:

There are a couple of important points about the API:

  • This API is in limited preview now, and during the preview anyone can use the API, but only those registered with Microsoft will be able to actually retrieve data from Office 365.
  • Actions and events are stored in content blobs in a database, and they are gathered across multiple servers and datacenters. As a result of this distributed collection process, the actions and events contained in the content blobs will not necessarily appear in the order in which they occurred. One content blob could contain actions and events that occurred prior to the actions and events contained in an earlier content blob.


Friday, October 9, 2015

Data Visualization Options in SharePoint and Office 365

A big thank you to everyone that attended my recent presentation last week in Houston on Data Visualization Options in SharePoint 2013 and Office 365.  We had a great turn out for our round table presentation with a lot of great dialog and questions.

You can view the presentation deck from our session here:

To summarize a few points from the session, Microsoft has several great data visualization tools available for SharePoint, including:

  • Excel Services
  • PowerPivot
  • SSRS
  • PowerView
  • PowerBI
  • Custom code with JavaScript
  • PowerBI
  • Datazen
  • Performance Point
  • Visio Services

Our presentation did not cover Performance Point or Visio Services due to the relatively low usage we see of those components.

Knowing which tool to use in which scenario can be really challenging.  So as part of the presentation we talked about the 3 questions you need to ask your self when choosing a tool, and we went through following use cases to help you decide the best tool for the job.  To recap that information...

The 3 questions to ask yourself when selecting a SharePoint data visualization tool:

  • What do you want to do with your data? Do you want to create reports, create dashboards, data analysis, data discovery?
  • Which devices are users consuming data on?  Are they using desktops, tablets, smart phones?
  • Where is your data located?  Is your data on premise or in the cloud?

The various use cases we went through were the following, with our recommended data visualization tool.

Use Case 1

I am an Excel pro.  I have a lot of data. I have SharePoint on-prem… and I need to provide and share info to many users on Intranet

Recommended tool: Power Pivot

Use Case 2

I have SharePoint on-prem.  I want users to do data analysis and discovery on the intranet (SharePoint 2010/2013) on their own.

Recommended tool: PowerView

Use Case 3

I have SharePoint on-prem, and need to provide reports to business users on my intranet  which they will print.  I would like power users to be able to create reports.

Recommended tool: SQL Server Reporting Services (SSRS)

Use Case 4

I have both Office 365 and SharePoint on-prem, do not have Power View, cannot use my on-prem data in the cloud, but still need to do some kind of Visualization.

Recommended tool: Javascript code using standard Javascript libraries (D3.js, chart.js)

Use Case 5

I am using Office 365, have my data on-prem and want users to be able to use different devices to do data discovery on the data.  I want the users to do create these “reports”.

Recommended tool: Power BI

Use Case 6

I have on-prem SharePoint, the data is in lists and need to create responsive dashboards that work on many different devices.  I want my users to create and consume these.

Recommended tool: Datazen

Please let me know if you have any questions.

Wednesday, October 7, 2015

How does Microsoft Protect Our Data in Office365?

I’ve received this question many times over the last year – clients who are considering Office 365 to store their corporate data asking:

How does Microsoft really protect our data as it sits within their data center?

Given the nature of my past security work, this is always a question that I’m happy to share the details about.  I often start by telling people that Microsoft has implemented an extremely robust, multi-layered security strategy for protecting data at rest in Office 365.  That sounds great, but what does that really mean?

Well, specific to SharePoint, OneDrive for Business and other solutions, Microsoft uses a multi-leveled encryption strategy with keys that are rotated (ie. regenerated) on a regular basis.  Actually the strategy is broader than that – it uses a combination of multiple levels of encryption, automatic key rotation, random distribution of data, drive level encryption and data spread across multiple systems each with their own network, OS, malware and physical protection.

In the on premise world, SharePoint data sits within content databases inside SQL Server.  You can certainly configure SSL communication between clients and SharePoint and between SharePoint and SQL to secure data in motion.  You can even enable Transparent Data Encryption (TDE) within SQL to secure your SharePoint data while at rest within the SQL Server database.  However, in the online world how exactly does Microsoft use encryption and other techniques to protect our corporate information?

Let’s look at how the strategy is applied in detail to your content within SharePoint and OneDrive for Business:

  • Files within SharePoint Online and OneDrive for Business are shredded into fragments and each fragment is encrypted with a different key, using AES 256 bit crypto.  When files are modified, each delta is encrypted with a different key.
  • Encrypted fragments are randomly distributed and stored across multiple Azure storage accounts.  These storage accounts are generated on demand and stored in separate systems.
  • The keys used to encrypt fragments are regenerated once per day (key rotation).
  • These keys are themselves encrypted using a master key that is specific to the customer.
  • The master key is stored in a highly secured and monitored “key store” which is completely separate from SharePoint content databases.  The key store is the most secured asset in the Microsoft data center.
  • The keys used to encrypt fragments, which are themselves encrypted with the master key, are stored in the SharePoint & OneDrive content databases along with a map to the fragments.
  • Microsoft also uses BitLocker to encrypt all of the disks on all systems.

So let’s consider scenarios where the data center is attacked:

  • If a content database is attacked, the attacker only gets access to a bunch of keys, which are encrypted and therefore unusable, and a map to the encrypted chunks that are stored in a different system with its own protections (the Azure storage accounts).  
  • If the Azure Storage Accounts are attacked, the attacker only gets access to a bunch of random fragments, which are encrypted… and did I mention that the distribution of those fragments is random.  Even if they could decrypt the fragments, they will not be able to put a file back together due to the random distribution.
  • Again, the key store is the most secure asset in the Microsoft data center.  Even if an attacker could get to the key store and attack it, at most they can get a key.  
  • If the physical environment is attacked, and drives are physically removed and stolen, none of the data on the disk will be accessible due to BitLocker drive encryption.

Keep in mind all of the physical, network level, malware protections and internal procedures which strictly limit access to internal employees in the data center which are also in place.  In addition, Microsoft works every day to improve the security of their Office 365 offering by attacking and defending their own environments:

  • They have a dedicated RED team, which sits outside of the Office 365 environment whose job it is to constantly attack the Office 365 environment looking for vulnerabilities and holes.
  • They have a dedicated BLUE team which sites within the Office 365 environment whose job it is to constantly defend the Office 365 environment looking for ways to better protect our data from would be attackers.

Don’t those sound like the coolest jobs in the world?!

In The Future…

The next thing that Microsoft is working on to further enhance this strategy is to allow customers to bring their own master key, so that even if an insider wanted to access your data or a government request is made to Microsoft to access your data, Microsoft will not be able to retrieve it themselves.

You can find a great video on this topic here:

As well, there was a great session at the Microsoft Ignite conference on this topic here:


Friday, October 2, 2015

UPDATED: Changing the SharePoint 2013 Farm Administrator Password

I wrote this post earlier this year regarding how to change the SharePoint 2013 farm admin password, and today I found an interesting situation that required a couple of extra steps.  You can find these extra steps below in red.  Big thanks to Doug Hemminger (@DougHemminger) for his assistance in finding a solution.

When setting up a new SharePoint 2013 farm, as a best practice we typically create service accounts for very specific purposes. The idea here is that we deploy SharePoint 2013 using a least privileged model, where very specific service accounts are created for very specific purposes and those accounts are only granted the permissions required to fulfill that purpose. That way, if such a service account is compromised by a malicious user, that user does not gain access to the entire farm. One such account is the SharePoint 2013 farm account.

When creating these service accounts, for various reasons, we typically create a domain account in Active Directory and configure it such that the passwords do not expire. As well, we find that the passwords for these service accounts typically are not changed often. However, there are circumstances in which the password for the SharePoint 2013 farm account must be changed.
  • One example of such a circumstance is if we suspect that the farm account has been compromised by a malicious user.
  • Another example is when consultants, such as myself, are brought in to deploy new SharePoint 2013 environments. Once that deployment process is complete and the client is happy with the environment, rightfully so, the client typically wants to take complete control of the environment and restrict farm admin level access to only a small set of internal employees - essentially they want to prevent the consultants that deployed the environment from continuing to have farm administrative level access.

Changing the SharePoint 2013 farm account is a manual process.  Its not something that is done often, so people often aren't sure which steps are required to ensure that it has been changed in all required locations.  Always be sure to test this process in a TEST SharePoint 2013 environment and monitor that environment for a period of time before performing this process in a PRODUCTION environment.  Your SharePoint 2013 farm may be configured differently that other standard configurations and your process may require extra steps.

For a standard SharePoint 2013 farm, the following are the steps required for modifying the SharePoint 2013 farm account:

1. Navigate to SharePoint 2013 Central Administration interface, click Security in the left hand menu, and click ‘Configure Managed Accounts’.  Select the farm administrators account in the account list shown, click the Edit icon and change the password.

I recently found that if the Central Administration application is running on a server in your farm which is not hosting the distributed cache service, this step will fail.  Luckily it fails early in the process and you get a "Sorry Something Went Wrong" message with a correlation ID.  If you look into the ULS logs you'll find that there is an Unexpected Error with the Distributed cache saying the SPDistributedCacheServiceInstance is not valid.  To resolve this, you can do the following:

  • Launch a SharePoint Command Shell window as an administrator
  • Run the following PowerShell command to temporarily enable the Distributed Cache service on this server:  Add-SPDistributedCacheServiceInstance.  The command should take a few seconds to run and completely successfully without any feedback on the command prompt.  Once the process is complete below, you'll remove the Distributed Cache service from this server.
  • Repeat step 1.  This step should now complete successfully.  Leave the SharePoint management console running.
  • You may find that this step stops the User Profile Synchronization Service on the server on which it is running.  At this point, check whether this service is still running in the 'Manage Services on Server' page and if not, start it now on the same server on which it was previously running.

2. Manually change the User Profile Synchronization Service password.  As required by SharePoint, this service uses the farm administrator account, however SharePoint 2013 does not treat this account as a managed account so it must be changed manually.
  • The farm administrator account must be made a local administrator on the server hosting the user profile service during the password change. 
  • Once that step is complete, launch SharePoint Central Admin, navigate to System Settings and click ‘Manage Services on Server’.  This page is used to start and stop services on each machine in the farm.  Select the machine hosting the user profile service and find that service.  It should say started. 
  • Stop the service.
  • Start the service again – when starting the script you’ll be asked for the new password
  • Ensure that you monitor the user profile service and ensure that the service starts correctly.
  • Once started, you may remove the farm administrator account as a local administrator.  However, we often recommend leaving it as a local admin on the server for simplicity of making such changes in the future.

3. Check if any applications in the Secure Store service use the farm administrator account, and if they do change the password there.
  • Launch SharePoint Central Admin, click Application Management in the left hand menu, click Manage Service Applications, click the Secure Store Application and click Manage Target Applications.
  • Select a single Target Application from the list.
  • In the Credentials group on the ribbon, click Set. This opens the Set Credentials for Secure Store Target Application dialog box.  If any target application uses the farm administrators account, change the password here. 
  • Repeat this process for all secure store applications.
  • Note: Be cautious when entering the password. If a password is entered incorrectly, no message will be displayed about the error. Instead, you'll be able to continue with configuration. However, errors can occur later, when you attempt to access data through the BCS.  If the password for the external data source is updated, you have to return to this page to manually update the password credentials.

At this point, if you added the Distributed Cache service as part of step 1, now we should remove this service.  In the SharePoint management console we previously opened running the following command:  Remove-SPDistributedCacheServiceInstance.

4. Reboot all the servers in the SharePoint farm, except for SQL server.  SQL Server does not need to be restarted.

Please let me know if you have any questions or comments about this process.  There may be other services that have been configured with the farm administration account, so your process may vary somewhat, but typically the farm administrator account is reserved for specific purposes.  As a best practice, due to its high level of access, the farm administrator account should not be used widely other than for the purposes in which it was designed.


Thursday, September 17, 2015

Security Controls - External Sharing of Sites in Office 365

In our SharePoint deployments in the past, we've often had the need to share content with external users, which are people that are not part of our company or organization.  Setting this up in an on premise environment often required a significant deployment of a SharePoint extranet, with some complex network configuration.

Office 365 makes sharing with external users very easy and gives administrators some great controls over it to ensure that its both secure and enables collaboration between internal and external users.

External Users
External users might be partners, customers, auditors, or generally those that cannot login to our corporate network.  From a technical standpoint we often think of them as people that do NOT have an account in our corporate Active Directory.  In Office 365, you can also think of an external user as one which does not have a license to your SharePoint Online sites or Office 365 subscription.

  • From a governance and security standpoint, we typically do not want to give external users an AD account or Office 365 license, so this makes sense.

If a SharePoint Online site is shared with an external user, that user inherits the rights of the SharePoint Online customer that's inviting them to access the site.  So, if you have an E3 Enterprise Plan with Office 365, any external users that you share a site with will also have the rights that are granted with an E3 Enterprise license.  That said, there are some capabilities that are NOT available to external users, which are:
  • Cannot be a site collection administrator or access any of the site collection administrator capabilities.
  • Cannot create their own personal One Drive for Business library.
  • Cannot search against everything, cannot search across site collections and cannot access the Search Center.
  • Cannot create their own personal sites, or a My Sites site.
  • Cannot access or view the company newsfeed.
  • Cannot change their user profile (things like their picture or contact information).
  • Cannot access site mailboxes
  • Cannot access PowerBI capabilities: PowerView, PowerPivot
  • Cannot use eDiscovery
  • Cannot open downloaded documents which are IRM protected (Information Rights Management).
  • Cannot use Excel Services features
  • Cannot access SharePoint Online data connection libraries
  • Cannot use Visio Services features
There are of course many other capabilities that external users can access, including viewing, editing and collaborating on content.  As described below, you can control the permission levels assigned to external users so that they can only view content, or so they can view and edit content.

We typically talk about 2 types of external users:
  • Authenticated User - this user is required to login with a user name and password
  • Anonymous User - this user is NOT required to login

Administering and Controlling External Sharing

Global Admin Controls
External sharing can be turned on and off globally, and individually for each site collection.  You turn it on or off globally by logging into your Office 365 tenant as a tenant administrator and doing the following:

  • Click the App Launcher and select Admin
  • In the menu at the left click the External Sharing option
  • Within External Sharing menu, click the Sharing Overview option
  • There are several options available on this page, including options for Sites, Calendars, Skype for Business and Integrated Apps.  We're focus on Sites for this article.

  • Under sites, you'll see a global ON and OFF switch for External Sharing - ensure that it is ON.
  • Click on the Go to detailed settings for sites link or in the left menu click on Sites

  • From this page, through the controls at the top, you can control the External Sharing settings for all site collections in the tenant.  The Let external people access your sites check box reflects the same state as the global ON and OFF switch mentioned above.
  • Selecting either the No anonymous guest links. Only allow sharing with authenticated users or Allow sharing with anonymous guest links for your sites and documents radio button will select whether only authenticated user access is permitted for external users, or if both authenticated users and anonymous users are permitted.
Security Note: This control allows me to turn ON and OFF anonymous access to all site collections (and leave authenticated access ON) all at once if needed.  In a situation where you detect that sensitive content may have been shared in appropriately through an anonymous link, this control can be extremely useful as it disables all previously shared anonymous access.  If I turn this off in the global Admin Center then it will be disabled (and I cannot turn it back on) in the SharePoint Admin Center:

Note: Each site collection remembers its previous setting, so if you have some site collections with only authenticated access and others with both authenticated and anonymous access, changing this radio button will change all site collections back and forth between their current settings or only allowing authenticated access.  

  • From this page you can select individual site collections, click the pencil icon to edit their settings and determine if external user access is permitted and if only authenticated users or if both authenticated and anonymous users are permitted.  Again, this can be done individually for each site collection here.

  • In the global Administration Center, you may also control the external users which have access to each site collection by selecting the site collection and clicking the Manage external users for this site link on the right, which will allow you to simply select and delete users:

SharePoint Admin Controls
From within the SharePoint Admin Center you may also manage sharing by selecting a site collection from your list::

...and then clicking the Sharing button in the ribbon:

However, these controls provide the same capabilities as those listed above at the global administration level.  If I modify the settings here in the SharePoint Admin Center for a site collection, the same change is reflected in the global Admin Center.

One advantage of the SharePoint Admin Center's sharing controls is that it allows me to edit the settings for more than 1 site collection at a time - I can select more than one, click the Sharing button in the ribbon and modify the sharing settings for all the selected site collections at one time:

Other than this, I can control external sharing settings for each site collection either at the global level or at the site collection admin level.  There are 2 advantages to controlling these settings at the global level:

  • View and delete the external users that sites are currently shared with
  • Turn sharing ON and OFF, or anonymous access ON and OFF for all site collections at once

See below for the administrative role needed to control external sharing.

Defaults for New Site Collections and Administrative Roles
The default External User settings for SharePoint Online is to have External Sharing turned ON at the global level.

However, external user sharing is turned OFF for new site collections - it must be explicitly turned ON for each site collection.  This is a good security feature, helping to ensure that you do NOT accidentally share sites externally.

In order to control External Sharing options, you must have either the Office 365 global administrator role or a SharePoint Administrator role.

  • You cannot control whether a site is share-able externally simply as the site collection administrator or from within the site settings.  Once you configure a site collection to be share-able in the global Admin Center and/or SharePoint Admin Center, then you select which external users a site is shared with from within the site collection, as discussed below.
Security Note: From the Office 365 Administrative interface it appears that a global administrator role would have the ability to control the global and SharePoint administrative settings for external sharing, whereas the SharePoint administrator role would only be able to control the SharePoint Admin Center settings for external sharing.  However, in testing I have found that this is NOT the case.  It appears that even if a user that has ONLY the SharePoint Administrator role they can control these particular settings in both the global Admin Center and the SharePoint Admin Center.  So, although some settings can be disabled at the global admin level and enforced automatically on all site collections, like turning anonymous access OFF on all site collections at once, a SharePoint administrative role can simply navigate to the global Admin Center and turn them back ON (even though they are not a global administrator).

Even if my user account is the site collection administrator on only 1 site collection, if I have the SharePoint administrator role in the Office 365 tenant settings, I can change external sharing settings for all site collections, both in the global Admin Center and the SharePoint Admin Center.

Sharing a Site with Authenticated Users
If you require that external users login with a username and password when accessing an Office 365 site that's been shared with them, then you must share with an authenticated user.

An authenticated user in this case must be a user with either a Microsoft account or an account assigned to them from Office 365, or what is sometimes referred to in Microsoft articles as a school or work account.

  • A Microsoft account is what used to be called a Windows Live ID, and can actually be any account used to access, OneDrive, Windows Phone, or Xbox LIVE.  If you have an account to access any of these services, then you already have a Microsoft account.  For example, my Microsoft account is still my account.  So, if I want to share a site with an external user that has a Microsoft account named then I can simply share the site with that email address.  The user will receive an email invitation and when they click the link within, they'll need to login using their existing username and password.  Office 365 will authenticate them against their Microsoft account.
  • A school or work account in this context is a user account that has been created for users within the Office 365 tenant.  For example, when creating my Office 365 tenant I can optionally register and verify other domain names that I own.  Once that step is complete, I can create accounts within Office 365 which use that domain name.  So, if I register and validate the domain CONTOSO.COM in my Office 365 tenant, I can then create an account called  In this case, if I need to share a site with an external user that does not already have a Microsoft account (and is not interested in getting one, even though they're free and really easy to register) I can simply create an account for them in my tenant.  I can then send that user an email invitation to access their new account with the username and password that I choose for them.  Finally, I can share a site with that user account and they'll be able to access the site even though they do not have an Office 365 license.  Please see below for some interesting controls (or lack of) about this scenario.

Sharing a Site
You may share a site with external users at the site collection level only.  So, if you share a top level site collection external users will gain access to all subsites, lists and libraries below that as well.  this is accomplished by clicking the Share button in the top right corner of the Office 365 page.

The following window will then appear where you can enter either the email address to their Microsoft account or the user name from the Office 365 tenant which represents the user's account.

Notice on the left side of the window you can also see who the site is currently shared with by clicking none other than Shared with.

If I access this same Share button from a subsite, I can share the site collection with an external user from there as well, but notice the message which appears at the top of the new window letting us know that sharing the subsite will also give the external user access to the site collection.

Once I enter the user name or email address and an email invitation message for the external user, they will receive an email with a link to the site. When they click that link, they will be taken to a page that allows them to choose how they wish to login, where they can choose either an organizational account or their Microsoft account:

Security Note: When you share a site collection with an external authenticated user and you select to give them the ability to view and edit content, that user is made a member the Members group within the site collection.  This means they have Contribute rights on the entire site collection.  It also means that they are now able to share the site collection with other authenticated users.  That can be a useful feature for them, and it can pose security risks.  

  • Fortunately, if they try to share a site with another external user, they will be prevented from doing so and the following error will appear:

  • If they try to share with other internal authenticated users, they are permitted to do this, but a request is first sent to the site owners group for approval, before those internal users are actually given access.

Its typically recommended that if you're going to share sites in this way that you create a separate site collection within your Office 365 tenant specifically for sharing with external users and you ensure that no sensitive content is placed within that site collection.

Sharing a Document
You may also share individual documents with external users, however I do find the experience for the user is not ideal.  When a user receives a sharing invitation to a document, clicking the link in the sharing email invitation received simply opens the document in the web browser.  The user does not get to navigate through SharePoint site to access the document.  Once again, to do this you must select the document and then click Share as shown in the following;

You can also share a document by clicking the ... on a document and selecting Share from the window that appears.  Once Share in either location is clicked, the following window will then appear:

Notice the extra options available in the dialog: 
  • Require sign-in option
  • Get a Link option
The Require sign in option does just that, requires a user to sign in when they access a document through a shared link.

See below for more information on the Get a link option.

Sharing a Site with Anonymous Users
Sites can only be shared with authenticated users.  They cannot be shared anonymously with external users.

Only documents can be shared anonymously with external users.  Once anonymous access is enabled for a site collection, as shown above, sharing a document anonymously is accomplished through the process shown above to share a document.

Once in the Share window, click the Get a link option and the following dialog will appear:

Clicking CREATE LINK either within the View Only section or the Edit section will then display links that you can email to users in order to enable them to access this document without having to login:

If you wish to only enable external users to view a document without logging in, then you may only email them the link under View Only.  If you wish to enable external users to view and edit a document without logging in, then email them the link under Edit.  A few other capabilities within this window are:

  • In this window, you can also click the Mobile phone icon beside each link, which will give you a QR code that you can send to users enabling them to easily open this document on the mobile phone.
  • Finally, you can also disable links to specific documents within this window through the disable link.

Security Note:  Its important to understand that by sharing documents anonymously through links like this, any external user that has the link can access the document.  So, if an external user inadvertently or maliciously forwards an email with such a link to another external user, that user will also be able to view or edit the document without having to login.  As a result, it is important to limit the external users with which documents are shared, and to ensure that these anonymous links to get reviewed on a regular basis and disabled once external users no longer have a need to access documents.  This should be part of your information governance policies.

Overall, sharing sites and information with external users is much simpler in Office 365 than its ever been, and Microsoft has provided some great security controls around this capability to ensure that sharing occurs in a secure and controlled way.


Thursday, September 10, 2015

Office 365 Alternatives to SharePoint Mail Enabled Libraries

I get this question a lot from clients: how can I use mail enabled libraries in Office 365?  As you know, mail enabled libraries are not supported in Office 365.  Microsoft’s reasoning behind it is the following:

Mail-enabled lists create contact objects in AD. Since SharePoint Online is a multi-tenant environment, this functionality would cause a large increase in traffic, which in turn would cause performance issues for all customers.  This functionality is currently disabled due to the performance concerns, as well as security, data requirement, legal compliance and scalability concerns.

Never say never, however due to the nature of mail enabled libraries, as described in this Microsoft message, I suspect that they are not scheduled to be supported in Office 365 for a very long time.  As such, I have been looking at alternatives and the following are 2 alternatives that I would recommend are worth considering.

Site Mailboxes
A site mailbox is a central email account that is accessed from a SharePoint site. A team may choose to use a site mailbox to gather relevant team email conversations or collaborate on composing an important email message. A team may also find it helpful to share important documents securely by using a site mailbox.  Once a site mailbox is set up for a site, a new email account is created that uses the name of the site. For example, if you have a team site that uses the URL The email address for that site mailbox will be  You can of course email or CC that address in order to have emailed stored within the Site Mailbox, as you could with mail enabled libraries. Everyone who has Contribute permissions to your site will be able to open the site mailbox and view those messages. Then, for example, a few months from now when another team member is trying to recall what information went into a particular decision, that team member can open the site mailbox, search through the mail captured in that account, and see the history of the issue.

When storing a team’s documents on a SharePoint site, you can leverage the Site Mailbox app to share those documents with those who have site access.  You can view a site mailbox in Outlook, and when doing so users will see a list of all the documents in that site’s document libraries. Site mailboxes will display the same list of documents to all users, so some users may see documents they do not have access to open.  If you’re using Exchange, your documents can also appear in an Outlook folder, which makes it easy to forward documents to others.

The following are a couple of good articles about site mailboxes:

All this said, there have been some issues found with site mailboxes (see here for more info) so Microsoft has introduced an alternative to site mailboxes in the last year called Office 365 Groups which I'll talk about further down.

From a licensing perspective, your Office 365 plan must include SharePoint Online and Exchange Online. Site mailboxes require that users have both SharePoint and Exchange licenses.  The site mailboxe feature is available across all Office 365 licenses.

If emails within a site mailbox must be secured, it’s important to note that Azure Rights Management (RMS) is not included but it can be purchased as a separate add-on in order to enable the supported IRM features within Site Mailboxes. Office 365 Message Encryption depends on Azure RMS.

Office 365 Groups
An Office 365 Group is a relatively new capability of Office 365 introduced over the last year.  It is a shared workspace for email, conversations, files, and calendar events where group members can quickly collaborate.  Microsoft has placed a lot of focus on making collaboration in Groups very quick and easy.

  • Users can subscribe to a group to receive group email, conversations and events in your email inbox, either in Outlook or in Outlook Web Access.  Subscribing is not enabled by default.  It can be enabled when creating a group, or on an already existing group when adding a new member.  As well, each member of a group can subscribe or unsubscribe from a group depending on their needs.
  • A group contains a shared calendar, allowing group members to manage events and schedules for group members.  This is an Outlook/Exchange calendar; it’s not a SharePoint calendar.  Groups has built in some really good integration between the Group calendar and your personal Outlook calendar, so that you can easily add events that are on the group calendar to your personal calendar.  
  • A group includes a shared OneNote notebook.
  • A group contains a OneDrive for Business page  which allows users to easily store and access documents in 1 central location that are relevant to group members.
  • A group also integrates a Yammer conversation feed for the group members.

A group can be public or private. Public groups are open to everyone. If you just want to see what the group is doing, all the content and conversations of a public group are viewable.  If you wish to collaborate with a public group, you can join it and become a member. A private group is exclusive and open to its members only. The content and conversations are secure and not viewable by everyone. Teams choose a private group when concerned about security and privacy, such as confidential documents. Everyone can see the name of a private group, but information within the group is security-trimmed so it is not accessible from search, links, or in other ways if you are not a member of the group. Joining a private group requires approval from a group administrator.

Through the OneDrive for Business capabilities, you can share a file or folder with people outside your group and even outside your organization, like customers, partners, or clients. One goal of Office 365 Groups is to strike a balance between collaboration and making sure files are not shared inappropriately. Administrators can require that access requests are sent before granting permissions, which helps to control the sharing within an organization, and enable/disable external sharing.

The following videos provide a great introduction and deep dive to Office 365 Groups:

You can also learn more about Office 365 groups here:

From a licensing perspective, at time of launch Office 365 Groups were rolled out to all customers that have an Exchange Online or Office 365 commercial subscription. Eligible Office 365 plans include the Office 365 Enterprise E1–E4 subscription plans (including the corresponding A2–A4 and G1–G4 plans for Academic and Government customers, respectively), Office 365 Business Essentials and Business Premium plans, Office 365 Small Business, Small Business Premium and Midsize Business plans and Office 365 Kiosk plan.


Monday, August 31, 2015

After Azure ADConnect - Activating Office 365 Users Through PowerShell

Many enterprises are looking at hybrid scenarios as part of their journey to a cloud based infrastructure, and one of the base scenarios or requirements for a hybrid cloud deployment is the synchronization of corporate identities (user accounts) from an on premise Active Directory (AD) environment to Office 365.  I've written several articles on this topic, and I often talk about 2 critical steps in the process:

  • Preparing on premise AD by cleaning up user account properties (before synchronization)
  • Activating synchronized user accounts in Azure AD (after synchronization)

Both operations can be performed manually in your on premise AD administration console, and in the Azure AD administration console in Office 365.  However, when you're dealing with a moderate to large number of users its often in practical to use the administration console GUIs for either step.

Activating Office 365 Users Through PowerShell

In this post I'll talk about how you can use PowerShell to activate Office 365 users once they're synchronized to Azure AD.

When synchronizing users with Azure ADConnect, the server hosting ADConnect will automatically have Windows Azure Active Directory Module for Windows PowerShell installed as part of that deployment, which is the PowerShell module you'll be using.  You can run the following PowerShell commands on that server.  Alternatively you can download and install the following 2 components:

  • Microsoft Online Services Sign-In Assistant (download here)
  • Windows Azure Active Directory Module for Windows PowerShell (download here)

We'll begin by connecting to your Office 365 tenant.

Connecting to Office 365

  • Launch Windows Azure Active Directory Module for Windows PowerShell.  Ensure you launch it as an Administrator.
  • Connect to your Office 365 tenant by using Connect-MsolService.  This command does not take any parameters.
  • A dialog will popup asking you for your service administrator username and password.  Enter them and click OK.  Once successfully connected, your PowerShell window will look like the following:
  • To view the list of available PowerShell commands with this module type Get-Command -Module MSOnline.

Get a List of Office 365 Users

  • To retrieve a list of Office 365 users you can use the command Get-MsolUser.  This will display a list of all users in your Office 365 tenant, including their User Principal Name, Display Name and whether or not they have a license.  Notice how both licensed and unlicensed users are shown in the following list:

  • If you only wish to see a list of unlicensed users then you can call the same command with a parameter for unlicensed users only:  Get-MsolUser -UnlicensedUsersOnly.

  • If you are working with a large number of users, consider using the -MaxResults parameter along with the -UnlicensedUsersOnly parameter.  For example, you can call: Get-Msoluser -UnlicensedUsersOnly -MaxResults 1000.  If -MaxResults is not specified, a default value of 500 is used.

Activating Office 365 Users

Before you can activate Office 365 users, we must first set the location of each user.  Microsoft requires this because the services it can offer to users is based on their location.

  •  The 2 character country code is used to set a location for each user.  So for Canada you use "CA" and for the United States you  use "US".  Other applicable country codes can be found here: two letter ISO code list.  You can set the location for an Office 365 user by calling: Set-MsolUser -UserPrincipalName "<user's upn>" -UsageLocation "US"

Here we specify the user by specifying their UPN by using the -UserPrincipalName parameter.
  • Once you have set a location for each user, you'll now require the name of your license SKU.  You can find this information by calling Get-MsolAccountSku.  This will return a string that's typically named <domain name>:ENTERPRISEPACK as in the following example:

Notice, the number of active units (available licenses), warning units and consumed units (assigned licenses) are displayed.  The number of licenses available to you will be Active - Warning - Consumed.  So in my case I have 19 licenses available that I can assign.

  • To assign a license to a specific user use the following PowerShell command: Set-MsolUserLicense -UserPrincipalName "<user's upn>" -AddLicenses "<your license SKU>".  After running this command and then running Get-MsolUser again we can see that our user now has a license, as in the following example:

Combine PowerShell Commands to Activate Users in Bulk

We can combine the PowerShell commands shown in order to assign a location and license to users in bulk, as in the following examples:

  • Get-MsolUser -UnlicensedUsersOnly | Set-MsolUser -UsageLocation "US"
  • Get-MsolUser -UnlicensedUsersOnly | Set-MsolUserLicense -AddLicenses "<your license sku>"

Azure ADConnect provides a fantastic tool for synchronizing users from on premise Active Directory to Office 365 and keeping them synchronized.  However, activating users is still a critical step in enabling users to access Office 365 services, and when activating users in bulk using PowerShell will save considerable time over using the administration console GUI.


Monday, August 24, 2015

DFW SharePoint User Group: Hybrid Identity Management in SharePoint and Office 365

Thank you to everyone that came out to the Dallas Fort Worth SharePoint User Group meeting last week on Tuesday August 18th.  We had a great crowd and I hope everyone enjoyed my presentation on Hybrid Identity Management in SharePoint and Office 365.

The presentation deck can be found here on slide share here:

As mentioned during the presentation, my slide deck does include an Appendix with screenshots of everything I went through.

Demo: User PowersShell to Active Office 365 Users

For those that attended, there was one glitch with my demo when trying to use PowerShell to apply a license to a newly synchronized user in my Office 365 tenant.  The issue with that I was trying to do was that I accidentally typed the PowerShell command Set-MsolUser rather than Set-MsolUserLicense.  This had been correct in the slide deck, and I just mistyped the command.  In any case, the full correct PowerShell command is:

Set-MsolUserLicense -UserPrincipalName " <user’s upn> " -AddLicenses “<your license SKU“

Customizing the User Sign In Page

There was also another correction made in the slide deck: in the table on slide 9 which describes various requirements along with which are supported with Synchronized Identity vs Federated Identity, it previously mentioned that customizing the sign in page was only available with the Federated Identity model.  Earlier this year however Microsoft released the capability to customize the sign in page in Office 365, so this will now work with both Synchronized Identity and Federated Identity models. This capability is only available with the Azure AD Basic or Premium editions, and not the free edition.  So you must pay for Azure AD in order to make this customization.  This fact was pointed out to me by Chris Goosen (Office 365 MVP who attended the meeting) - Thanks Chris!  He's even written a great blog post about how to go about customizing it here:

Please reach out to me if you have any other questions on this topic.


Friday, August 21, 2015

Initializing Azure VMs to Host SQL Server for SharePoint 2013

This is another post designed to help people with some basic steps involved in setting up Azure VMs for a SharePoint farm in a lab or test environment.  As mentioned in a recent post, I often use Azure VMs to test scenarios related to SharePoint 2013. When standing up a brand new SharePoint environment, I might setup a new VM specifically to host my SQL database.  When doing so, I'll often quickly create a VM using a SQL Server template in the Azure VM Gallery.

As part of this process, Azure will ask me to specify a network (which I already have configured in my Azure instance) and an administrative user account.  I cannot seem to specify a domain account here since the new server will not yet be domain joined.  The administrative user account I do specify is created as a local administrator in my new VM and given ownership over the SQL database which is deployed as part of the template.

Once created, my next step is to domain join this new VM to my domain.  You can see basic steps for how to accomplish that in a previous post here:  Domain Joining New Azure VMs.

At this point, you would think that I could just start installing my SharePoint 2013 Server on a separate VM (using service accounts), and as part of that installation process specify the administrative account I created for SQL as the SharePoint Database Access account (allowing my new SharePoint farm to connect to this newly setup SQL Server VM).  But, there are a few settings you need to configure first related to SQL before you can start setting up SharePoint 2013.

After my SQL Server VM was created, and even after I domain joined it, the only account that I specified which currently has ownership of the database was the local administrator account that was created as part of the VM creation process.  I cannot and should not use that account to setup SharePoint and connect to the database.  I can login to the SQL Server VM as a domain account, but it would not have any administrative capabilities over the database, nor could it connect to the database from a separate server (like the one you're installing SharePoint on).  There is no domain account yet that has that type of access to the database.

Configuring a Domain Account as SQL Sys Admin

Typically the first domain account that you provide access to SQL Server would not be used to install SharePoint. When configuring this domain account to access SQL Server, you often give it the sysadmin server role so that it can have ownership over all databases and be used to perform any operation in SQL Server, including configuring other accounts with appropriate permissions in SQL Server with which to install/configure SharePoint.  This is done by doing the following:

  • Login to the SQL Server VM as the local administrator account that was specified when you created the VM.  
  • Launch SQL Management Studio and connect as that local administrative account.  If you look in the top node of the Object Explorer in this screen shot, you can see that I am logged in as ALMAIOSQL-1\Antonio.Maio which is the local administrator account.

  • In the Object Explorer, open the Security node, then right click on the Login node and select New Login...

  • In the Login - New window, click the Search... button to specify the domain account that you wish to use as SQL administrative account.
  • Specify the domain account, click Check Names, and click OK.

Pretty basic stuff so far!

  • In the Login-New window click the Securables page (top right corner of the page), click Search..., select the The server <SQL Server Name> option, and click OK.
  • Click the Server Roles page, check the sysadmin option.  Click OK.

This account may now be used to create and grant appropriate permissions to other accounts which will be used to install and configure SharePoint 2013 on another server.  Before you can do this, you'll need to logout of the VM, and login again as the domain account you just configured.

Allowing the Database Access Account to Connect to SQL Server

In order to install SharePoint 2013 and specify a Database Access Account, the following is a process that you'll need to go through to give a domain account access to the database.
  • Login to the SQL Server VM as the domain account which you gave sysadmin access to the database. 
  • Launch SQL Management Studio and connect as that domain account. 
  •  In the Object Explorer, open the Security node, then right click on the Login node and select New Login...

    • In the Login - New window, click the Search... button to specify the domain account that you wish to use as SharePoint's Database Access Account.
    • Specify the domain account, click Check Names, and click OK.

  • In the Permissions for <SQL Server Name> list check Grant option beside the Connect SQL permission and click OK.

This account may now be specified when running the SharePoint 2013 install process as the Database Access Account.

There are other service accounts that are also required when installing a SharePoint 2013 farm (setup account, farm account) which have their own requirements for permissions, privileges and being part of local administrator groups.  Its important to understand these requirements when creating a new SharePoint farm and there are some great resources available from Microsoft on the specific requirements of each account:  Account Permissions and Security Settings in SharePoint 2013.