This article is contributed. See the original author and article here.
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:
An App Registration in Azure Active Directory that allows you to read the Project reporting data. You will need your Tenant ID and also the Client ID and registered secret of the App Registration
The require App Registration Settings
A user account that just needs Access to Project Server reporting service. You will need the account name and password. The authentication will use the Resource Owner Password Credential (ROPC). This method of authentication is not recommended when other approaches are available (see Microsoft identity platform and OAuth 2.0 Resource Owner Password Credentials ) but as there is no “app-only” authentication options for Project Online this is one such occasion when this is the only way. To ensure this is as secure as possible we will be storing the username and password in Azure Key Vault (AKV).
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.
An Azure Data Factory resource in Azure
Somewhere to write the data. In this example I cover both saving out as Json to blob storage in Azure, as well as saving to SQL Server (in this case hosted in Azure. You will need connection strings for whatever storage you are using
If using SQL Server you will need stored procedures that will do the data handling – more details later
Once you have all these pieces in place, we can continue with ADF to:
Add Linked Services
Add Datasets
Build a pipeline
Linked Services
We need 4 linked services
An Azure Key Vault where we will be storing our account details and App Registration secret
A REST linked service – basically our OData endpoint
Azure Blob Storage (not necessary – but I found it useful in debugging before I added it all into SQL Server)
SQL Server
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.
Basically it is using the output.value property of the previous steps to complete the “grant_type” body needed for an ROPC call.
I then use a Set variable action to take the response and keep the token for later use.
Variable setting for token
The full string used in the Value is @activity(‘M365Login’).output.access_token
Now I have my token I can use that to make my REST call to Project Online’s OData endpoint using a Copy data activity. First I use a Stored procedure activity to clear out my staging table. Take a look at the GitHub for more details, but it is just a ‘delete from’ call.
The copy data activity has a source and sink (destination) and I use one to read and then write to blob storage, then another to read and write to SQL. I’ll concentrate on the second, which has Source settings configured like this:
Source data settings
The source dataset is my REST dataset, I add the header Authorization with a Value of
@concat(‘Bearer ‘,variables(‘token’))
which gets the token from my variable called token, and I have also set the Pagination rulesRFC5988 with a Value True (although that isn’t in the above screenshot.
The Sink settings are as follows:
Sink data settings
with the sink dataset as my SQL dataset ‘ProjectsTable’. The magic happens on the Mappings tab – and I had created a table that matched the columns I was returning from REST – so just a 1:1 mapping. You can get more adventurous here if you need to do anything fancy:
Data mapping from OData to my SQL table
Once that is complete, we have a populated Project staging table with the current projects read from OData. The final steps are then just 3 stored procedure steps that remove deleted projects from the live project table (by deleting if they do not now exist in staging). also deleting any projects that have been updated (the modified date is newer in the staging table) and then finally copying in the updated and new plans from staging to the live table.
As mentioned, this is just the basics and only looks at Projects – but the main focus here was the authentication steps of getting the token with ROPC, then using the token in the REST call.
I appreciate I have glossed over a lot of the detail here so happy to fill in some of the gaps if required in the comments section or another blog if needed. However, if you know ADF and already use SSIS – the authentication piece was probably all you came for.
This article is contributed. See the original author and article here.
Hey there, MTC! We’re dancin’ in September, so let’s boogie on down to this week’s recap!
MTC Moments of the Week
Our first MTC Member of the Week for the new month goes to @Harun24HR! Thank you for your contributions to help other MTC’ers in the Excel community!
We didn’t have any new community events this week, *but* September 1st did mark the start of the Azure Quantum Summer 2022 Hackathon in collaboration with IEEE QCE22! If you’re interested in quantum computing, check out this virtual hackathon happening through Wednesday, September 7, and get the chance to win a Surface Go 3 and other cool prizes!
Every week, users come to the MTC seeking guidance or technical support for their Microsoft solutions, and we want to help highlight a few of these each week in the hopes of getting these questions answered by our amazing community!
This article is contributed. See the original author and article here.
Mozilla has released security update to address a vulnerability in Thunderbird. An attacker could exploit this vulnerability to take control of an affected system.
CISA encourages users and administrators to review the Mozilla security advisory for Thunderbird 102.2.1 and apply the necessary updates.
This article is contributed. See the original author and article here.
CISA, the National Security Agency (NSA), and the Office of the Director of National Intelligence (ODNI), have published part one of a three-part joint publication series, Securing Software Supply Chain Series – Recommended Practices for Developers. This guidance—created by the Enduring Security Framework (ESF), a public-private cross-sector working group led by the NSA and CISA—focuses on software developers and provides suggested practices to ensure a more secure software supply chain.
This article is contributed. See the original author and article here.
Microsoft 365 Defender Monthly news August 2022
This is our monthly “What’s new” blog post, summarizing product updates and various assets we have across our Defender products.
Legend:
Product videos
Webcast (recordings)
Docs on Microsoft
Blogs on Microsoft
GitHub
External
Product improvements
Previews / Announcements
Microsoft 365 Defender
Hunt in Microsoft 365 Defender without KQL! To reduce the learning curve for hunting and enable all analysts to hunt easily, we are excited to announce that a Guided hunting experience in Microsoft 365 Defender is now in public preview! This removes previous dependencies on KQL. Link to learn more about it on our docs: Get started with guided hunting mode.
(GA)Microsoft Defender Experts for Huntingis now generally available. If you’re a Microsoft 365 Defender customer with a robust security operations center but want Microsoft to help you proactively hunt for threats across endpoints, Office 365, cloud applications, and identity using Microsoft Defender data, then learn more about applying, setting up, and using the service. Defender Experts for Hunting is sold separately from other Microsoft 365 Defender products.
Microsoft Defender for Cloud Apps
Webinar Sep 14 9AM PST: Manage your SaaS Security Posture with Microsoft. In the current evolution of SaaS apps, there are many different SaaS configurations and posture options. Misconfigurations are one of them and is a potential risk for your organization that can lead to a breach or sensitive data leakage. Learn how to easily manage your SaaS Security Posture with Microsoft and prevent this potential risk. Register here.
Feature parity between commercial and government offerings. We have expanded our support for GCC customers who can now benefit from the SecOps experience features within Defender for Cloud Apps all from the Microsoft 365 Defender portal.
Azure AD “Security Reader” role alignment. As of August 28 2022, users who were assigned an Azure AD Security Reader role won’t be able to manage the Microsoft Defender for Cloud Apps alerts. To continue to manage alerts, the user’s role should be updated to an Azure AD Security Operator. Currently the Azure AD “Security Reader” role may manage Defender for Coud Apps alerts while the same role may only view alerts from all other workloads. The purpose of this change is to align the AAD “Security Reader” role assignments to provide clarity for the customers, prevent confusion of the same role use.
Hunt for Azure subscriptions using Defender for Cloud Apps. This blog describes how attackers can compromise Azure subscriptions and use them for malicious activities. In addition, it shows how Microsoft Defender for Cloud Apps data can help hunt for these activities and how to mitigate the risk of compromised subscriptions.
Protect sensitive SharePoint sites with Defender for Cloud Apps. This blog walks through the configuration of Azure AD, Purview, SharePoint Online and Defender for Cloud Apps to block downloads of a file that has sensitive content. This will also provide an example of how you can configure it in your own environment.
Microsoft Defender for Endpoint
New Device Health Reporting for Microsoft Defender for Endpoint is now in Public Preview. We’veredesigned the dashboard so that you can view sensor health and antivirus protection status across platforms and easily access detailed Microsoft Defender for Endpoint information.
Network Protection and Web Protection for macOS and Linux is now in Public Preview! Read all the details in this blog post as well as how to evaluate them in your environment.
Step-by-step guide on how to deploy Attack Surface Reduction rules to Azure VMs using Azure Guest Configurations.
Webinar Sep 6 9AM PST: Microsoft Defender for Identity | Identity Targeted Attacks – A Researcher’s Point of View. Attendees will get a peek behind the curtain and see how our research teams deal with newly disclosed identity vulnerabilities, and how that information is turned into an alert in Defender for Identity. Register here.
Microsoft Defender for IoT
Webinar Sep 14 8AM PST: The Last Piece of the XDR Puzzle – Augmenting IT SecOps with IoT Security. Security teams invest heavily in bringing security-related telemetry and data into a single place, with the vision of “one XDR to rule them all”. But many overlook a huge bulk of the network that remains obscure – IoT and unmanaged devices. Join us in reviewing how Microsoft Defender for IoT integrates with M365D to complete the XDR story with IoT visibility, assessment, and security. Register here.
Microsoft Defender for Office 365
Exciting Feature Updates to Attack Simulation Training. We have been hearing from a lot of our enterprise customers that payload technique variety is key to any long-term end user behavior change program. To help facilitate we are pleased to announce two new payload techniques.
Improving the reporting experience in Microsoft Defender for Office 365. These new reporting features and improvements will help refine SecOps professional’s workflows when assessing Office 365 security effectiveness.
Announcing the release of step-by-step guides! These guides are there to help you with common tasks across the product in a flash, with the minimum information & clicks needed, reducing the time needed by your admins to secure your enterprise.
Introducing tenant blocks via admin submissions. You can now block suspicious entities when submitting emails, URLs, or attachments for Microsoft to review.
Mastering Configuration in Defender for Office 365 – Part Three. This blog is the final installment of a three-part series detailing the journey we’re on to simplify configuration of threat protection capabilities in Office 365 to enable best-in class protection for our customers.
Automatic Redirection to Microsoft 365 Defender is coming! All security-related functionality will be automatically redirected from the Office 365 Security & Compliance Center (https://protection.office.com) to the Microsoft 365 Defender portal. Additional details on our docs page.
We’re updating Microsoft Secure Score improvement actions to ensure a more accurate representation of your organization’s security posture. This update will include new recommendations as Microsoft Secure Score improvement actions for Microsoft Information Protection and for anti-spam policies in Defender for Office 365.
This article is contributed. See the original author and article here.
Apple has released security updates to address a vulnerability (CVE-2022-32893) in iPhone 5s, iPhone 6, iPhone 6 Plus, iPad Air, iPad mini 2, iPad mini 3, and iPod touch (6th generation). Exploitation of this vulnerability could allow an attacker to take control of affected device.
CISA encourages users and administrators to review Apple’s advisory HT213428 and apply necessary updates.
This article is contributed. See the original author and article here.
As more sophisticated cyber criminals take aim at hybrid and remote workers, Microsoft is working to raise awareness among Exchange Online customers that one of the most important security steps they can take is to move away from outdated, less secure protocols, like Basic Authentication. As previously announced, we are turning off Basic Authentication in Exchange Online for all tenants starting October 1, 2022.
This article is contributed. See the original author and article here.
We all know one, the person whose weekend is consumed by a singular passion for their favorite sports team. Their weekdays are dedicated to deconstructing the recent drama while contemplating the narrative awaiting the Saturday or Sunday afternoon ahead. The rain-soaked terraces are home to them. The booming music of the arena is their hymn book, and the legendary names hanging from the rafters are their heroes. A peek into their closet will reveal a sea of matching colors printed on scarves and t-shirts. Their social media will be awash with images of heroes present and past, opinions of games told in terms worthy of epic conquests, and, of course, the barbed insult to anyone who dares question the validity of their narrative.
This is the world of the sports “super fan”they are the backbone of the world’s greatest sports franchises. Along with their passion, the super fan is the mainstay of a team’s economy, accounting for a disproportionate number of ticket sales, merchandise, media impressions, and revenue. Teams all over the world love their super fans. In some countries and sports, they have seats in the boardroom and strong voices in ownership decisions. And while it is tempting to view the super fan as a dynastic, generational phenomenon passed on through time and culture, the truth is that today, the super fan is a function of data as much as history. Big, multidimensional data gleaned from the numerous touch points between fans and sports franchises is woven into insights that are specific and actionable at an individual level. Data is how casual fans ascend the ladder of engagement to become super fans and how super fans are rewarded and celebrated.
Row 15 seat 164 in Valencia’s vast Mestalla Stadium is a hallowed place. It is occupied by a statue honoring Seor Vicente Navarro Aparicio, a lifelong Valencia CF fan who sat in that seat for 25 years, never missing a game, even when his eyesight failed him in his later years. Seor Aparicio is the very definition of “super fan.” Valencia CF is more than 100 years old with a proud history of Spanish and international success, and a worldwide fan base of more than 50 million supporters. Although fan engagement has always been a top priority, the club didn’t always know its fans or what they needed in the intimate detail they do today. When Franco Segarra joined Valencia CF as Innovation Director, he immediately recognized the need for a new game plan to enhance fan engagement.
“Super fans aren’t like ordinary customers. They are passionateexperiencing euphoria and shedding tears or losing sleep with the inevitable ups and downs. The sport is steeped in tradition, where fans have special rituals, passed down from parents and grandparents. Therefore, each fan is unique. Fragmented data makes it impossible to understand, let alone deliver, deeply personal experiences that speak to each fan as an individual.”Franco Segarra, Innovation Director, Valencia CF.
Franco Segarra, Innovation Director, Valencia CF
Innovate personalized customer experiences
As an innovative and forward-thinking organization, Valencia CF is continuously improving the global fan experience and building new connections with their fans. Using Microsoft Dynamics 365, the club gained new and actionable insights and a deeper understanding of its fans. With an integrated data architecture, Valencia can run personal campaigns with its season ticket holders. From deploying an app to speed food orders in the stadium to checking in with 1,500 season ticket holders who missed in-person games during the COVID-19 lockdowns, Valencia has used the insights from its customer data to create delightful, rewarding, and engaging fan experiences. By applying custom Al on top of the unified data, the club was able to predict which season ticket holders were most likely to attend games. For those who missed two consecutive games, Valencia CF reached out with a tailored email. And for a subsegment of the fans, football legends Ricardo Arias and Miguel Angel Bossio, who played for Valencia CF in the 80s and 90s, made personal calls.
“Imagine the older fans’ reactions when their all-time favorite idol calls to say hi and see how things are goingit’s an unforgettable experience. It also gave us the opportunity to sincerely thank our fans and hear firsthand what’s on their mind. Customer insights helped us engage at the right moment with a meaningful personal touch that deepened the relationship and strengthened loyalty.”Franco Segarra, Innovation Director, Valencia CF.
Take a look at how Valencia CF leveraged customer data and technology to create amazing fan experiences.
This embed requires accepting cookies from the embed’s site to view the embed. Activate the link to accept cookies and view the embedded content.
Valencia CF demonstrates that transforming a casual fan into a super fan is about delighting and engaging that fan on a one-to-one basis.
Super fans are increasingly important in the world of sports brands. Building the commitment of the super fan is not just about tradition and generational influence. It’s about data, and more specifically, drawing all the diverse data sources into one platform driving innovation. But we are only at the beginning of this data journey, and Microsoft is the partner enabling sports teams all over the world to discover the exciting possibilities when the power of integrated data is unleashed.
Variable setting for token
Source data settings
Sink data settings
Data mapping from OData to my SQL table
Recent Comments