Can I create a Model Driven app with Azure SQL

Question

Is it possible to create a model driven app and have the CRUD operation directly connecting to Azure SQL Server?

Answer 1

You could create a Canvas app connecting directly to Azure SQL You could make a Model-Driven app, but you will need to integrate the Dataverse with the SQL database

You could also see if Virtual Tables are a viable option.

The documentation is available here https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve

From the above link, here are some general information: (TL;DR - Virtual Tables might work for you in a Model-Driven app, see the link for details about what they are, how they work, and the limitations)

A virtual table is a definition of a table in the Dataverse platform without the associated physical tables for records created in the Dataverse database. Instead during runtime, when a record is required, its state is dynamically retrieved from the associated external system. Each virtual table type is associated with a virtual table data provider and (optionally) some configuration information from an associated virtual table data source.

The following data providers ship with Dataverse:

• An OData v4 provider is included with the service and is installed by default. This provider supports create, read (retrieve, retrieve multiple), update and delete operations.
• An Azure Cosmos DB (formerly Microsoft Document DB) provider is available from AppSource.

If a data provider cannot be found for your external data source, you can develop a custom virtual table data provider; for more information, see Virtual table data providers. Full CRUD operation is now supported for custom virtual table data provider. Developers can implement plug-ins and register them using the Plug-in Registration tool for each of the CRUD operation supporting the virtual table.

Following are the limitations in virtual tables that must be considered.

• Only organization-owned tables are supported. The security filtering applied to user-owned tables is not supported. Access to the virtual table data can be turned on or off for individual users based on their security role. Field-level security is not supported.
• It must be possible to model the external data as a Dataverse table. This means:
  • All tables in the external data source must have an associated GUID primary key.
  • All table properties must be represented as Dataverse table columns. You can use simple types representing text, numbers, choices, dates, images, and lookups.
  • You must be able to model any table relationships in Dataverse.
  • A column on a virtual table cannot be calculated or rollup. Any desired calculations must be done on the external side, possibly within or directed by the data provider.
  • Although you can add virtual table columns as a lookup on a grid or other UI views, you cannot filter or sort based on this virtual table lookup column.
• Auditing is not supported.
• Search functionality is not supported for virtual tables as they do not persist data.
• Charts and dashboards are not supported for virtual tables.
• Virtual tables cannot be enabled for queues.
• Offline caching of values is not supported for virtual tables.
• A virtual table cannot represent an activity and do not support business process flows.
• Once created, a virtual table cannot be changed to be a standard (non-virtual) table. The reverse is also true: a standard table cannot be converted into a virtual table.
• Selecting attributes in Retrieve and RetrieveMultiple queries will not be applied since all attributes will be returned

Answer 2

I disagree with Alex's suggestion. You are basically asking if you can connect the model-driven UI to a different SQL server that is not Dataverse. The short answer is "no" because it would cost you more than just writing it from scratch and you would be bypassing the entire security model. If your intention is to get full control over the data in SQL, then just buy D365 for on-prem and you can do what you want with the SQL server from there.