Partition elimination on SQL Server 2025 running on Azure VM querying files in Storage Account

I'm trying to get partition elimination to work when using OPENROWSET on SQL Server 2025 running on an Azure VM and querying files in an Storage Account with Hierarchical namespace enabled. I have no more ideas how to get it to work.

My starting point was a structure like this

      SELECT   
         table_parquet.*,
         table_parquet.filename() as _filename,
         cast(table_parquet.filepath(1) as date) as _date
      FR OM OPENROWSET ( -- F R O M seems to be a forbidden word in msft Q&A?
         BULK '/folder/file_*__*.parquet',
         DATA_SOURCE = 'data_source_adls',
         FORMAT = 'PARQUET'
      )
      with (
        COL_A varchar, COL_B varchar -- Both should be size 50 but msft Q&A wont let me post this if i write it like that (???)
     ) as table_parquet
      where
        table_parquet.filepath(1) = '2026-08-22'

But I could tell straight away that it wasn't eliminating based on filename, the query ran for 30seconds instead of expected < 1 second (each file contains only 2 rows). I then tried running the same query in our SQL Server 2022 Managed Instance and it ran instant. I can see in the execution plans that "Remote Scan" only returns 2 rows in Managed Instance but several hundred on the 2025 instance running on VM.

I have since tried every solution i could think of

• Partitioning by year, month and date folders as the examples show

      ...
      BULK '/folder/*/*/*/file_*__*.parquet'
      ...
      where 
        table_parquet.filepath(1) = '2026' 
        and table_parquet.filepath(2) = '08' 
        and table_parquet.filepath(3) = '22'
  

• Removing all wildcards in the filename BULK '/folder/*/*/*/file.parquet'
• Using a recommended collation in the where clause where table_parquet.filepath(1) COLLATE Latin1_General_100_BIN2 = '2026-08-22'
• Casting the filepath to varchar where cast(table_parquet.filepath(1) as varchar) = '2026-08-22' -- Should be size 12 but msft Q&A wont let me post this if i write it like that (???)
• Removing the column definitions in with()
• Removing the filename and the columns fr om the parquet files fr om the select
• Running the query against the blob (abs) endpoint instead of adls

I can see that if i hardcode part of the BULK-path that it is reflected in the rows returned fr om "Remote Scan" and runtime is being lowered by the expected amount.

Is there a setting or something that im missing? I have tried all these approached in the managed instance as well and they always work as expected there.

According to Microsoft Learn - filepath and filename this should be supported for "SQL Server 2022 (16.x) and later versions" and my tests are on 2025 (17.0.1125.2) with 170 compatibility level.

I had loads of problem posting this question, thats why there are some strange syntax with comments