Shortcut table not showing in SQL Endpoint nor Dataset

[jpthoma7]

Hello.  I am able to successfully establish a shortcut from one lakehouse to another.  I am the owner of both and both exist in the same workspace (also owner there).  

I can see the data in my lakehouse view but it doesn't appear in my dataset nor my SQL endpoint.  The lakehouse was created in the workspace as part of the Dataverse sync.

I am currently testing and troubleshooting which is why I have the permissions amped all the way up but I feel I have eliminated all of the possible authorization reasons?!  Any other possibilities?

[charrington]

Hi guys,
any update in this? I have the same thing here.

It seems to me it's either a limitation or bug at this point. I have this scenario:
-LH1 has shortcut to ADLS Gen2 table, which is showing in SQL endpoint, fine.

-LH2 has shortcut to OneLake LH1 same table (virtual with shortcut). That doesn't show in SQL endpoint.

-LH2 has shortcut to OneLake LH1 to some other physical table. That shows in the SQL endpoint. I tested also OneLake shortcuts from other physical tables from other warehouses, they all show in SQL endpoint.

So in my case it seems only when adding a shortcut from a Lakehouse, which is itself a shortcut, this doesn't show in SQL endpoint. But still the wizard makes it available, so it should work in theory, meaning to me it's more a bug than anything else...

I added both in the LH2 to double check. 1st one here is shortcut from ADLS, and 2nd is OneLake shortcut referencing the same table (which is clearly parquet as you can see):

But in the SQL view I see only the 1st one, althought they reference the same exact table on ADLS Gen 2 !!

Why??

@v-cboorla-msft I think in your case it's working because you do shortcut to some physical table inthe Lakehouse. If you try to shortcut to some existing shortcut, you should not be able to see them on the SQL endpoint.
Please confirm.

thanks

[FelixL]

I reported this to MS support, who verified the issues and patched it during the November 11 release. After this, it worked fine (again) for me for a few weeks. However, the isse returned last week, and shortcuts to external tables in other lakehouses are now missing in the SQL endpoint again. 

I have reported this to MS again (yesterday) and they are now investigating. I will update you here once I hear more. 

However, it appears to me that MS does not really know themselves if this should or should not work. I get conflicting communication back from support, sometimes referring to the fact that shourtcuts to external AGLS Gen 2 tables are not supported (at all), and sometimes saying this is a bug and that they will investigate. 

OneLake shortcuts - Microsoft Fabric | Microsoft Learn

My view on this is that it would be very unfotrunate if this is not supported/will not be supported. That would mean that using external tables (in my case DeltaLake tables created in Azure Synapse Analytics) would relegate me to a second grade fabric citizen, not able to use the most basic functionality of shortcuts throughout the business. 

Another issue related to this has recently been introduced; if a user tries to create the OneLake shortcut to an external lakehouse table (ADLS Gen 2 DeltaLake table) they must have the underlying cloud data connector shared with them, else they receive an error.

This also goes against the whole idea of using the OneLake security model for sharing data - since we really dont want to share the underlying data connectors (containing credentials and root access to data lake) with all business users, since they could then in practice bypass the entire security model by creating their own external shortcuts to whatever data they have access to through the cloud connectors. 

This was not the case until Fabric went GA - before GA all users could create shortcuts to tables in all lakeohuses shared with them, regardless if they had access to the underlying data connector. This more correlated with how I see users working with Fabric's security model; sharing of data sohuld be done on Lakehouse / object level - not also on underlying data connectors.

This has also been reported as a bug to MS support. 

This all goes to say that MS does not seem to really know what will work and what wont work with external shortcuts. And as it now stands the functionalit is at the very least bugged/broken... 

[charrington]

Thanks for the additional info!
The limitation you highlighted makes no sense to me, that article talks in detail on how ADLS shortcuts work, so how can they be not supported?? Looks more like a documentation not up to date from Microsoft. 

Yeah some of the Fabric stuff is still in the early stage, even though it's GA. I'm sure they'll improve it over time, we need to be patient.

[12angrymentiger]

That's what I'm thinking. It doesn't make any sense to not support ADLS Gen 2 Delta Shortcuts. That's the whole point of the OneLake shortcuts. 

I was informed by a Microsoft Support Engineer that the deployment for the fix went out last Friday. @v-nikhilan-msft  Mentioned there is a fix in place. Hopefully it either gets rolled out further or there is another issue that they are working on.

[FelixL]

I just got verified from MS support who checked with the responsible persons in the product team (regarding the limitation of having to share the cloud connection); this is apparently intended behaviour - and wont be fixed. 

I'm very dissapointed by this answer, as it shows that no real thought has been spent on the practical usage of external shortcuts.

As mentioned above, we now find ourselves in a situation where we either have to create one cloud conneciton per external delta lake table, and manage sharing of these connections (thousands of connections) manually to allow users to create shortcuts to the data..

..or we use one top level cloud connection (as we do now), and share this with the entire organization, and live with the fact that all users in fabric can bypass any and all table security (object level and RLS) by simply mapping their own external shortcuts pointing directly to the ADLS Gen 2 delta lake tables.

We faced similar security issues in Synapse serverless, where all users who had the Synapse workspace managed identity credential shared with them could actually bypass all table security if they knew the storage layer and wrote their own OPENROWSET queries. The response from MS 6 months back when pointing this out was that it wouldnt be fixed. When Fabric got released I was hopeful that the external shortcut support in Lakehouses would fix this issue - but the very same security and usability issues just seem to have been carried over 😞 

[Ostrzak]

Hi everyone,

The suggestion proposed by @DennesTorres was interesting but unfortunately it is not the case. I have a delta table as a shortcut and sql queries don't work on it, although you can display it.

I'm not sure if it worked before Monday as @FelixL mentioned. Would be great if it was fixed since the only solution I can think of would be to copy the file between the workspaces.

Thank you in advance for your help.

[FelixL]

I have reproduced this issue in multiple capacities and multiple lakehouses. Whenever the original source (Lakehouse #1) of the delta table is external (in your case a lakehouse generated by Dataverse/Synapse. Link, in my case external Delta tables on ADLS Gen 2) the tables are missing in the SQL endpoint in Lakehouse #2, #3, ...

I have also seen that the spark tables in Lakehouse #2, #3, ... are are experiencing issues if you link more than a few to the same Lakehouse. They start moving between "tables" and "unidentified" in the target Lakehouse randomly; forcing you to frequently refresh the tables in order to be able to query them. This also only seem to be the case when the source table is external (not managed in Fabric). 

Both my issues has been forwarded to a developer/product team for further investigation. I will keep you posted here if/when I hear more. 

[DennesTorres]

Hi,

I did shortcuts between delta tables in lake houses and datawarehouses with no problem, I even created a video, not published yet.

Your delta table may have something different/special to be failing. Try with a simple one between lakehouses for test purpose, then try to make them more similar as possible until you discover the difference which makes it fail.

Kind Regards,

Dennes

[FelixL]

I am experiencing the exact same issue. I have multiple lakehouses set up that refer to Delta Lake tables on adls Gen 2 via shortcuts. These lakshouses all work fine, and I can see the tables in the spark and SQL endpoint view. 

I also have multiple other lakehouses with oneLake shortcuts pointing to my primary lakehouses. In these secondary lakehouses none of the linked tables are visible in the SQL endpoint or default dataset. 

This all worked flawlessly until this Monday, when all of the tables disappeared. I have logged a ticket at MS, and will keep you posted when I hear anything… 

[DennesTorres]

Hi,

Just a suggestion: Everytime I got something appearing in the lakehouse view but not the sql endpoint view it was because of a wrong format on the files or data type of the columns.

The SQL Endpoint is very selective, even if something is under "Tables", if it's not in Delta format, it will not appear in the SQL endpoint. Try "View Files" to confirm the format.

Kind Regards,

Dennes

[v-cboorla-msft]

Hi @jpthoma7 

Welcome to Microsoft Fabric platform and thanks for posting your question here.

As I understand that you have successfully established a shortcut connection from one Lakehouse to another Lakehouse in the same workspace. You can able to see the data in Lakehouse view but can’t see the same in dateset nor SQL endpint.

While I tried to repro the same scenario from my side, I am able to see the data in SQL endpoint as well as in dataset. Find the screenshots for reference.

Data in the first Lakehouse:

Data in the second Lakehouse (shortcut of first Lakehouse):

Data in the second Lakehouse (SQL Endpoint):

Data in the second Lakehouse (Dataset):

Could you please send me a screenshot of the error message you are getting?  This will help me to troubleshoot the issue and I can understand the problem better and help you.

I hope this information is helpful. Please do let us know if you have any questions.

[v-cboorla-msft]

Hi @jpthoma7 

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

[v-cboorla-msft]

Hi @jpthoma7 

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.