What values are used to parameterize connections to Data Warehouse in Fabric Pipeline?

[arpost]

Greetings, all. It seems the Fabric Pipeline UI/behavior has changed the way you set up dynamic connections to Data Warehouses. I need to change the connections when deploying to Test/Prod workspaces via deployment pipelines. Historically, DWs would rebind in the next stage, but that looks to have changed as deployed pipelines now keep their previous stage's objects. Example below is of Lookup activity, but this applies to related activities also.

I had assumed the following (this is how it worked previously):

1. Connection = Data Warehouse GUID
2. Workspace ID = Workspace GUID
3. Warehouse ID = Didn't exist; I'm assuming it is now the Data Warehouse GUID
4. SQL Connection String = Data Warehouse Server

However, I get errors on the Connection when I use the DW ID. I thought then that I needed to create a connection like I've done with Fabric SQL DB. Turns out there's no Fabric Data Warehouse connection type. Things I tried:

1. There IS a Warehouse connection, but that doesn't work when I try to use a service principal.
2. I tried using the Fabric Data Pipeline type with workspace identity, but that also didn't work.

I even set up a Variable Library and brought those values in using input from #2 without success:

Got an error saying the following:

The value of the property '_partnerResourceProviderUrl' is invalid: 'Value cannot be null.
Parameter name: _partnerResourceProviderUrl'.
Value cannot be null.
Parameter name: _partnerResourceProviderUrl

Anyone have suggestions on how to get this to work? Really need to be able to change the connections to Test/Prod DW objects on deploy.

UPDATE: Realized I didn't have my workspace identity created yet. Progressed to next stage before failing. I tried setting the Warehouse ID to both the DW GUID and the DW DB name when I saw the error message used the Warehouse ID property in the Database spot.

[apturlov]

@arpost Why do you think Fabric prevents connections from being shared? You absolutely can control which users can use connections if you are a Fabric admin. There is no need in sharing creds with anyone. Take a look at this documentation to learn how to manage users access to connections Data source management - Microsoft Fabric | Microsoft Learn.

If you think this conversation helped you find a solution feel free to give kudos or accept answers as a solution.

[arpost]

There was a time when OAuth2/Entra connections were shareable (that was default for majority of the time I've used Fabric), but there was a change at some point in the Fabric UI a while back that prevented the sharing of an Entra connection with an error stating this was a security problem (since sharing Entra connection = letting people do things as if they were that user account); other Authentication methods didn't have this block and were shareable. I ran into this change several months back.

That said, I just tested with one of my OAuth2 connections (not on DW since I can't test that yet) and found that these do indeed look to be shareable again. I'm glad to see this unlocked, though it is definitely something of a "with great power comes great responsibility" feature since that let's devs perform operations under the guise of the shared account.

[arpost · Accepted solution]

UPDATE: I just tracked down at least one place where I ran into this before. It was the Outlook 365 Entra connection type and gives the following error:

Sharing this connection type is prohibited for security reasons.

It is entirely possible I misinterpreted this error as being a block on Entra sharing when it was actually Outlook connection sharing. If true, that's months of my life my team and I could have been using a different connection methodology I'll never get back...

[v-ssriganesh]

Hi @arpost,

Thank you for the update. We appreciate your efforts in investigating this further and are glad to hear that the issue has been resolved.

Your findings and clarification are valuable and will certainly help other community members who may encounter similar behavior or error messages.

Thanks again for taking the time to share the resolution.

[apturlov]

@arpost I did an experiement and based on a combination of error messages and the final successful pipeline run below is the explanation. Here is my setup for the experiment that mimics your setup:

I parameterized what I thought made sense. For example, Connection type here is hardcoded but it can be a string parameter (which would not make sense if you target a specific storage type). Workspace ID is a Guid and can be parameterized. Other parameters:

Connection: GUID - a connection Id (from the Settings/Connections and Gateways)

Connection type: "Warehouse"

Workspace ID: GUID - a target Warehouse ID (from the Url)

SQL connection string: a string, actual connection string of the target Warehouse (from WH settings)

Schema: string

Table: string

For my experiement I used pipeline variables with default values for simplicity, but I could have used Variables Library with the same effect and even define values per environment (target workspace).

With this setup the Lookup step completed wihout errors which proves all the parameters were correct:

As a secondary proof, the pipeline input from resolved variables looked like this:

Hope this helps to answer your question and clarify how to parameterize Data Warehouse connection in Factory pipeline.

[arpost]

Thanks for the reply, @apturlov. What Connection Type and authentication method (e.g., OAuth2, Service Principal) did you use when creating your Connection in Connections & Gateways?

[apturlov]

@arpost For Warehouse and Lakehouse only OAuth 2.0 authentication with organizational account is currently supported as explained here Data Warehouse connector overview - Microsoft Fabric | Microsoft Learn. It's kind of a long standing discussion in the forums how to not use a personal organizational account in the Warehouse/Lakehouse in Factory pipeline activities. Activities like Copy, Lookup only support OAuth 2.0, unfortunately. My current suggestion is to create a non-human organizational account for such activities one per each environment and use it in connections created per environment as well. But this is really a different question.

[arpost]

Regarding the "how not to use personal org account" piece, @apturlov, that's what I was afraid of. The previous DW connection behavior in pipelines essentially allowed any developer to pop in to a pipeline without having to have an explicit connection shared with them, thus providing a kind of "user-agnostic" connection behavior.

While I haven't been able to create a Warehouse connection to confirm, I am assuming this new behavior will be the same as with other connections, which means pipelines will be forced to use user-specific Entra connections for DW that cannot be shared like other connections (prevented by Fabric UI and also just makes sense from a security standpoint).

Basically, functionality and Enterprise scalability will be effectively lost until Microsoft adds support for service principal connections.

[apturlov]

@arpost while I understand and agree with your concern in general, I would not characterize the situation as "effectively lost". It's an inconvenience, of course, and a deviation from established security patterns considered best practices, but there are workarounds that do not introduce any particular high risk. You can create a new Entra ID organizational account that does not represent a person but is reserved for using with Fabric connections. You manage the account password according to your organization policy and save it in a secure secret storage. You assign Fabric access permissions to that account according to your requirements to specific WH/LH in specific workspaces, and finally you create a new Fabric connection using that account and share the connection with other Fabric developers/engineers. The team members don't need to know the account credentials because they will be using a connection instead. From the usage stand point it would be very similar to using a service principal account. That's my 2 cents. Hope this could help.

[arpost]

This gives me some hope. I had run into issues with normal connections previously where developers couldn't interact with pipelines because they didn't have shared permissions on the connection. If they tried to perform actions (e.g., preview data), they'd get an error since their accounts hadn't been granted access (e.g., User rights).

Since Fabric prevents Entra connections from being shared now, that made Entra-scoped authentication a non-starter in the past. Can't go sharing creds for the same service account with all developers and have them use the same SA to build pipelines concurrently.

I'll do some testing once we get the issue in our environment rectified with MS.

[arpost]

Thanks for the reply, @apturlov. Assuming you're using the Warehouse connection type, this is what I get when I try to create that connection:

[apturlov]

It seems you may have authentication issues in your environment. I just created another new Data Warehouse connection with no issues.

[arpost]

Thanks for confirming, @apturlov. I'm working with MS support on this. Multiple accounts in our org get different errors when trying to create these, so I'm guessing they need to rectify something under the hood.

[apturlov]

@arpost which pipeline activity are you trying to configure a connection in? Is this connection for a data source or destination?

[arpost]

@apturlov, I'm specifically using a Lookup, but there are other data-related activities in view (Script activity, Copy activity). Previously, this was all that was required in pipelines to leverage dynamic content, and it worked just fine:

I am guessing these changes were related to a broader implementation of the "Dynamic Connection" capability that allows us to supply a Connection ID.