Warehouse connection in scheduled pipeline breaks after deployment

[DarSz]

Hi, we have a process of deploying from dev to prod using fabric-cli and Azure DevOps.

After a successful deployment, our scheduled pipelines start returning errors in the Script and Lookup activities. These activities are connected to a Warehouse item. Here is the error:

Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=Cannot connect to SQL Database. Please contact the SQL Server team for further support.
Server: 'serverid.datawarehouse.fabric.microsoft.com',
Database: 'databaseid', User: ''.
Check that the connection configuration is correct and that the SQL Database firewall allows
the Data Factory runtime to access it.,
Source=Microsoft.DataTransfer.Connectors.MSSQL,''

''Type=Microsoft.Data.SqlClient.SqlException,
Message=Login failed for user '<token-identified principal>'.
Reason: Authentication was successful, but the database was not found
or you have insufficient permissions to connect to it.,
Source=Framework Microsoft SqlClient Data Provider,''

As far as I understand, the pipeline loses the user context, which is why the user field is blank in the error.

When I run the pipeline manually, everything works — Script and Lookup activities start working as intended. Even the scheduler works fine until the next deployment, when the issue appears again.

I tried creating SQL users and adding retries to these activities, but nothing seems to help.

Any ideas?

[v-prasare]

Hi @DarSz,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @DarSz,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[spaceman127]

Hi @DarSz ,

here is an Video from Aleksi Partanen and in my optinion he describe your problem.

https://www.youtube.com/watch?v=s5cmFr3uOZ4

@v-prasare  post with the link describes it quite well. However, you can follow it easily in the video.

You can test it whether this way is functional.

I believe the connection was interrupted after deployment. This also explains why it continues to function with manual execution.

I hope that is helpful.

Best regards

Rene

Feel free to leave kudos or accept it as a solution. This will also help other community members.

[DarSz]

Thank you @spaceman127 and @v-prasare for your replies.

I followed links provided by you, but I believe my problem is somewhere else. Pipeline works fine. The issue is probably with connection in my Lookup activities. It's based on Warehouse connection type, and the only available authentication method is OAuth with my own credentials.

After Service Principal takes over Fabric pipelines it breaks, and when I take over this pipeline again it starts working.

So it's rather issue with my configuration. But can you tell me what kind of connection type do you use with your warehouses? Because creating seperate connections for every warehouse in my Fabric doesn't sound good.

[spaceman127 · Accepted solution]

Hi @DarSz ,

ok.

In my case i use Library variables for this. 

Also you can use parameters for the connection.

In the connection tab, you can enter the name of your warehouse. I do this here using variables that I define.

Here ist an example Screenshot for my Lookup Activity with Library Variables.

Best regards.

[v-prasare]

Hi @DarSz,

If this issue is blocking your workflow or impacting scheduled executions, we recommend raising a Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

Please include the relevant diagnostics (failed scheduled run vs. successful manual run, Pipeline Run ID, Workspace ID, and Warehouse ID) so the product team can investigate the deployment‑related behavior in detail.

@spaceman127, thanks for your prompt response here.

please do update your findings here, this helps community members with similar queries.

Thanks,

Prashanth

[spaceman127]

Hi @DarSz ,

Question:

Once you have completed the deployment, do you first have to correct the connection again, or will the pipeline still work properly if you do this manually?

If I understand correctly, you don't need to repair the connection, right?

Best regards 

Rene

[DarSz]

Hi, no, I don't have to correct the connection. It just works when I run pipeline manually.

[spaceman127]

HI @DarSz ,

Thanks for your reply.
I'll try to reproduce your problem over the next few days and then get back to you. However, I already have a hunch. I think the identity breaks after your deployment.

I'll let you know.

Best regards

Rene 

[v-prasare]

Hi @DarSz,

This behavior might not related to SQL firewall rules or retry handling. After a Dev to Prod deployment using Fabric CLI, scheduled pipeline runs seem to execute under a system or workspace identity, while manual runs execute under the interactive user identity. The deployment may reset or detach permissions and identity bindings, which can leave the system identity without access to the Fabric Warehouse even though authentication itself succeeds. This can surface as “login successful but database not found” or “insufficient permissions” errors. Manual runs may continue to work until the next deployment, when the same permission reset happens again.

let me know if this helps to resolve your issue. refer to below community similar threads
Solved: Can we run Data Pipelines with Workspace Identity? - Microsoft Fabric Community

Thanks,

Prashanth