A few weeks ago Microsoft announced the availability of SQL Server Environment variables which will be able to support your Power Apps deployments as part of an ALM Process.
These environment variables will allow us to move the configuration from one instance to another. The environment variables will have the configuration from your lower/development environment, and as part of the deployment will be moved to the higher environment. In the test/prod environment you will just need to modify the connection so that it connects to the appropriate environment by changing the value in the Environment variable. Let’s take a look at an example.
In order to get this working, we need to create a Connection to the SQL Server and two environment variables. Let’s start with the Connection first.
In your Power Apps or Power Automate maker portals, select the Connections from the Navigation menu and create a new SQL Server Connection. You can create different types of connections, but for this purpose we will be creating a Service Principal (Microsoft Entra ID application) type of connection as seen in the screenshot below. Enter your Tenant Id, Client Id and Client Secret as you have created them in Azure Entra ID.
Once the Connection has been created, we will go back to our Power Apps solution and create the two environment variables. First we will go ahead and create the Server connection. Enter a Display Name for the connection (such as Azure SQL Server). The name will be autofilled based on the Display Name. Select Data Source as the Data Type, select the SQL Server Connector, and in the Connection, select the Connection that you just created. Finally, in the Parameter type, select Server and enter the Server details in either the Current server value or the Default Value. You will be able to override these as part of the deployment. The screenshot below demonstrates how this should look like.
Next, we will create the environment variable for the connection to the database. This is almost identical to the creation of the server connection, but the difference is that in the Parameter Type we will have to select Database and the type of parameter, select the Server connection that we just created and enter the database name. The below screenshot shows the difference.
Once the connections are set, they can be used across your different environments as part of your ALM deployment process and within your different environments.
Within your Canvas application, Select the SQL Server data source to connect to your SQL Server.
In the Choose a dataset screen, click on Advanced below the SQL Server name and SQL database name (do not enter values there), and select the Server environment variable that you created.
Next, select the database environment variable that you previously created, and you will then be prompted to select the tables that you want to use for this new connection which you can use within your Power Apps application.