Ingest Salesforce Data to Microsoft Fabric

I’m using Dataflow Gen 2 in Microsoft Fabric to ingest data from Salesforce via the Salesforce Objects connector, which is authenticated through an Organizational Account (OAuth 2.0). However, unlike Azure Synapse’s SalesforceV2 type, this connector doesn’t offer fields to input a client ID, client secret, or environment URL.

Thank you for reading this post, don't forget to subscribe!

Here are the key concerns:

1. Reauthentication Requirement

Will reauthentication be required regularly (e.g., after access tokens expire), and how often will that occur? What factors contribute to the frequency of reauthentication?

With OAuth 2.0, the system typically provides an access token (short-lived, often around 1 hour) and a refresh token, which can last longer. Reauthentication is necessary when both expire. While Dataflow Gen 2 does not allow manual token management, it should handle refreshing access tokens automatically. The reauthentication frequency depends largely on:

Expiration of the refresh token, which typically lasts 90 days but can be shorter depending on the organization’s security policies.
Conditional Access Policies: If your organization enforces stricter access controls, reauthentication might occur more frequently.

2. Cons of Using an Organizational Account

What are the potential downsides of using an Organizational Account for this connection, particularly in a production setting where automation and stability are critical?

Potential drawbacks:

Security Risks: Using an Organizational Account grants broader access than needed, violating the principle of least privilege.
Management Complexity: Managing multiple Organizational Accounts for various processes can become cumbersome, leading to potential confusion and inconsistencies.
Stability Risks: If the Organizational Account gets locked, compromised, or its permissions are altered, it can interrupt your data flows unexpectedly, especially in production environments.

To mitigate these risks, I recommend using a service account (rather than individual accounts) to centralize and secure access.

3. Workaround for Client Credentials Flow

Is it possible to implement a client credentials flow (i.e., providing a client ID, client secret, and environment URL) to prevent frequent reauthentication, similar to Azure Synapse or Data Factory? If not, what options are available for maintaining a stable, long-term data connection from Salesforce?

Currently, there doesn’t appear to be support for client credentials flow in Dataflow Gen 2. You may want to reach out to Microsoft support for confirmation. As an alternative, you could explore:

Middleware Authentication Service: Set up a service to manage OAuth tokens, refresh them, and provide an API endpoint that Dataflow Gen 2 can call. This would ensure long-term stability and avoid reauthentication disruptions.