Environment variables for data sources

If you’ve worked with a healthy ALM approach in the Power Platform, yet somehow still have had Sharepoint lists or SQL Server as sources in Power Apps (Especially Entra ID and Windows Authentication in SQL), you’re either a creative soul, that have built workarounds, or you’ve suffered with many others.

Recently Microsoft introduced the possibility have environment variables be of type “Data Source”, with the choice of Sharepoint, SQL Server, Dataverse and SAP ERP. This allows a healthier ALM approach to Power Apps, using those sources, as Connection references in that regard have been / are somewhat lackluster. And honestly, the implementation is quite neat.

Creating the environment variables

First off, we want to be in a solution, as environment variables live here. We will be creating two environment variables in this case, as the example uses SQL Server, one to for the server and one for the database.

Creating an environment variable, is pretty straight forward, as shown below.

After initiating the creation, we need to create a display name for the variable, as well as the Data Type. In this case, we’re going for Data Source, after which we’re prompted to select between our different connectors, and we’ll select SQL Server. Once you select the connector, it’ll ask you to specify the actual connection, which is just your usual suspects from the Connections tab. Finally, you’ll also select the Parameter Type, and in the case of SQL we’re selecting between Server and Database. If you’re working with Sharepoint, you’ll be picking between Site and List. In this case we’ll start with Server, as that comes before Database, and simply type it into the dropdown, and save the Environment Variable.

Now, we will repeat the process, but this time we’re working with the Database instead. One major difference between the two, is that the Database is dependent on a server being selected, as it can’t exist without a server, basically. Which means we’ll have another parameter to select, and then same approach with typing or pasting in our value straight into the dropdown.

Using the environment variables in an app

Now with the two environment variables created, it’s always nice to confirm that it’s working, so I’ll hop into the Power Apps canvas studio, and add a connection to SQL server in the data property, as shown below.

You’ll be met by the usual window, but for the curious individual a recently added “Advanced” tab is now on display, and selecting that Advanced tab, unlocks all the goodies for us.

Here you’ll be asked to select a server environment variable, of which we just created one, so there should be atleast one available.

Once we’ve selected the server variable, it will then present only the database variables associated with that server, which once more makes the approach simpler.

And finally, with both environment variables selected, we’ll be shown our tables and our stored procedures, in the usual manner, when connecting to SQL server

Closing thoughts

This severely easens having a healthy ALM process in Power Apps, when dealing with SQL Server & Sharepoint for instance. It’s been fine in Power Automate, as we have always been able to use text environment variables there, but canvas apps and connection references have always been a bit iffy to me. This makes it heaps easier, and also covers a broader use case. I will be using this for all canvas apps using non-Dataverse storage for the future, so I am a happy camper!