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

[Mahee]

Hi All,

Good day!

I am calling a stored procedure which is in Azure SQL MI  from Power BI to execute the logic dynamically via parameters which are binded to the columns in the table and the binded columns are used in slicer, so that when user change any inputs in the slicer it will be passed to azure sql mi via mQuery to get back the results via 'Direct Query' mode.

This method is working fine but the only disadvantage is that we are feeding the username and pwd directly inside the mQuery of each report. Can you please help to provide any info on how to handle the same scenario without directly feeding the username/pwd  inisde mQuery.

Any help on this will be much appreciated.

Example (Working mQuery) :

let
TaskNameList =
if Type.Is(Value.Type(TaskNameParam), List.Type)
then Text.Combine(TaskNameParam, ",")
else TaskNameParam,
DayRangeParamFormatted = "''" & DayRangeParam & "''",
FinalQuery =
"SELECT *
FROM OPENROWSET(
'MSOLEDBSQL',
'Server=sqlmi-******.database.windows.net;Database=******;UID=*******;PWD=********',
'EXEC *********..PowerBI_SEV_OpenTasks
@report_Date = ''"
& FromDateParameter & "'', @days = "
& DayRangeParamFormatted & ",@Task_Name = ''"
& TaskNameList & "'' ')",
Source = Sql.Database(Server, Database, [Query=FinalQuery])
in
Source

Info :

1) Calling SP (PowerBI_SEV_OpenTasks) --> Power BI to Azure SQL MI
2) Via Parameters binded to columns and used in slicers to allow user to give dynmaic inputs based on what they need.
3) Mode : Direct Query

[v-mdharahman]

Hi @Mahee,

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.

[v-mdharahman]

Hi @Mahee,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Mahee,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Mahee,
We noticed there hasn’t been any recent activity on this thread. If you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[kushanNa]

Hi @Mahee 

It seems like this user is connect with MI using direct mode , so if import mode is working for you then i guess direct mode also should work 

https://www.youtube.com/watch?v=ya3-FXkLWoc&ab_channel=AzureSQLManagedInstance 

[MohamedFowzan1]

Use Power BI’s Native Data Source Connection
Connect to your Azure SQL MI through Power BI Desktop's Get Data > Azure > Azure SQL Database or Managed Instance connector. Enter your credentials securely once during connection setup. This way, Power BI manages authentication, and you do not include credentials within your queries.

Parameterize Your Stored Procedure Call Without Embedded Credentials
Create Power BI parameters for the inputs you want to pass to the stored procedure from slicers or filters. Use these parameters in your M query like this:

let
    Param1 = Text.From(YourParameter1),   // Parameter from slicer or user input
    Param2 = Text.From(YourParameter2),  
    Source = Sql.Database(
        "yourserver.managedinstance.windows.net", 
        "yourdatabase", 
        [Query="EXEC yourStoredProcedure @param1 = '" & Param1 & "', @param2 = '" & Param2 & "'"]
    )
in
    Source

[Mahee]

Hi @MohamedFowzan1 ,

Will it work for Direct query mode please?