SQL Views not showing all fields in Managed Environment connected using Service Principal

(2) Share

Report

Posted on by NotOnUrNelly7

Hi All,

I have recently been tasked with moving an existing canvas application to a Power Platform Managed Environment due to our company switching from SQL Server Authentication to Service Principal (Microsoft Entra ID Application) Authentication.

I seem to have a problem with many of the SQL views that provide data for the Application having blank columns where there should be data (See Screenshot attached).

I ran the problem through co-pilot and the following steps have been suggested.

1) Remove and re-Add the SQL connection entirely

save the app

Close the App

Re-open the App

Re-Add the SQL connection to the view.

This worked in the when I first re-added the view but failed to work upon re-opening the App

2) Verify the target environment points to the correct SQL database - All Okay
3) Check whether the columns are in a SQL View - All Okay
4) Check SQL Permissions - All Fine
5) Clear Power Apps Cache- Done

Has anyone else had a similar experience when moving to a Managed Environment to use Service Principal Authentication?

Thanks in Advance
Jamie

Views Not Showing...

Your file is currently under scan for potential threats. Please wait while we review it for any viruses or malicious content.

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Sort by

Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @NotOnUrNelly7,

When its huge data one of the things came in mind it might be a Delegation or Filtering Issues.
Some SQL views or queries may behave differently due to delegation limits or filtering applied in the app or environment.

When using Service Principal authentication, Power Apps may not fully expand or retrieve all columns from SQL views automatically.

Can you please mention here the what is number of rows you are trying to view?

Can you please try one thing, instead of directly binding your gallery or controls to the SQL view, use the ShowColumns function to explicitly specify which columns to retrieve. This forces Power Apps to fetch only the columns you list, avoiding the partial or blank column issue.

For example:

ClearCollect( colViewData, ShowColumns( '[dbo].[YourSQLView]', "Column1", "Column2", "Column3" ) )

Note: Please replace the name with the your intended column names.

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
Suggested answer

11manish 3,333 on at

Like
a
(1)

Report
Copy link

Link copied!
This issue is most likely caused by one or both of these:

The Service Principal does not have full effective access to all underlying objects referenced by the SQL views

AND/OR

The SQL views are too complex for stable schema handling in Power Apps under Managed Environment + Service Principal authentication

The fact that the data appears temporarily after re-adding the datasource strongly indicates metadata/schema caching behavior rather than a simple app

configuration issue.

Recommended Steps

Test the SQL views directly using the Service Principal identity

Create a simplified test view and validate behavior

Rebuild the SQL connection completely

Simplify problematic views if possible

Verify all underlying table/function permissions

Avoid complex computed logic in Power Apps-facing SQL views

Was this reply helpful? Yes No
NotOnUrNelly7 93 on at

Like
a
(2)

Report
Copy link

Link copied!

@Haque Thank you for your reply.

I have pulled the VW into a collection with ShowColumns at first this did not work and the same columns where blank. I then refreshed the view in the Data Panel (left) and the Button OnSelect pulled the correct fields into the collection.

Funnily enough if I refresh the View in the data panel the Columns pull through and the Screen Gallery that is populated as expected.

The problem is I want the data to be present when the App loads or the when the screen is navigated to and this is not the case.

Also the View is not dealing with a lot of records 50 maximum.

Its a strange one and seems to be totally random.

Was this reply helpful? Yes No
Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @NotOnUrNelly7,

Data chunk seems to be very minimal. By the way - curious to know, when you poured data from VW to collection - what is the event there?

Could you please check this thead if needed?

Was this reply helpful? Yes No
NotOnUrNelly7 93 on at

Like
a
(2)

Report
Copy link

Link copied!

I have set up a very simple app that connects just to the SQL view that had columns missing and the columns are visible when I first connect.

I then close the application and go back in and the view is showing [object Object] in the columns.

Was this reply helpful? Yes No
Suggested answer

Haque 3,653 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @NotOnUrNelly7,

Can you please do one thing: Explicitly cast or convert columns in the SQL view to simple scalar types (e.g., VARCHAR, NVARCHAR, INT) to avoid complex objects. For example, use CAST or CONVERT in your view definition:

SELECT CAST(ComplexColumn AS NVARCHAR(MAX)) AS ComplexColumn, ... FROM YourTable

One more thing: Clear Power Apps metadata cache by removing and re-adding the SQL connection, but also consider restarting Power Apps Studio or the browser.

The behaviour seems like Power Apps sometimes interprets certain SQL view columns as complex objects (e.g., JSON, XML, or SQL user-defined types), and on first connect, Power Apps may flatten or parse these columns correctly. After reopening, Power Apps caches the metadata differently and treats those columns as objects, showing [object Object] instead of the actual data.

To narrow it down - can you please play around with a samll view having 2/3 columns?

I am sure some clues I tried to give. If these clues help to resolve the issue brought you by here, please don't forget to check the box Does this answer your question? At the same time, I am pretty sure you have liked the response!

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @NotOnUrNelly7,

The [object Object] on reopening confirms this is a metadata caching issue specific to how Power Apps stores the SQL view schema after switching to Service Principal auth.

Power Apps sometimes serializes columns differently when the connection uses Service Principal vs SQL auth, and certain column types (computed columns, columns with implicit conversions, or columns referencing other complex types) get stored as objects in the cached metadata rather than scalar values.

Two things to try:

1. In your SQL view, explicitly CAST every column to a basic type:
SELECT
CAST(YourColumn AS NVARCHAR(255)) AS YourColumn
FROM YourTable

This forces Power Apps to see simple scalar types regardless of what's underneath.

2. After updating the view, remove the SQL connection from the app completely, save and close the app, clear your browser cache, reopen the app and re-add the connection fresh. The key is clearing the cached metadata so Power Apps re-reads the updated schema.

The fact that it works on first connect but breaks on reopen is a metadata cache problem. The CAST fix prevents Power Apps from misinterpreting the column types when it re-reads the cached schema on subsequent opens.

Also worth checking: are any of the columns in the view computed columns or columns that reference user-defined functions? Those are the most common cause of this exact symptom.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
NotOnUrNelly7 93 on at

Like
a
(3)

Report
Copy link

Link copied!

Thank you everyone for your responses they are most helpful. I will work with my SQL colleagues to make the updates to the views and then follow the steps to reconnect and clear the cache.

I will provide an update to this chat once I have worked through.

Much Appreciated

Was this reply helpful? Yes No
NotOnUrNelly7 93 on at

Like
a
(2)

Report
Copy link

Link copied!

Valintis & Haque

Has this been documented as an issue/limitation anywhere?

Thank You

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(3)

Report
Copy link

Link copied!

Hi @NotOnUrNelly7,

Not officially documented as a specific known issue from what I can find. Microsoft docs cover Service Principal authentication for SQL in Power Apps generally but don't call out this metadata caching behavior with column types specifically.

What is confirmed in Microsoft docs is that the SQL connector and Service Principal auth have known limitations compared to standard SQL auth, and that schema handling differences exist. The [object Object] behavior with complex column types is a community-observed pattern rather than something Microsoft has formally documented as a bug or limitation.

If you want to log it officially, you can submit it via https://aka.ms/powerappsfeedback.

hat creates a formal record and Microsoft's product team can acknowledge it. Given you have a reproducible case (works on first connect, breaks on reopen), it's a solid bug report.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No