Dataflow Gen2 cannot write to Fabric SQL Database, error says "Data source kind: FabricSql"

[tpg0220]

# Dataflow Gen2 cannot write to Fabric SQL Database, error says "Data source kind: FabricSql is not supported"

Hoping someone has hit this and found a workaround.

## What I'm trying to do

Standard pattern. Pull a reference table from an on-premises SQL Server through a gateway, transform it in a Fabric Dataflow Gen2, land it in a Fabric SQL Database that lives in a different workspace.

- **Source:** on-prem SQL Server (`Sql.Database("CBIPRDDB", "PRODIGY-PDR-PRD")`), reachable through an existing On-Premises Data Gateway
- **Transform host:** Fabric Dataflow Gen2 (CI/CD enabled)
- **Destination:** Fabric SQL Database (`Prodigy_RLS`) in a different workspace, schema `dbo`, table `RefBrand`, update method Replace

## What happens

On Save and Run I get this immediately:

```
Error

An exception occurred: The given data source kind is not supported. Data source kind: FabricSql.
(Session ID: 5088244f-3f21-40d8-b7fa-47f26d92d788, Region: ussouth)
```

The error claims the data source kind is `FabricSql`, but the source in my M code is plain `Sql.Database` against an on-prem server. The destination is the Fabric SQL DB. So the error message appears to be misidentifying the destination as the source, or there's a wire crossed under the hood.

## Things I've ruled out

- **Not a permissions issue on the destination.** A different Dataflow Gen2 in the SAME workspace as the Fabric SQL Database successfully writes 14 tables to that same Fabric SQL DB. Same identity, same gateway, no problems.
- **Not a source connector issue.** The on-prem SQL query previews 99+ rows cleanly inside Power Query. The data lands in the canvas. Only Save and Run fails.
- **Not a Gen1 vs Gen2 issue.** I tried wrapping the source through a Gen1 MPL dataflow, hit the same error. Then went direct from SQL Server to bypass Gen1, still same error.
- **Not a connector picker mistake.** Source is the regular "SQL Server database" connector with gateway auth. Not the "Fabric SQL Database" connector.

The only thing different about this dataflow versus the one that works: this one is in a different workspace than the destination Fabric SQL DB. Cross-workspace destination might be the issue.

## My M

```
let
Source = Sql.Database("CBIPRDDB", "PRODIGY-PDR-PRD"),
MPL = Source{[Schema = "dbo", Item = "master_product_library"]}[Data],
Picked = Table.SelectColumns(MPL, {"Brand"}),
Cleaned = Table.SelectRows(Picked, each [Brand] <> null and Text.Trim([Brand]) <> ""),
Dedup = Table.Distinct(Cleaned, {"Brand"}),
Renamed = Table.RenameColumns(Dedup, {{"Brand", "entity_key"}}),
WithName = Table.AddColumn(Renamed, "display_name", each [entity_key], type text),
WithActive = Table.AddColumn(WithName, "is_active", each 1, Int64.Type),
WithLoadedAt = Table.AddColumn(WithActive, "loaded_at", each DateTime.From(DateTimeZone.UtcNow()), type datetime),
Final = Table.TransformColumnTypes(WithLoadedAt, {{"entity_key", type text}, {"display_name", type text}})
in
Final
```

Destination configured through the standard UI: Fabric SQL Database, pick `Prodigy_RLS`, schema `dbo`, table `RefBrand`, Replace.

## What I want to know

1. Is cross-workspace Fabric SQL Database as a destination from Dataflow Gen2 a known limitation? I cannot find this documented anywhere.
2. If it is a limitation, what is the supported workaround for product teams that want the source data in one workspace and the destination SQL DB in another?
3. Is there a way to get an error message that actually identifies the failing component instead of pointing at "Data source kind: FabricSql" when the source is clearly not FabricSql?

Same setup with destination set to a Lakehouse or Warehouse works fine. The Fabric SQL Database destination is where it breaks.

Capacity is F-series, region East US, dataflow is Gen2 (CI/CD).

Any pointer appreciated.

[GilbertQ]

Hi @tpg0220 

What happens if you try and write to a SQL database within the same workspace? Does it work successfully? If it does, then the cross workspace might be the issue.

[v-aatheeque]

Hi @tpg0220 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[v-aatheeque]

Hi @tpg0220 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[ati_puri · Accepted solution]

Hi @tpg0220 ,

1) There seems to no official documentation on the above issue from Microsoft, however, as Fabric SQL DB is authenticated via only OAuth2 and no SPN or workspace identity is supported, hence can cause cross-workspace conenction issue.

You can raise a ticket for this bug to Microsoft once.

Workaround- you can try using Azure SQL DB as your destination and give Fabric SQL DB endpoint -server name and database name. Use Fabric SQL DB T-SQL connection string. You can find it in settings-Connection strings-SQL server format. Authenticate it via Org account or SPN.

This method bypasses FabricSQL connection.

Let me know if the above solution works, else we can use Lakehouse to move the data to Fabric SQL DB.

Let me know if it answers above query.

Thanks

Ati Puri