none
What is the supported way to authenticate to odata on Project Online? RRS feed

  • Question

  • I have a .net app that needs to consume data from Project Online. (Note: this is not a "project app", at this point it's just a console application). In the browser, I can easily connect to /_api/projectdata. But how can this be done via C#? I have found a variety of different methods for authenticating with office 365 on the internet, but the ones I've found seem a bit hacky and I'm not confident that those methods are really supported by MS. So, is there a supported method for authenticating and working with oData from Project Online from an on-premise application?

    Mike G.

    Thursday, October 10, 2013 6:54 PM

Answers

  • Thanks for Paul on this thread:

    https://social.msdn.microsoft.com/Forums/en-US/3e597b16-c4b5-48bb-a6e7-fcbdce49c784/c-desktop-application-get-project-online-data-using-odata-service?forum=project2010custprog

    I finally have my answer on the only supported way to grab data from Project Online from a standalone app. It's a bit messy, but at least you can get the data and map it to data contracts.

                var securePassword = new SecureString();
                string password = "ThePassword";
                foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
                var cred = new SharePointOnlineCredentials("TheUserName.onmicrosoft.com", securePassword);
    
                string url = "https://servername.sharepoint.com/sites/pwa/_api/ProjectData/Projects?format=json";
                var req = (HttpWebRequest)WebRequest.Create(url);
                req.Credentials = cred;
                req.Headers["X-FORMS_BASED_AUTH_ACCEPTED"] = "f";
    
                var resp = (HttpWebResponse)req.GetResponse();
                var receiveStream = resp.GetResponseStream();
    
                var readStream = new StreamReader(receiveStream, Encoding.UTF8);
    
                var data = readStream.ReadToEnd();

    Friday, May 13, 2016 10:16 PM
  • Tuesday, May 17, 2016 5:08 PM
    Moderator

All replies

  • Hi Mike

    You should look at http://msdn.microsoft.com/en-us/library/office/jj163048.aspx under section Creating LinQ queries that should give you relevant information for consuming oData using console application

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Text;
    using System.Data.Services.Client;
    
    namespace TestProjectData
    {
        class Program
        {
            private const string PSDATA = "http://ServerName/ProjectServername/_api/ProjectData";
            
            static void Main(string[] args)
            {
                ProjectOData.ReportingData context = 
                    new ProjectOData.ReportingData(new Uri(PSDATA, UriKind.Absolute));
                context.Credentials = CredentialCache.DefaultCredentials;
    
                var projectQuery1 = from p in context.Projects
                                    where p.ProjectStartDate > new DateTime(2012, 1, 1)
                                    orderby p.ProjectName
                                    select p;
    
                Console.WriteLine("Using declarative LINQ query syntax:\n");
                foreach (ProjectOData.Project proj in projectQuery1)
                {
                    Console.WriteLine(proj.ProjectName + " :\tStart date: " + proj.ProjectStartDate.ToString());
                }
    
                var projectQuery2 = context.Projects
                                       .Where(p => (p.ProjectStartDate > new DateTime(2012, 1, 1)))
                                       .OrderBy(p => p.ProjectName)
                                       .Select(p => p);
    
                Console.WriteLine("\n\nUsing lambda expressions for a LINQ query:\n");
                foreach (ProjectOData.Project proj in projectQuery2)
                {
                    Console.WriteLine(proj.ProjectName + " :\tStart date: " + proj.ProjectStartDate.ToString());
                }
    
                Console.Write("\nPress any key to exit: ");
                Console.ReadKey(true);
            }
        }
    }

    Let us know if this helps

    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Thursday, October 10, 2013 7:12 PM
    Moderator
  • That method works great for an on premise installation of project server. But I'm trying to connect to Project Online. So, the "Add Service Reference" dialog doesn't work, as it doesn't support the office 365 authentication. I was able to get past that with a fiddler hack, but then how do you authenticate in code to the office 365 / Project online service? CredentialCache.DefaultCredentials won't work, as Project Online isn't using domain credentials.

    Mike G.

    Thursday, October 10, 2013 7:43 PM
  • My fault missed the online part, let me check if i can figure out a way for authentication

    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Thursday, October 10, 2013 8:46 PM
    Moderator
  • Hi Mike,

    You might find this article by Wictor Wilen helpful: http://www.wictorwilen.se/Post/How-to-do-active-authentication-to-Office-365-and-SharePoint-Online.aspx

    That will get you the required authentication cookies to remotely use the client object model without being in an authenticated App context, even though that was written for <2013 it should work for you here also.

    However I'd ask why are you trying to do this without creating a provider or SharePoint hosted app? What user context will your code run in when using the above method? I assume you won't be logging in manually each time then running your code?

    HTH,

    Martin


    Martin Laukkanen (Project Server Blog - www.nearbaseline.com/blog)

    Monday, October 14, 2013 12:33 AM
  • Thanks for the link, I'd found a few other sites that had working code for SharePoint online for the 2010 version, but they reported problems for the 2013 version, but I'll check that one out.

    Why not an app? Good question. I'm trying to create a sort of timer job. Of course, timer jobs are not supported in SharePoint online, so I thought I'd create a local app that access the project online data via oData and schedule it. It seems like it would be the simplest mechanism, though it has the authentication hurdle. It does work using the CSOM, but the CSOM code is more complex and has much worse performance. (I'm simply trying to read a few oob fields for each project, along with a few custom project enterprise fields. With an on-premise project server, this can be done with one call to the reporting database.)

    If you have other suggestions for a timer job mechanism that is able to read data from Project Online, I'd love to hear them.

    Thanks,


    Mike G.

    Monday, October 14, 2013 12:48 PM
  • Hi,

    I see and yes understand why a scheduled task would solve that easily, though the authentication issue is no small issue which I've looked a for some similar requirements in the past, here's a couple of suggestions;

    Firstly PowerShell:

    $ie = New-Object -ComObject InternetExplorer.Application
            $ie.Navigate($url)
            $ie.Visible = $true
            while ($ie.Busy -eq $true) { Start-Sleep -Milliseconds 50; }
            
            #Check for access denied
            if ($ie.Document.url.ToString() -like '*/AccessDenied.aspx*') {
                write-host "Attempting sign out." -ForegroundColor Yellow
    
                $signOutURL = $url
                
                $tmpUri = New-Object System.Uri($signOutURL)
    
                if ($tmpUri.PathAndQuery.Length -gt 1) {
                    $signOutURL = $signOutURL.Replace($tmpUri.PathAndQuery, '')
                }
                $signOutURL = $signOutURL+'/_layouts/SignOut.aspx'
                $ie.Navigate($signOutURL)
                while ($ie.Busy -eq $true) { Start-Sleep -Milliseconds 50; }
    
                #$ie.Navigate("https://login.live.com")
                $ie.Quit()
    
                # Remove any FedAuth cookies for this URL
                $cookieList = Dir ([Environment]::GetFolderPath("Cookies")) | Select-String $tmpUri.Host -List 
                $cookieList 
    
                #$cookieList = $cookieList | % { Select-String 'FedAuth' -List -Path $_.Path }
                if ($cookieList -ne $null) {
                    $cookieList | % { Remove-Item $_.Path }
                }
    
                $ie = New-Object -ComObject InternetExplorer.Application
                $ie.Navigate($url)
                $ie.Visible = $true        
                while ($ie.Busy -eq $true) { Start-Sleep -Milliseconds 50; }
            }
    
            if ($ie.Document.url.ToString() -notcontains $url) {
                write-host "Attempting authentication."  -ForegroundColor Yellow 
    
                # Add login details         
                $ie.Document.getElementById("i0116").value = $username; 
                $ie.Document.getElementById("i0118").value = $password; 
            
                # Click the submit button 
                $ie.Document.getElementById("idSIButton9").Click(); 
                # Wait for the page to load 
            
                while ($ie.Busy -eq $true) { Start-Sleep -Milliseconds 50; }
                
            }
            else {
                Write-Host "Already authenticated." -ForegroundColor Yellow
    
            }
    
            $result = $ie.Document.body.innerHTML

    ** DISCALAIMER, the above script was part of an automation script that I eventually gave up on as I couldn't get it to do 100% of what I needed, so it might give you a starting place but as-it it will not work.

    2nd suggestion (this one's fully MS supported): Provider hosted remote event receiver. Pick an appropriate event to hook onto then write your code accordingly. The challenge here is that as this time there is very little documentation out there on remote event receivers, and from experience they can be tricky.


    Martin Laukkanen (Project Server Blog - www.nearbaseline.com/blog)

    Monday, October 14, 2013 10:58 PM
  • I know this post is a couple of years old, but even today, i don't think I have seen a "best practice" for authenticating an odata service reference to Project Online. It's even tough to get the odata service reference, because VS does not support office365 authentication. Some people have suggested to get it from a VS Solution where you added it from an onPrem (which is not optimal).

    Can someone offer some guidance on how to do this from a console application? All we want to do is use the odata service reference client and use LINQ queries to get our data from Project Online. 

    To put it even simpler, we are just trying to do what is on the link below, but we want to connect to Project Online. The link below is for onPrem.

    https://msdn.microsoft.com/en-us/library/office/jj163048.aspx

    Thursday, September 24, 2015 5:41 PM
  • Hi Sunil ,

    I followed this logic and try to get the  project names along with some other metadata fields , but its always giving me 300 records . No where i mentioned any rowlimit of the records. 

    Any limitation in getting the records..?

    Thanks,

    Purna

    Tuesday, December 22, 2015 3:05 PM
  • Purna, odata queries against project online or project server are limited to 300 rows at a time. You will need to use odata verbs to skip the first 300 and grab the next 300 until you don't have any left.

    The question on this thread though, is HOW were you able to get this data at all from Project Online. Several people are having trouble executing an odata query against Project Online due to authentication issues. Were you able to do this from a standalone c# console application? If so, please share.

    • Proposed as answer by Purna Chander.M Friday, January 8, 2016 11:42 AM
    • Unproposed as answer by m g Friday, January 8, 2016 4:14 PM
    Tuesday, December 22, 2015 5:59 PM
  • Hi Carlos,

    I am retrieving the project data and binding to grid in this way using Client side programming.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using Microsoft.SharePoint.Client;
    using Microsoft.ProjectServer.Client;
    using System.Globalization;
    
    namespace SampleProjectGrid
    {
        public partial class ProjectGrid : System.Web.UI.Page
        {
            DataTable dtProjects = null;
            private const string pwaPath = "http://myserver.com/mytestgprojects";
            private static ProjectContext projContext;
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    dtProjects = FillProjects(pwaPath);
                    if (dtProjects != null && dtProjects.Rows.Count > 0)
                    {
                        gridProjects.DataSource = dtProjects;
                        gridProjects.DataBind();
                    }
                }
            }
    
            private DataTable FillProjects(string siteUrl)
            {
                dtProjects = new DataTable();
                dtProjects.Columns.Add("ProjectName");
                DataRow dr;
    
                projContext = new ProjectContext(pwaPath);
                // Get the list of published projects in Project Web App.
                projContext.Load(projContext.Projects);
                projContext.ExecuteQuery();
                if (projContext.Projects.Count > 0)
                {
                    for (int i = 0; i < projContext.Projects.Count; i++)
                    {
                        var pubProj = projContext.Projects[i];
                        projContext.Load(pubProj);
                        projContext.ExecuteQuery();
                        //  Console.WriteLine("\n{0}", pubProj.Name);
    
                        dr = dtProjects.NewRow();
                        string projectName = pubProj.Name;
                        dr["ProjectName"] = projectName;
                        dtProjects.Rows.Add(dr);
                    }
                }
                return dtProjects;
            }
        }
    }

    Friday, January 8, 2016 11:44 AM
  • Purna,

    The code you posted is NOT helpful.

    1. It seems to be for OnPrem - we are asking for project online.

    2. The code is clearly running on a web page - we are asking for a console application way that requires authentication to be set manually.

    3. You are using CSOM, we are asking for an Odata way of doing this.

    Friday, January 8, 2016 4:09 PM
  • Hello,

    I am also facing the same authentication issue for getting Project Online data using OData.

    I also want to get the Project Online data using Odata and not using CSOM  in a console application. Does any one found a way of doing it using ODATA . Please let me know as my work is stucked due to it.

    If it is not possible to access Odata for Project Online in C# application do clarify so that we can take further action.

    Thanks & Regards

    Supriya

    Thursday, May 12, 2016 7:14 AM
  • I would appreciate some sample code for this as well. It has to be possible, since you can get odata from excel, power BI and SSIS (all of which are standalone apps and Microsoft has no problem getting odata from them). 
    Thursday, May 12, 2016 1:14 PM
  • Thanks for Paul on this thread:

    https://social.msdn.microsoft.com/Forums/en-US/3e597b16-c4b5-48bb-a6e7-fcbdce49c784/c-desktop-application-get-project-online-data-using-odata-service?forum=project2010custprog

    I finally have my answer on the only supported way to grab data from Project Online from a standalone app. It's a bit messy, but at least you can get the data and map it to data contracts.

                var securePassword = new SecureString();
                string password = "ThePassword";
                foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
                var cred = new SharePointOnlineCredentials("TheUserName.onmicrosoft.com", securePassword);
    
                string url = "https://servername.sharepoint.com/sites/pwa/_api/ProjectData/Projects?format=json";
                var req = (HttpWebRequest)WebRequest.Create(url);
                req.Credentials = cred;
                req.Headers["X-FORMS_BASED_AUTH_ACCEPTED"] = "f";
    
                var resp = (HttpWebResponse)req.GetResponse();
                var receiveStream = resp.GetResponseStream();
    
                var readStream = new StreamReader(receiveStream, Encoding.UTF8);
    
                var data = readStream.ReadToEnd();

    Friday, May 13, 2016 10:16 PM
  • Thanks a lot Paul and Carlos..

    The above solution of reading data using Project Online in standalone application worked. I was looking for this solution from a long time and it is finally here.

    Tuesday, May 17, 2016 5:16 AM
  • Tuesday, May 17, 2016 5:08 PM
    Moderator