MongoDB Atlas SQL connection using 2 connections with 1 data gateway problem

[nbustos]

I have a problem when I try to open my published report in Power BI service using two connections and one single data gateway.

Context: I have a MongoDB Atlas DB exposed through a PBI Gateway. Inside Mongo there are two collections of which I need to use one table from each collection, so I created two data connections through the single gateway. In PBI Desktop it works perfectly and I can create visuals without any problem using two different collection tables in the same report, but when I publish this report to my workspace and open it I get the following message:

Couldn't load the model schema associated with this report. Make sure you have a connection to the server, and try again.

Underlying Error: PowerBI service client received HTTP response error. HttpStatus: 400. PowerBIErrorCode: DM_GWPipeline_Gateway_InvalidConnectionCredentials

Microsoft.Data.Mashup.CredentialError.Reason: CredentialMissing

Underlying error message: Credentials are required to connect to the MongoDBAtlasODBC source.

I have tried to publish a report where I only load one collection and use only one connection and it works, so I understand that it is not a credentials problem. Is there some kind of limitation between the Power Bi connection and Mongo or am I just missing something that I am unable to see?

[v-mdharahman]

Hi @nbustos,

Thanks for reaching out to the Microsoft fabric community forum.

Based on what you've described, the issue seems to be related to how Power BI Service handles multiple connections to the same data source (MongoDB Atlas in this case) via ODBC and a gateway.

This behavior isn’t a MongoDB-specific limitation it’s how Power BI handles ODBC and gateway connections in the service. If the connection strings aren’t exactly identical, Power BI sees them as separate and requires separate credential configuration.

In Power BI Desktop, you're able to define separate connections for each collection without issue, but when the report is published, the service expects each unique data source (including different connection strings) to be configured and credentialed individually in the gateway settings.

Please try these steps to fix the issue:
* Go to the Power BI Service -> Manage Gateways. Under your gateway cluster, you should see both connections listed separately, even if they’re pointing to the same MongoDB server, Power BI treats them as separate if their connection strings differ in any way (database name, collection, parameters, etc.).

* Make sure both data sources are configured and credentials are set. For each listed connection click "Edit Credentials" then re-enter the correct authentication details (likely basic or username/password). Make sure privacy level settings are consistent for both connections.

* Verify that your dataset in the workspace is mapped to the correct gateway and both data sources.

I would also take a moment to thank @Irah24, 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.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[nbustos]

Hi @v-mdharahman, thanks for replying to my problem!

I can see both connections separatedly but in the same gateway and working correctly:

Also in the report semantic model, I can see both connections separated and working too:

The credentials of both connecionts are right because I have been testing using only one connection with both of them and it works without any problem.

Even with all this, still not working. 

[v-mdharahman]

Hi @nbustos,

Since both MongoDB ODBC connections are correctly configured, mapped, and online in your gateway, and the semantic model is detecting and mapping them properly, we can rule out basic credential or mapping issues.

From here, a couple of deeper things to check:

* Check privacy level settings. Even though this is more commonly an issue in Power BI Desktop, the service still enforces data isolation rules. Please verify the privacy levels on both connections in the gateway settings. Go to Manage Gateways > Each connection > Edit credentials. Make sure both have the same privacy level (e.g., Organizational or None). A mismatch here can silently cause issues with data combination.

* Re-publish with reconfigured source (Same DSN). Since you're connecting to two collections from the same MongoDB server, try this as a workaround. In Power BI Desktop, edit the queries so both collections use the same DSN/connection string, and branch off from there. This may mean querying the full database and then filtering by collection/table within Power BI. This helps Power BI treat the data as coming from a single source, avoiding multi-connection schema confusion in the service.

* Make sure your on-premises gateway is running the latest version. An outdated gateway has been known to cause DM_GWPipeline_Gateway_InvalidConnectionCredentials errors with custom connectors or ODBC drivers.

If all of the above are verified and it’s still not resolving, it could be a limitation or bug in how Power BI handles multiple ODBC Mongo connections through the gateway. In that case, I’d recommend raising a support ticket with Microsoft directly.

Please refer below link on how to raise a contact support or support ticket.
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Best Regards,

Hammad.

[v-mdharahman]

Hi @nbustos,

I wanted to follow up on our previous conversation. Has your issue been resolved or were you able to raise a support ticket with Microsoft Fabric Support?

Your feedback would be greatly appreciated, as it can help us better understand and address similar issues in the community.

Thank you.

[nbustos]

Hi @v-mdharahman !

I followed your instructions but it's not working, I will now contact with support team. As soon as they respond me, I will let you know. Thanks for the help!

[v-mdharahman]

Hi @nbustos ,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you.

[v-mdharahman]

Hi @nbustos,

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 and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Irah24]

Yes am using in Native Query 

am getting 1007 column is not specified as it is array of objects  and one lead may have many quotation and many leads will have blanks how to resolve this??

[nbustos]

Error 1007 indicates that the status field does not exist in all objects in the quotations array, or that quotations is null or empty in some documents.

To avoid this, make sure the field exists before accessing it. For example:

IF(quotations IS NOT NULL AND quotations.status IS NOT NULL, quotations.status, NULL)

Also check that there are no name errors in the fields.

[Irah24]

Tried with case

SELECT
_id,
CASE WHEN quotations IS NOT NULL AND quotations.status IS NOT NULL THEN quotations.status ELSE NULL END AS quotation_status,
CASE WHEN quotations IS NOT NULL AND quotations.totalAmount IS NOT NULL THEN quotations.totalAmount ELSE NULL END AS quotation_amount

FROM UNWIND(
leads WITH PATHS => (
quotations[OUTER => TRUE, INDEX => quotation_idx]
),
INDEX => idx,
OUTER => FALSE
)
Direct If Condition is not working so tried with CASE WHEN but also same error 
CommandError { code: 96, code_name: "OperationFailed", message: "failed getting result set schema: algebrize error: Error 1007: Cannot access field `status` because it could not be found. below is my Schema 

quotations
Array (1)

0
Object

status
"Yes"
totalAmount
10

[Irah24]

am getting  Error 1007: Cannot access field `status` because it could not be ,

but in my Database it status column there if i use only totalAmount than showing error totalAmount not dound as , quotations is a arrayof object it may be null also but am not getting correct output

[Irah24]

Hi @nbustos , 
am having few problems while connecting with MongoDB Atlas SQL if i connect through Import and Transform the JSON file in power query mode than the power bi is taking very long time to Refresh as i have used pivot , groupby how to resolve this ,

and if i connect via Direct Query it is fast but am able to FLATTEN and UNWIND the few columns like k and v array of objects , but unable to to extract Array of object specific column am getting error as 1007 column not found i tried many ways but not getting correct solution , can you please guide me which method is good 

[nbustos]

Hi @Irah24,

If you want to extract values from an array using DirectQuery mode, I recomend you to use SQL native query when you first load data (that's the solution I'm applying). Here's where you can write SQL code:

For extracting array values, only you need is to write this:

SELECT
          array.value1,
          array.value2,
          array.value3,
          var1,
          var2

Hope this can help you!!

[nbustos]

Also important to mention that I'm using DirectQuery, because when I publish the report with import mode it works well, but I need automatic page refresh function, so DirectQuery mode is obligatory for me.