Use service principal to connect to on-prem oracle through the gateway

[Mohski]

Hello,

Has anyone sucessfully tried connecting to an on-prem oracle database using an entra ID service principal account either using data factory or dataflow gen2? Are there any limitations to this or is there a workaround that you have put together?

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi Mohski,

Thank you for your follow-up.

Based on my understanding, you may use a dedicated Oracle service account (instead of a personal user account) while configuring the Oracle data source in the On-premises Data Gateway. Kindly create a service account in Oracle (for example, svc_fabric_oracle) and configure the gateway to authenticate using its username and password. These credentials will be stored securely within the gateway.

This arrangement ensures that the service account manages Oracle database authentication, while the Service Principal continues to manage and execute your Fabric pipelines and dataflows. Consequently, there will be no direct handling of Oracle credentials by the Service Principal.

Should you find this response helpful, please consider marking it as the accepted solution and providing kudos. This will assist other members of the community facing similar queries.

Thank you.

[v-pnaroju-msft]

Hi @Mohski,

Thank you for reaching out to us via the Microsoft Fabric Community Forum.

To the best of my understanding, Microsoft Fabric and Power BI support the use of Service Principals for native connections to cloud-based data sources. However, when connecting to on-premises data sources such as Oracle through a gateway, the gateway utilizes credentials stored within itself rather than directly using the Service Principal. Therefore, a workaround is necessary wherein the gateway handles the credentials, while the Service Principal manages the permissions for the Fabric pipeline or dataflow.

At present, the Service Principal authentication type supports only the following data sources:

Please follow the workaround outlined below, which may help to resolve the issue:

1. Install and configure the On-premises Data Gateway.
2. Add Oracle as a Data Source in the Gateway, setting the authentication to use stored username and password.
3. Configure the Service Principal and grant it workspace access with either Contributor or Admin role. This allows it to manage Fabric resources, but not direct database connections.
4. Create a Linked Service in Data Factory that connects to Oracle via the Gateway. The Linked Service will use the stored credentials in the gateway.
5. Build Dataflow Gen2 or Pipelines using the Linked Service to securely retrieve data from Oracle.

Additionally, please refer to the following link for further information:
Service principal support in Data Factory - Microsoft Fabric | Microsoft Learn

If our response has been helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[Mohski]

We want to replace this "setting the authentication to use stored username and password." to a stored service account instead of a user account.