C#: How to access SharePoint 365 Excel file with MFA enabled via unattended application?

Question

We have SharePoint 365 site with MFA enabled. One project folder on the SharePoint contains an Excel file that I want to read and process via my unattended C# application. Interactively I can open the file after going through the MFA process. I tried following 5 cases but each one throws an exception:

The URL of the file is:

string excelUrl = @"https://company.sharepoint.com/teams/Projects/DocCtrl/Project.xlsm";

For each of these 5 cases, the web client headers are (showing here to avoid repetitions in the subsequent code):

webClient.Headers[HttpRequestHeader.UserAgent] = "Other";
webClient.Headers["X-FORMS_BASED_AUTH_ACCEPTED"] = "f";

Case-1:

using (WebClient webClient = new WebClient())
{
    webClient.UseDefaultCredentials = true;
    using (MemoryStream memoryStream = new MemoryStream(webClient.DownloadData(excelUrl)))
        using (SpreadsheetDocument xlDocument = SpreadsheetDocument.Open(memoryStream, false))
            readAndProcess(xlDocument);
}

Exception on webClient.DownloadData(excelUrl):
The remote server returned an error: (401) Unauthorized.

Case-2:

I would like to avoid this becuase it needs password to be either hardcoded or managed in some configuration file.

using (WebClient webClient = new WebClient())
{
    webClient.Credentials = new NetworkCredential(@"emailId@company.com", @"Password", "Domain");
    using (MemoryStream memoryStream = new MemoryStream(webClient.DownloadData(excelUrl)))
        using (SpreadsheetDocument xlDocument = SpreadsheetDocument.Open(memoryStream, false))
            readAndProcess(xlDocument);
}

Exception on webClient.DownloadData(excelUrl):
The remote server returned an error: (401) Unauthorized.

Case-3:

I would like to avoid this becuase it needs password to be either hardcoded or managed in some configuration file.

using (WebClient webClient = new WebClient())
{
    SecureString securePwd = new SecureString();
    foreach (char chr in @"Password")
        securePwd.AppendChar(chr);
    webClient.Credentials = new SharePointOnlineCredentials(@"emailId@company.com", securePwd);
    using (MemoryStream memoryStream = new MemoryStream(webClient.DownloadData(excelUrl)))
        using (SpreadsheetDocument xlDocument = SpreadsheetDocument.Open(memoryStream, false))
            readAndProcess(xlDocument);
}

Exception on webClient.DownloadData(excelUrl):
The request was aborted: The request was canceled.
The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling Scenarios.

Case-4:

This does prompt me for the user id, password, and MFA. I would like to avoid such solution because my application is running from unattended console.

using (WebClient webClient = new WebClient())
{
    var authManager = new OfficeDevPnP.Core.AuthenticationManager();
    ClientContext ctx = authManager.GetWebLoginClientContext(@"https://company.sharepoint.com");
    ctx.Load(ctx.Web, w => w.Title);
    ctx.ExecuteQuery();
    using (MemoryStream memoryStream = new MemoryStream(webClient.DownloadData(excelUrl)))
        using (SpreadsheetDocument xlDocument = SpreadsheetDocument.Open(memoryStream, false))
            readAndProcess(xlDocument);
}

Exception on webClient.DownloadData(excelUrl):
The remote server returned an error: (401) Unauthorized.

Case-5:

This also prompt me for the user id, password, and MFA, even though it is specified in the code. I would like to avoid such solution because my application is running from unattended console.

using (WebClient webClient = new WebClient())
{
    var authManager = new OfficeDevPnP.Core.AuthenticationManager();
    ClientContext ctx = authManager.GetWebLoginClientContext(@"https://company.sharepoint.com");
    SecureString securePwd = new SecureString();
    foreach (char chr in @"Password")
        securePwd.AppendChar(chr);
    ctx.Credentials = new SharePointOnlineCredentials(@"emailId@company.com", securePwd);
    ctx.Load(ctx.Web, w => w.Title);
    ctx.ExecuteQuery();
    using (MemoryStream memoryStream = new MemoryStream(webClient.DownloadData(excelUrl)))
        using (SpreadsheetDocument xlDocument = SpreadsheetDocument.Open(memoryStream, false))
            readAndProcess(xlDocument);
}

Exception on ctx.ExecuteQuery():
The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling Scenarios.

Any suggestions on how to access an Excel from MFA enabled SharePoint 365 site from unattended C# application? Would app password help in such cases?

score 0 · Answer 1 · answered Mar 18 '23 at 23:00

Have you already tried using Microsoft Graph API with certificate authentication. If not you can try this:

using Microsoft.Graph;
using Microsoft.Identity.Client;
using Microsoft.Identity.Client.Extensions.Msal;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Security.Cryptography.X509Certificates;
using System.Threading.Tasks;
using System.Net.Http.Headers;
using System;

// Define the SharePoint site URL and Excel file path
string siteUrl = "https://yourtenant.sharepoint.com/sites/your-site";
string filePath = "/Shared Documents/YourExcelFile.xlsx";

// Define the client ID of your Azure AD application
string clientId = "your-client-id";

// Define the thumbprint of the certificate used for app-only authentication
string certThumbprint = "your-certificate-thumbprint";

// Define the Azure AD tenant ID
string tenantId = "your-tenant-id";

// Load the certificate used for app-only authentication
X509Certificate2 cert = null;
using (X509Store store = new X509Store(StoreLocation.CurrentUser))
{
    store.Open(OpenFlags.ReadOnly);
    var certs = store.Certificates.Find(
        X509FindType.FindByThumbprint, certThumbprint, false);
    if (certs.Count == 1)
    {
        cert = certs[0];
    }
}

// Create a new instance of AuthenticationContext using the certificate
string authority = $"https://login.microsoftonline.com/{tenantId}";
var authContext = new AuthenticationContext(authority, false);
var certCred = new ClientAssertionCertificate(clientId, cert);
var authResult = await authContext.AcquireTokenAsync(
    "https://graph.microsoft.com", certCred);

// Create a new instance of GraphServiceClient using the access token
var graphClient = new GraphServiceClient(
    new DelegateAuthenticationProvider(requestMessage =>
    {
        requestMessage.Headers.Authorization =
            new AuthenticationHeaderValue("Bearer", authResult.AccessToken);

        return Task.FromResult(0);
    }));

// Get the Excel file from SharePoint using Microsoft Graph
DriveItem excelFile = await graphClient
    .Sites[siteUrl]
    .Drive
    .Root
    .ItemWithPath(filePath)
    .Request()
    .GetAsync();

// Read the contents of the Excel file into a byte array
byte[] excelContents = await graphClient
    .Sites[siteUrl]
    .Drives[excelFile.ParentReference.DriveId]
    .Items[excelFile.Id]
    .Content
    .Request()
    .GetAsync();

// Process the Excel file contents as needed
// For example, you can use the OpenXml SDK to read the Excel file
// See https://docs.microsoft.com/en-us/office/open-xml/ for more information

score 0 · Answer 2 · answered Mar 19 '23 at 13:25

I'd like to discuss good practices beforehand. An unattended application should never use a user (licensed or not) as means of authentication, whether you consume the Graph API or SharePoint API. As the previous answer implies, you should use an Azure AD Application with Application authentication and rights, authentication with a Client ID and a Client Secret or Certificate.

On the second front, accounts with MFA may not be used by applications, not with their normal password at the very least. Account may define an "application password" to circumvent MFA for applications that do not implement MFA. This may be used for such use cases, but i'm not sure.

C#: How to access SharePoint 365 Excel file with MFA enabled via unattended application?

2 Answers2