Enable SAP Principal Propagation for live OData feeds with Power Query

Article
10/12/2023

Working with SAP datasets in Microsoft Excel or Power BI is a common requirement for customers.

This article describes the required configurations and components to enable SAP dataset consumption via OData with Power Query. The SAP data integration is considered "live" because it can be refreshed from clients such as Microsoft Excel or Power BI on-demand, unlike data exports (like SAP List Viewer (ALV) CSV exports) for instance. Those exports are static by nature and have no continuous relationship with the data origin.

The article puts emphasis on end-to-end user mapping between the known Microsoft Entra identity in Power Query and the SAP backend user. This mechanism is often referred to as SAP Principal Propagation.

The focus of the described configuration is on the Azure API Management, SAP Gateway, SAP OAuth 2.0 Server with AS ABAP, and OData sources, but the concepts used apply to any web-based resource.

Important

Note: SAP Principal Propagation ensures user-mapping to the licensed named SAP user. For any SAP license related questions please contact your SAP representative.

Overview of Microsoft products with SAP integration

Integrations between SAP products and the Microsoft 365 portfolio range from custom codes, and partner add-ons to fully customized Office products. Here are a couple of examples:

The mechanism described in this article uses the standard built-in OData capabilities of Power Query and puts emphasis for SAP landscapes deployed on Azure. Address on-premises landscapes with the Azure API Management self-hosted Gateway.

For more information on which Microsoft products support Power Query in general, see the Power Query documentation.

Setup considerations

End users have a choice between local desktop or web-based clients (for instance Excel or Power BI). The client execution environment needs to be considered for the network path between the client application and the target SAP workload. Network access solutions such as VPN aren't in scope for apps like Excel for the web.

Azure API Management reflects local and web-based environment needs with different deployment modes that can be applied to Azure landscapes (internal or external). Internal refers to instances that are fully restricted to a private virtual network whereas external retains public access to Azure API Management. On-premises installations require a hybrid deployment to apply the approach as is using the Azure API Management self-hosted Gateway.

Power Query requires matching API service URL and Microsoft Entra application ID URL. Configure a custom domain for Azure API Management to meet the requirement.

SAP Gateway needs to be configured to expose the desired target OData services. Discover and activate available services via SAP transaction code /IWFND/MAINT_SERVICE. For more information, see SAP's OData configuration.

Azure API Management custom domain configuration

See below the screenshot of an example configuration in API Management using a custom domain called api.custom-apim.domain.com with a managed certificate and Azure App Service Domain. For more domain certificate options, see the Azure API Management documentation.

Screenshot that shows the custom domain configuration in Azure API Management.

Complete the setup of your custom domain as per the domain requirements. For more information, see the custom domain documentation. To prove domain name ownership and grant access to the certificate, add those DNS records to your Azure App Service Domain custom-apim.domain.com as below:

Screenshot that shows custom domain mapping to Azure API Management domain.

The respective Microsoft Entra application registration for the Azure API Management tenant would look like below.

Screenshot that shows the app registration for Azure API Management in Microsoft Entra ID.

Note

If custom domain for Azure API Management isn't an option for you, you need to use a custom Power Query Connector instead.

Azure API Management policy design for Power Query

Use this Azure API Management policy for your target OData API to support Power Query's authentication flow. See below a snippet from that policy highlighting the authentication mechanism. Find the used client ID for Power Query here.

<!-- if empty Bearer token supplied assume Power Query sign-in request as described [here:](/power-query/connectorauthentication#supported-workflow) -->
<when condition="@(context.Request.Headers.GetValueOrDefault("Authorization","").Trim().Equals("Bearer"))">
    <return-response>
        <set-status code="401" reason="Unauthorized" />
        <set-header name="WWW-Authenticate" exists-action="override">
            <!-- Check the client ID for Power Query [here:](/power-query/connectorauthentication#supported-workflow) -->
            <value>Bearer authorization_uri=https://login.microsoftonline.com/{{AADTenantId}}/oauth2/authorize?response_type=code%26client_id=a672d62c-fc7b-4e81-a576-e60dc46e951d</value>
        </set-header>
    </return-response>
</when>

In addition to the support of the Organizational Account login flow, the policy supports OData URL response rewriting because the target server replies with original URLs. See below a snippet from the mentioned policy:

<!-- URL rewrite in body only required for GET operations -->
<when condition="@(context.Request.Method == "GET")">
    <!-- ensure downstream API metadata matches Azure API Management caller domain in Power Query -->
    <find-and-replace from="@(context.Api.ServiceUrl.Host +":"+ context.Api.ServiceUrl.Port + context.Api.ServiceUrl.Path)" to="@(context.Request.OriginalUrl.Host + ":" + context.Request.OriginalUrl.Port + context.Api.Path)" />
</when>

Note

For more information about secure SAP access from the Internet and SAP perimeter network design, see this guide. Regarding securing SAP APIs with Azure, see this article.

SAP OData authentication via Power Query on Excel Desktop

With the given configuration, the built-in authentication mechanism of Power Query becomes available to the exposed OData APIs. Add a new OData source to the Excel sheet via the Data ribbon (Get Data -> From Other Sources -> From OData Feed). Maintain your target service URL. Below example uses the SAP Gateway demo service GWSAMPLE_BASIC. Discover or activate it using SAP transaction /IWFND/MAINT_SERVICE. Finally add it to Azure API Management using the official OData import guide.

Screenshot that shows how to discover the OData URL within Azure API Management.

Retrieve the Base URL and insert in your target application. Below example shows the integration experience with Excel Desktop.

Switch the login method to Organizational account and click Sign in. Supply the Microsoft Entra account that is mapped to the named SAP user on the SAP Gateway using SAP Principal Propagation. For more information about the configuration, see this Microsoft tutorial. Learn more about SAP Principal Propagation from this SAP community post and this video series.

Continue to choose at which level the authentication settings should be applied by Power Query on Excel. Below example shows a setting that would apply to all OData services hosted on the target SAP system (not only to the sample service GWSAMPLE_BASIC).

Note

The authorization scope setting on URL level in below screen is independent of the actual authorizations on the SAP backend. SAP Gateway remains the final validator of each request and associated authorizations of a mapped named SAP user.

Screenshot that shows the login flow within Excel for the Organizational Account option.

Important

The above guidance focusses on the process of obtaining a valid authentication token from Microsoft Entra ID via Power Query. This token needs to be further processed for SAP Principal Propagation.

Configure SAP Principal Propagation with Azure API Management

Use this second Azure API Management policy for SAP to complete the configuration for SAP Principal Propagation on the middle layer. For more information about the configuration of the SAP Gateway backend, see this Microsoft tutorial.

Note

Learn more about SAP Principal Propagation from this SAP community post and this video series.

Diagram that shows the Microsoft Entra app registrations involved in this article.

The policy relies on an established SSO setup between Microsoft Entra ID and SAP Gateway (use SAP NetWeaver from the Microsoft Entra gallery). See below an example with the demo user Adele Vance. User mapping between Microsoft Entra ID and the SAP system happens based on the user principal name (UPN) as the unique user identifier.

Screenshot that shows the UPN of the demo user in Microsoft Entra ID.

Screenshot that shows the SAML2 configuration for SAP Gateway with UPN claim.

The UPN mapping is maintained on the SAP back end using transaction SAML2.

Screenshot that shows the email mapping mode in SAP SAML2 transaction.

According to this configuration named SAP users will be mapped to the respective Microsoft Entra user. See below an example configuration from the SAP back end using transaction code SU01.

Screenshot of named SAP user in transaction SU01 with mapped email address.

For more information about the required SAP OAuth 2.0 Server with AS ABAP configuration, see this Microsoft tutorial about SSO with SAP NetWeaver using OAuth.

Using the described Azure API Management policies any Power Query enabled Microsoft product may call SAP hosted OData services, while honoring the SAP named user mapping.

Screenshot that shows the OData response in Excel Desktop.

SAP OData access via other Power Query enabled applications and services

Above example shows the flow for Excel Desktop, but the approach is applicable to any Power Query OData enabled Microsoft product. For more information on the OData connector of Power Query and which products support it, see the Power Query Connectors documentation. For more information which products support Power Query in general, see the Power Query documentation.

Popular consumers are Power BI, Excel for the web, Power Apps (Dataflows) and Analysis Service.

Tackle SAP write-back scenarios with Power Automate

The described approach is also applicable to write-back scenarios. For example, you can use Power Automate to update a business partner in SAP using OData with the http-enabled connectors (alternatively use RFCs or BAPIs). See below an example of a Power BI service dashboard that is connected to Power Automate through value-based alerts and a button (highlighted on the screenshot). Learn more about triggering flows from Power BI reports on the Power Automate documentation.

Screenshot that shows the flow-enabled Power BI service dashboard.

The highlighted button triggers a flow that forwards the OData PATCH request to the SAP Gateway to change the business partner role.

Note

Use the Azure API Management policy for SAP to handle the authentication, refresh tokens, CSRF tokens and overall caching of tokens outside of the flow.