Microsoft 365
Disclaimer: This page discusses a Microsoft-developed open source package, which was at version 2.3.4 at the time of this writing. Readers should refer to the Microsoft365R package documentation developed by Hong Ooi for the most up-to-date information.
Introduction
Microsoft 365 is a subscription extension of the Microsoft Office product line with cloud hosting support. The Microsoft-supported method for interfacing with R is the Microsoft365R
package which was developed by Hong Ooi and has extensive documentation. It supports access to Teams, SharePoint Online, Outlook, and OneDrive.
This article describes approaches for authentication to Microsoft 365 using the Microsoft365R
R package for use with the Posit professional products. Examples for interacting with data from SharePoint and writing data in pins format to SharePoint are included.
Authentication Options in Microsoft365R
Discussion between the developers and the Global Azure Administration team about your organization’s content and security requirements should determine the approaches supported.
There are four main authentication approaches supported by Microsoft365R
that can be used depending on how the developer wants to access the Microsoft resources.
Method | auth_type | Permissions | Capability |
---|---|---|---|
User Sign-in Flow: Default | default | User | Interactive only (local IDE and Workbench, interactive Shiny content) |
User Sign-in Flow: Device Code | device_code | User | Interactive only (local IDE and Workbench) |
Service Principal / Client secret | client_credentials | Application | Interactive and non-interactive (same as above plus scheduled content) |
Embedded Credentials | resource_owner | User | Interactive and non-interactive (same as above plus scheduled content) |
Authentication Background
Authentication for Microsoft365R
is through Microsoft’s Azure cloud platform through a registered application. This grants developers access to the underlying Microsoft API for programmatic access to Microsoft resources.
Within this page “application,” also referred to as “app,” refers to a specific part of the Microsoft Authentication Flow. The application is used to:
- Obtain an ‘OAuth 2.0’ token by authenticating the requesting user or system
- Provide token to access protected APIs for interacting with Microsoft resources
Microsoft365R
will use a pre-existing app, “d44a05d5-c6a5-4bbb-82d2-443123722380”, by default that is pre-configured for use with the RStudio IDE and interactive use. Depending on your organizations security it may be blocked and need the Azure Global Administrator to grant access. This default application does not support scheduled reports or development / deployment outside of the RStudio IDE without additional configuration.
A custom app will need to be registered if the default app is blocked or the content being developed is being run outside of the RStudio IDE and/or in a non-interactive context. Depending on the organization’s security policy the custom app registration can be done either by the developer or the Azure Global Administrator. The steps for registering a custom app are provided in this Microsoft page with additional details below.
Under the hood, Microsoft365R
is using the AzureAuth
and AzureGraph
packages, also developed by Hong Ooi, for authentication, token management, and access to the Graph API. In some cases, for example with troubleshooting or to remove interactive prompts in scheduled content, calling those packages directly to manage tokens is useful.
Authentication Configurations
Depending on your organization’s security policy these steps may be able to be done by the developer, may require support from your Azure Global Administrator for some steps, or may need to be entirely handled by your Azure Global Administrator. The steps for registering a custom app are provided in this Microsoft page.
User Sign-in Flow: Default
A custom app can be created or the default app registration “d44a05d5-c6a5-4bbb-82d2-443123722380” that comes with the Microsoft365R
package can be used. The user permissions will need to be enabled as detailed in the app registrations page. Depending on your organization’s security policy, access to your tenant may need to be granted by an Azure Global Administrator. Additionally, Redirect URLs will need to be added through Azure under App Registrations
-> select your app
-> Authentication
-> Platform configurations
-> Mobile and desktop applications
->Add URI
as well as also enabling nativeclient
.
For adding Redirect URLs, which will give a typical web-app authentication experience for interactive applications:
- For RStudio Desktop the URL is:
http://localhost:1410/
. - For content hosted in shinyapps.io this would be of the form
https://youraccount.shinyapps.io/appname
(including the port number if specified). - A SSL certificate will be required for non-local connections. This means that the Connect and Workbench URLs will need to be HTTPS. A wildcard could be used where appropriate for server-wide access.
User Sign-in Flow: Device Code
A custom app can be created. User permissions will need to be enabled as detailed in the app registrations page. Depending on your organization’s security policy, access to your tenant may need to be granted by an Azure Global Administrator.
Enabling the device code workflow is through the App Registration dashboard in Azure -> click on the created app
-> Authentication
-> Allow public client flows
and setting Enable the following mobile and desktop flows
to yes
. The device code workflow does not need Redirect URLs. Instead, this method provides a code and a link for the developer to access in a separate browser window (or even on a separate device) for sign-in.
Service Principal / Client Secret
A custom app will need to be registered in Azure with Application permissions. The permissions can be based off of the user permissions but can be assigned as needed for the application and to comply with any security restrictions.
Application permissions are more powerful than user permissions so it is important to emphasize that exposing the client secret directly should be avoided. As a control, using environment variable’s for storing the client secret is recommended. Connect allows Environment Variables. The variables are encrypted on-disk, and in-memory.
Adding environment variables can be done at the project level with securing deployment through the Connect UI.
Embedded Credentials
A custom app will need to be registered in Azure with User permissions as specified in the app registrations page. Depending on your organization’s security policy, access to your tenant may need to be granted by an Azure Global Administrator.
The credentials being embedded can be a user or a service account, as long as access to the desired content inside Microsoft 365 has been granted. Creating service accounts per content is recommended to enable faster troubleshooting and easier collaboration. As a control, the Username / Password should never be exposed directly in the code, instead using Environment Variables. The variables are encrypted on-disk, and in-memory.
Adding environment variables can be done at the project level with securing deployment through the Connect UI.
Authentication Examples Using Microsoft365R
User Sign-in Flow: Default
The user sign-in flow option provides the typical web browser authentication experience. A user will need to be available to interact with the authentication pop-up in order to which makes this an option for interactive applications (such as the local RStudio IDE, Workbench, or an interactive Shiny app), but not applicable for scheduled content. The details are discussed in the auth vignette.
library(Microsoft365R)
= MySharepointSiteURL
site_url = MyApp
app
<- get_sharepoint_site(site_url = site_url, app = app) site
User Sign-in Flow: Device Code
In some interactive cases it may be easier to use the device code flow where the user is prompted with a code and a link which is opened in a separate screen for logging in. For example for using a Workbench instance that was deployed without an SSL certificate. This does require interaction from the user and as such will not be applicable for scheduled content nor hosted content. The details are discussed in the auth vignette.
library(Microsoft365R)
= MySharepointSiteURL
site_url = MyApp
app
<- get_sharepoint_site(site_url = site_url, app=app, auth_type="device_code") site
Service Principal / Client Secret
Content in a non-interactive context (such as scheduled reports) won’t have a user account available for interactive authentication. There are several approaches outlined in the vignette, with the Service Principal via using a Client Secret discussed in this section being the Microsoft recommended approach.
- Application permissions are more powerful than user permissions so it is important to emphasize that exposing the client secret directly should be avoided. Instead the recommended approach is to store it as an Environment Variable which can be done through the Connect UI.
- Use of the Microsoft developed package AzureAuth may be needed for fully removing console prompt elements so a script can be run in a non-interactive context, for example by explicitly defining the token directory with
AzureAuth::create_AzureR_dir()
.
library(AzureAuth)
library(AzureGraph)
library(Microsoft365R)
= MyTenant
tenant = MySharepointSiteURL
site_url = MyApp
app
# Add sensitive variables as environmental variables so they aren't exposed
<- Sys.getenv("EXAMPLE_SHINY_CLIENT_SECRET")
client_secret
# Create auth token cache directory
create_AzureR_dir()
# Create a Microsoft Graph login
<- create_graph_login(tenant, app, password=client_secret, auth_type="client_credentials")
gr
# An example of using the Graph login to connect to a SharePoint site
<- gr$get_sharepoint_site(site_url) site
Embedded Credentials
Content in a non-interactive context (such as scheduled reports) won’t have a user account available for interactive authentication. There are several approaches outlined in the vignette. In cases where the additional access that comes with Application level permissions isn’t appropriate for the organization’s security requirements the embedded credentials approach can be used.
- The credentials embedded will need to be granted access to the desired content and can either be a user or a service account. Working with your Azure Global Administrator to create service accounts per content is recommended to enable fast troubleshooting and easier collaboration.
- Sensitive variables such username / password should be embedded as Environment Variables so that they aren’t exposed in the code directly. This can be done through the Connect UI. See the example here.
- Use of the Microsoft developed package
AzureAuth
may be needed for fully removing console prompt elements so a script can be run in a non-interactive context, for example by explicitly defining the token directory withAzureAuth::create_AzureR_dir()
.
library(AzureAuth)
library(AzureGraph)
library(Microsoft365R)
= MyTenant
tenant = MySharepointSiteURL
site_url = MyApp
app
# Add sensitive variables as environmental variables so they aren't exposed
<- Sys.getenv("EXAMPLE_MS365R_SERVICE_USER")
user <- Sys.getenv("EXAMPLE_MS365R_SERVICE_PASSWORD")
pwd
# Create auth token cache directory, otherwise it will prompt the user on the console for input
create_AzureR_dir()
# create a Microsoft Graph login
<- create_graph_login(tenant, app,
gr username = user,
password = pwd,
auth_type="resource_owner")
# An example of using the Graph login to connect to a SharePoint site
<- gr$get_sharepoint_site(site_url) site
Troubleshooting Authentication Failures
In the case of authentication failures clearing cached authentication tokens/files can be done with:
library(AzureAuth)
library(AzureGraph)
= MyTenant
tenant
::clean_token_directory()
AzureAuth::delete_graph_login(tenant="mytenant") AzureGraph