Power Apps: Read-Only Data Source with Azure SQL Database

Power Apps: Read-Only Data Source with Azure SQL Database
Reading Time: 2 minutes

Hello Power Platform! This is my first Power Apps post!

Working almost exclusively with Microsoft Power BI and Azure tools, I traditionally had not found much use for Power Apps. That has changed recently as I’ve had the chance to learn more and start building canvas apps. As someone who enjoys creating unique content in Power BI that’s always “one step away from useful” and not what Power BI was intended for, I now feel like I’ve missed out on a few years’ worth of opportunities. I should have started learning Power Apps long ago.

In any case, I’m using it now, and I think that I’ll focus on blogging about entry level topics that match my experience. These “solutions” probably won’t be much of a surprise for people who have been involved with Power Apps for awhile though.

Unlike blogging about Power BI here on DataVeld, where I feel more comfortable, it’s a stretch to consider anything I will write about Power Apps ‘expert-level’. Seasoned Power Apps makers should find my topics a bit juvenile and likely a regurgitation of common knowledge. Being new, however, nothing is common knowledge to me yet. Blogging helps me reinforce my own learning. If writing about entry level Power Apps topics also helps other beginners like me–especially those coming from Power BI–I consider it worth the effort.

SQL Connector – “The Data Source is Read-Only”

Recently when working with a table in Azure SQL Database, I came across an issue that manifested itself in Power Apps in a few ways.

The message stated that the data source is read-only, when I knew that my SQL credentials had the correct permissions to insert and update table records. I was successfully doing this with other tables, so SQL was not the issue.

Attempting to use functions like Remove and Patch provided the message, as did Form.Edit.

In fact, I was initially confused and first noticed that something was amiss when I inserted an Edit form and the text inputs did not automatically appear. A sample form like the one shown below was a bother because there was no explicit error message or indication why I didn’t see what I expected.

Getting into formulas started providing a clue as to what was going on relative to the data source. For example, using Remove in a button at least gave me a message stating the data source is read-only (which I knew was not true).

The solution was not what I would have expected based on the message Connecting to Azure SQL Database from Power Apps using the SQL Server connector requires a primary key on the table.

After altering the table and adding a primary key, the various write capabilities became available after refreshing the connection in Power Apps.

1 Comment

Leave a Reply

%d bloggers like this: