Calling Stored Procedure which is in Azure SQL MI from Power BI to execute the logic dynamically.

Hi Anonymous,

Thanks for reaching out to the Microsoft fabric community forum. You are right to be concerned about embedding usernames/passwords directly in the M query, that’s not secure.

If you connect to your Azure SQL MI using the native SQL connector in Power BI, you don’t need to include credentials in the query at all. Power BI will securely manage authentication for you (either through your login, or via a service principal when you publish to the Service). And regarding DirectQuery you cannot build dynamic SQL text inside M queries (like concatenating strings with EXEC ...). That approach forces Import mode.

The correct way in DirectQuery is to use Value.NativeQuery, which allows you to call your stored procedure with parameters, while still letting Power BI push queries down to SQL MI. For example:

let
   Param1 = Text.From(YourParameter1),
   Param2 = Text.From(YourParameter2),
   Source = Value.NativeQuery(
        Sql.Database("yourserver.managedinstance.windows.net", "yourdatabase"),
        "EXEC dbo.YourStoredProcedure @param1 = @p1, @param2 = @p2",
        [p1=Param1, p2=Param2]
   )

in
    Source

This way no credentials are stored in the query (Power BI manages them securely). It works in DirectQuery mode and parameters from slicers can still be passed down to the stored procedure.

So to answer in short yes, it will work in DirectQuery, but you’ll need to use Value.NativeQuery rather than string concatenation.

I would also take a moment to thank kushanNa and MohamedFowzan1, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.