The first problem we hear from customers moving to Azure Data Factory (ADF), who have been using SQL Server Integration Services (SSIS) to get their Project Online OData, is that the authentication and authorization is not straightforward. There isn't a simple choice to login to Project Online, so you have to make a call to get a token which can then be used in the REST calls to OData. The following post steps through the process. I'm not going deep into the details of ADF and won't cover all the steps of making an App Registration - there are plenty of resources out there, and this concentrates on the authentication then pulls in some Project level data. It gets more complicated obviously when you also want tasks and assignments, but the same approaches used with SSIS will work just as well in ADF.
TL;DR - if you know all about ADF and Project Online and App Registrations and just want the auth piece - jump to the M365Login section - just about halfway down, or just take a look at https://github.com/LunchWithaLens/adf which has definitions for the whole pipeline.
What you will need:
The require App Registration Settings
Minimum user settings for the account (although they don't need Team Member)
In this example they are also a team member, but that is not necessary.
Once you have all these pieces in place, we can continue with ADF to:
Linked Services
We need 4 linked services
To keep this blog relatively short, I'm not going into all the details of setting up AKV, just that using a managed identity makes it fairly easy to use in AFD.
The REST linked literally just needs the base URL configured - and this will be the URL for your PWA instance's OData feed, along with any select options to limit the returned fields. As an example, I used:
This limited the columns returned to just those I needed. The authentication type was left as anonymous as I was handling this latter with a bearer token.
The Azure Blog storage isn't a necessity - if you want to use one then easy to configure but I won't go into the full details here. Ping me in the comments if you can't find good resources to help.
Finally the SQL Server, and mine was a database I was already using for something else to which I just added a couple of tables and sprocs. In an earlier attempt I'd configured a more expensive SQL Server instance than I'd realised - and blown through my monthly allowance... The SQL Server linked service allows easy connectivity to an AKV to get the connection string - for a secure configuration.
Datasets
The datasets match up to 3 of the linked services. My "RestResource1" to link to my REST, my "ProjectTable" to match up to my SQL database and a specific table, and my "json1" that I use to connect to my blob storage to save a file. Again, configuring these I leave as an exercise for the reader :) , but the GitHub repo has definitions for all of these so you can see how they hang together. The pipeline will help them make more sense too - which comes next.
The Pipeline
To help visualize where we are headed, first we can look at the final short pipeline:
The full end-to-end pipeline
The first column of activities is reading the required data from AKV. The names should make it obvious what the data is, the username and password, the ClientId and secret for the app registration, then finally the scope for the authentication call. This isn't strictly a 'secret' but I put in in the AKV as it helps when demonstrating (or recording) the solution to be able to show the values. Exposing the scope is no big deal and avoids having to redact stuff in any recording I do.
The only part defined for these activities are the settings - and the scope one is a good example:
Example KeyVault settings
The most interesting step, and maybe the only one you are interested in, is the one I called M365Login - and that is just my name - there isn't a special activity, it is just a web activity. The settings for this one are as follows:
Web call settings to get token
The URL is of the form https://login.microsoftonline.com/<tenantid>/oauth2/v2.0/token and the method is POST and the headers configured as shown above with Content-Type application/x-www-form-urlencoded, Accept */* and Connection keep-alive. The Body is the key part - and is using the concatenation function and brings in the values from the previous calls to AKV. The full form looks something like the following, where I have used specific names for my AKV activities - yours may vary.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.