Intro
A few weeks ago a request came through to create a group that would have full access to all OneDrive for Business accounts in our Office 365 Tenant. I’m am patently against blanket access to things, even for administrators. It turns out the goal was to enable our Service Desk staff to manage user’s OneDrive’s as we ramp up our adoption rate through various “to the cloud” projects in the works.
We have a very small team who have admin rights to our SharePoint Online and we are wary of granting frontline technicians admin rights to it as it is a complex beast and there is sensitive data to consider. Currently, all requests to access another user’s OneDrive requires an escalation to that small team. This creates a constraint that isn’t much of a problem today, but will become one as our adoption rate grows.
I identified two user stories we needed to support:
- IT Technicians needing temporary access to a user’s OneDrive to assist them with various tasks
- Users needing to access the OneDrive of another user permanently (e.g. a manager needing access to the OneDrive of an employee on Extended leave)
We already have automation built into our leaver process which grants managers access to their leaver subordinate’s OneDrive for 30 days. Unfortunately, that functionality is tightly coupled with the leaver process so it can’t really be used for these two user stories.
I am a big fan of SharePoint lists. They make it really easy to make a web based form and tracking mechanism that can support RBAC. I’m also real big on PowerShell automation. I have quite a bit of automation involving both in production. One thing I don’t like is that most of this automation is on a scheduled basis and not a trigger basis. I noticed that SharePoint Online now has triggers for Microsoft Flow and that Microsoft Flow added the ability to run Azure Automation Runbooks. So that’s where I decided to go.
This blog will cover the temporary admin access solution. It’s intended to be more of an overview and not a deep dive or tutorial. This is a PowerShell blog, but most of this post will be taken up by Flow as it is the glue of the solution. However, I won’t go into great detail of Flow either.
Primary Components
The main components are pretty simple:
- 3 SharePoint lists
- One for Permanent access requests
- One for Temporary admin access
- One with the blocked target users (e.g. the CEO and his direct reports)
- 2 Microsoft Flows
- One for Permanent access requests
- One for Temporary admin access
- 1 Azure Automation Account with 2 Azure Runbooks
- One Runbook to add permissions to a OneDrive
- One Runbook to remove permissions from a OneDrive
- 1 Azure AD Service Account with an Office 365 license, SharePoint Online administration role, and Automation operator access to the Azure Automation account
We have an E3 which means we get something like 2000 flows per month per user aggregated across our 5000 users. We also have a modest Azure Enterprise Subscription which is just running one regionally balanced static site for our PKI repository. Be mindful that if you are doing this yourself, you may have to make some purchase decisions or may incur costs.
Design
I’m only going to cover the Temporary grant flow in this post. There is quite a bit of duplication between the two processes and they only significantly differ in their Flow configurations. Since this is a PowerShell blog and not a Microsoft Flow blog, I don’t wish to go into the full Flow design for both user stories.
The Temporary grant flow allows IT technicians to request their admin accounts be granted admin access to a user’s OneDrive for 2 hours. Their request will be automatically approved as we care more about tracking this than keeping IT users from doing their jobs. Our IT users have 2 accounts, one is their normal user account associated with their email and OneDrive and another is their admin account used for privileged access such as managing Office 365. For the sake of demonstration, these accounts are prefixed with “admin_” and, since this is not a sanctioned company blog, I will be using “adatum.com” and “adatum.onmicrosoft.com” for the domain names.
The design goes something like this:
- IT User makes a new item on the SharePoint list indicating the UPN of their “admin_” account and the UPN for the target user
- The Flow is triggered by the new item creation
- The Flow verifies the target UPN is not in the blocked user list
- The Flow verifies that the requestor’s UPN begins with “admin_” and ends with “@adataum.com”
- The Flow starts the Azure automation Runbook to add the permissions
- The Flow waits for 2 hours
- The Flow starts the Azure Automation Runbook to remove the permissions
Throughout the process the SharePoint list is updated with the current status of the flow and the requesting user is emailed.
Azure AD Service Account
The first thing that we need is a “Service Account”. This is just an Azure AD user. For my environment, I opted for a cloud-only account as this account doesn’t need any on-prem AD rights. The account is named OneDriveAutomation@adatum.onmicrosoft.com and has been assigned a SharePoint Online, Mail, and Flow licenses from the E3 subscription. It has also been granted the SharePoint Administrator role. As with all service accounts, it has a max length, high entropy, and randomly generated password with a setting of never expire.
I use a separate service account for this “application” to make it easy to kill off if it starts doing something it’s not supposed to. It’s always a best practice to create service accounts with the least access it requires and to use separate service accounts for separate processes.
Additionally, the service account needs to be given full control access to the SharePoint list where the requests are made and Read access to the list where the protected users are stored. The SharePoint Administrator role does not automatically grant permission to the list so this does need to be done separately.
Finally, the service account needs to have Automation Operator access to the Azure Automation Account.
Azure Automation Account
I want to start at the end of the process because this is where the PowerShell lives and this is a PowerShell blog. The Automation account I created is called OneDriveAutomation. Since I don’t think this will have a very high level of usage, I chose the Free Tier option. There are 4 components required: Modules, Credentials and two run books.
Modules
The PowerShell scripts for this require two modules: SharePointPnPPowerShellOnline and Microsoft.Online.SharePoint.PowerShell. The SharePointPnPPowerShellOnline module can be installed from the PowerShell gallery. For the Microsoft.Online.SharePoint.PowerShell module, it will need to be installed locally on your test machine, then zipped and uploaded to Azure. There is a decent guide available here.
I use both modules because neither one does both of the primary actions required easily, but each does separately. The two things we need to accomplish is get a user’s OneDrive Site URL from their UPN and add a Site Collection Admin to that Site. The PnP module has a really convenient way to get the OneDrive URL, but no convenient way to add a site collection admin using the administrative grant (the way it its done through the tenant admin console where you don’t need to already have site collection admin rights, just SharePoint admin rights). The SharePoint Online module does have the ability to do the admin grant, but doesn’t have a very convenient way to get a user’s OneDrive URL. It can be done using CSOM against the Profile Manager, but that would require my other module which warps CSOM. Using just the 2 Microsoft modules is simple enough.
Credentials
The Runbooks will need credentials to access the SharePoint online environment. It might have been possible to do this with RunAs, but I don’t think the modules would work with it. Instead, I opted to use the stored credentials. In the OneDriveAutomation Azure Automation account under Credentials, I added a credential entry named OneDriveAutomation that contained the OneDriveAutomation@adatum.onmicrosoft.com service account user and password.
AddOneDriveSiteAdmin Runbook
The first of two Runbooks is used for adding a OneDrive Site Admin. Azure Automation Runbooks come in a few different flavors. Since the Runbooks in this application will be acting on a single item at a time, I chose the “PowerShell Runbook” option. If it was acting on multiple OneDrive’s at once I would consider using a “PowerShell Workflow” Runbook.
Runbooks are simple to grasp and use if you have written a PowerShell script or Advanced Function. They start with a Param block that lists the names, types, and validation of parameters the Runbook requires. Both Runbooks require two parameters: $Requestor and $TargetUser. The $Requestor is the user who will be granted or revoked access and the $TargetUser is the user to which whose OneDrive the requestor will be accessing.
The Get-AutomationPSCredential command is an azure Automation specific command which pulls in the OneDriveAutomation credential I discussed earlier. It pulls it in as a [PSCredential] object similar to how you can use Import-Clixml to import a [PSCredential] from an XML file created with Export-Clixml.
RemoveOneDriveSiteAdmin Runbook
This Runbook is responsible for removing the Requestors permissions from the $TargetUser’s OneDrive. The code is almost identical except for the final $Params parameter hash where IsSiteCollectionAdmin is set to $false instead of $true.
SharePoint Lists
Next, we need the 2 SharePoint lists: One for protected users and One for temporary admin access requests.
OneDrive Permissions Blocked
This list is the simplest part of the whole process. This list contains a single column. I chose to use the default Title column and renamed it “User UPN”. This column will contain the UserPrincipalName of the users for which have OneDrive’s that we do not want to allow people to access. This includes the CEO and all his direct reports as well as the IT management chain. Not that we don’t trust our IT Technicians, it’s more of a best practice. That data is considered ultra sensitive so we do not attach automated processes to it to prevent leaks and such. In the event that a technician’s account is compromised, we want to limit the risk of exposure and exfiltration where possible.
The only thing special about this list is that we remove permissions inheritance, give only a select group full control in order to update it, and grant the service account read access to the list.
The list will be something like this:
| User UPN |
| John.Doe@adatum.com |
| Bill.Deverson@adatum.com |
| Bob.Testerton@adatum.com |
OneDrive Permissions – Temporary
This list will be used by the IT Technicians to request the temporary access to a target user’s OneDrive. It contains 4 columns:
- Ticket Number – contains the ticket number associated with the request. This is marked required.
- Admin UPN – contains the UserPrincipalName of the IT technician’s “admin_” account that will be granted access. This is marked required and has Column Validation to ensure the text begins with “admin_”
- Target UPN – contains the UserPrinicpalName of the user whose OneDrive will be accessed temporarily. This is makred required.
- Status – contains the current status of the request. The Flow will update this through out the process. It is not marked required but has a default value of “Requested “.
I generally hate the Title column. In my lists, including this one, I usually change it to not required, hide it from all views, and mark it as hidden on the Item content type. The reason is that it keeps the Title permanent name on the backend which makes coding a bit awkward for a general column that is not an actual “title”.
Another thing that needs to be done is to mark the “Status” column as hidden from the item content type. We don’t want the user’s updating or setting this. We want Flow to “own” this column.
For the permissions on this list we grant the same limited group from before full control of the list. We also grant the Service Account full control of the list. I also had to create a new Permission in the site collection that I called “AddAndViewOnly”. This permission site grants the ability to add items and view items, but not to update, modify, or delete items. I granted this permission then to the IT Service Desk group. This way it serves as an immutable record, of sorts, to track who has granted what when and why. That should satisfy auditor requirements.
In the default view I also made the “Created” and “Created By” columns visible.
| Ticket Numer | Admin UPN | Target UPN | Status | Created | Created By |
| 9729816 | admin_smartk@adatum.com | Abba.Abdul@adatum.com | Permissions Added | 5/31/207 | Kyouko Smart |
| 6524464 | admin_goodalt@adatum.com | Tan.Kyuei@adatum.com | Rejected - User Protected | 6/1/2017 | Tom Goodall |
| 9625751 | admin_proderj@adatum.com | jeff.brokenly@adatum.com | Requested | 6/1/2017 | Jill Proderson |
Go with the Flow
The last piece of the puzzle and the glue of the entire application is a Microsoft Flow. I’m used to PowerShell being my glue but this time I wanted to play with Microsoft Flow. I have worked with SharePoint workflows before to make some interesting things possible, but it appears to me that Microsoft is trying to kill off SharePoint workflows off and replace them with Flow. I’m not heavily opinionated on this as SharePoint is not my bread and butter. But, I’m always up for learning and trying something new.
Flow has been on my radar since it was added to our Office 365 tenant. I haven’t had a good use-case for it until now. Flow is really meant for business users to automate workflows. It allows for a very graphical way of putting tasks in order to reach a result based on a trigger.
Where to Begin?
We are going to use the service account for the creation of the Flow so that the Service account is the original owner of the flow. This way all actions in the flow are more easily connected to the service account including list access and Azure Automation account access. After I created it I shared the Flow my “admin_” account so I could modify it later without having to log in as the service account. I also shared it with my team so it can survive me if I happen to leave the company. this moves the flow from the “My Flows” tab to the “Team Flows” tab and makes it visible to all those it is shared with on that tab.
Microsoft added Flow integration with SharePoint online not too long ago. At the top of the list there is a Flow drop down
Click the “Create a Flow” option and then chose the “When a new item is added…” template option
Flow Logic
As I said before, this isn’t a Flow blog and I don’t really intend this to be a Flow tutorial. I will only cover the overall logic of the flow and wont go into great detail about the individual pieces. It’s also hard to show without revealing much of my production environment.
The flow looks like this:
When a new item is created
This is the trigger for the flow. It is created automatically when we chose the Flow template. this is what determines when the flow starts running.
Initialize variable
This step initializes a ProtectedAccounts integer variable to 0. This variable will be used to check if the target user is a protected account. I originally tried using a Boolean, but for whatever reason, the Boolean would not get processed correctly and the Flow would not properly detect the presence of a protected user. Instead, I use an integer and increment it by 1 for each protected account it finds in the “Check if user is protected” step. Then I evaluate if it is grater than 0 in the “Exit if User is Protected” step.
- 0 - not protected
- >0 - protected.
Get Blocked Users
This step pulls in all the items from the “OneDrive Permissions Blocked” list
Check if user is protected
This step loops through each result from the “Get Blocked Users” step, checks if the “User UPN” in the “OneDrive Permissions Blocked” list starts with the “Target UPN” from the “OneDrive Permissions – Temporary” request. If there is a match it increments ProtectedAccounts by 1.
Exit if User is Protected
One thing I like to do whenever possible no matter what programming language I’m using is to use Flat Logic and avoid nests as much as possible. This means using short-circuits to terminate early. Normally, I would exit right away when a user is found to be protected, but Flow does not permit the “Control – Terminate” task within “Apply to each” loops. That is the reason for the ProtectedAccounts variable. If it is set to anything greater than 0 the Flow updates the status on the item to ‘Rejected – User is Protected’, sends an email to the IT Technician letting them know the request was rejected, and then terminates the Flow.
Ensure Admin account
This step is an added precaution incase someone bypasses the Column Validation on the Admin UPN column. It verifies that the Admin UPN starts with “admin_” and ends with “@adatum.com” . The advanced condition looks like this:
Update – Status – Adding Permissions
This step updates the Status column on the item to “Adding Permissions” at this point the requestor is valid and the target is not protected so we can begin adding permissions.
Run AddOneDriveSiteAdmin
This step passes the “Admin UPN” and “Target UPN” from the new list item to the $Requestor and $TargetUser parameters respectively and waits for the job to complete before continuing. This fires off the PowerShell script in the AddOneDriveSiteAdmin Runbook.
Update Status – Permission Added
This step updates the Status column of the list item to “Permissions Added”
Let requestor know the permissions have been added
This step emails the item creator and lets them know the permissions have been added and that the permissions will be removed in 2 hours.
Wait 2 hours
This step puts the Flow to sleep for 2 hours
Update Status – Removing Permissions
This step updates the list item Status column to “Removing Permissions”
Run RemoveOneDriveSiteAdmin
This step is similar to the Run AddOneDriveSiteAdmin except it runs the RemoveOneDriveSiteAdmin Runbook.
Update Status – Permissions Removed
This step updates the list item Status column to “Permissions Removed”
Let requestor know Permissions are Removed
This step emails the item creator and lets them know the temporary permissions have been removed.
Flow Issues
Flow is really easy to use and get up and running quickly. However, it has some serious quirks. One already noted was that I could not terminate the flow from inside an “apply each” loop. That means additional logic is required.
Another issue was that I could not use a SharePoint item’s dynamic content on the ‘Schedule – Delay” task. The original idea was to have SharePoint list column with a limited number of hours that the IT users could chose from (1-8) and the Flow would sleep for that amount. The “Schedule – Delay” task takes a strict integer and the only field that SharePoint returns as an integer is the item ID. Technically, number columns in SharePoint are doubles so they can’t be used either. No column type I had available would work.
Another pain point in this was there is no directly linked documentation for advanced conditions. I found a forum post for Flow which says that it uses the Logic Apps language.
Finally, the Dynamic Content helper is nice, but, it eventually gets really crowded. It would be great if it would collapse the the tasks by default so it’s easier to pick the correct item. For example, since I’m making a great number of updates to the same list, by the final update action, I have the “Target UPN” for all 6 previous SharePoint actions. For this Flow, it doesn’t matter as much because I’m basically using the same values every time. However, if I were doing something more complex, it would be easy to chose the wrong one. Since this is targeted at business users, I could see that being problematic for them.
Conclusions
This was a blast to do. I really enjoyed linking all of these technologies together and getting a satisfying result. These tools are currently in user testing. I’m missing some production quality things such as proper exit codes for failures in the Runbook and watching for failures of the Runbook in the Flow. Right now, errors still result in a success status for the Runbook. Even if it was returning a failure exit code, Flow would still assume it was successful.
I hope this was somewhat informative. I know it lacks great detail, but I hope it can help at least point others in the right direction.
