Unanswered

ALM : SQL Server AAD : change server name and database name for SQL Server AAD across environment

(1) Share

Report

Posted on by DavidForlen

Hello,

I'm trying to replace the direct SQL Server connector with SQL Server AAD connector for my canvas apps and flows regrouped in differents solutions.

As this new connector doesn't save the server name and database name in itself, exporting canvas app and flow to the production environment doesn't change the server and database used. And if a different server and database was specified in the production environment, it's replaced at the next import.

How can I make sure that my production environment uses the SQL production database, and my development environment another SQL database, without having to modify each Flow and Canvas App after each import ?

For Flows, I may use environment variables, but I don't see how to proceed with the Canvas App, as the SQL connection can't use a variable for server and database name.

Thanks !

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (9)

Answers (0)

Javier Gallego on at

Like (0)

Report

Hi DavidForlen,

Here you have more info about the connector and the integration with Power Apps.

https://powerapps.microsoft.com/en-us/blog/general-availability-of-the-azure-ad-sql-server-connector/

Using the feature in Microsoft Power Apps

In Power Apps, this feature is now enabled by default. All new applications have this capability. If you have an existing application that you need to convert to use this new authentication, you must enable it the the File > App settings > Advanced settings section. To enable this feature, open your app in studio and enable the “Enable enhanced Microsoft SQL Server connector” feature.

To the use the Azure AD Integrated authentication select the “Azure AD Integrated” option from the drop-down. Click on “Create” to create a connection. Note you may need to sign in to Azure AD. Once the connection is created, you can specify the SQL server and database name and connect to it.

Once you are connected to a database, you will be able to select the tables and views you want to add in your app from a list. This experience and the rest remain the same as before.

Once you build the app, you can share the app. When you use Azure AD authentication with SQL Server, the connection will not be shared with the shared users.

The user to who you have shared the app with, will need to login with their own credentials.

I hope it helps you.

Javier

Was this reply helpful? Yes No
DavidForlen 55 on at

Like (0)

Report

Hi @FCGALLEG ,

@FCGALLEG wrote:
[...]
Once the connection is created, you can specify the SQL server and database name and connect to it.

[...]
I understood how to use SQL Server AAD connector on one given environment.
But I don't see how to export my apps and flows that use a SQL Server AAD connector into an environment that must use a different SQL database (so that my development and production environment remain separate).

As the server and database name are in plain text in canvas app, I don't see how to make sure that it will be automatically changed when exported to another environment.

Was this reply helpful? Yes No
Chris Hammond 130 on at

Like (0)

Report

@DavidForlen - Did you get a response on this please, it is an issue that I am now experiencing.

Thanks

Was this reply helpful? Yes No
mzandegiacomo 92 on at

Like (0)

Report

Hello @BO55VXR , @DavidForlen
Right now, I am asking myself the exact same question. Did you get any further in this topic?
Thanks,
Martin

Was this reply helpful? Yes No
HFG 516 on at

Like (0)

Report

I also have this issue. Has anyone found a way to switch the DB in different environments? There's got to be a way!

Was this reply helpful? Yes No
RobertLHU 50 on at

Like (0)

Report

apparently the data source environment variable currently only works with Sharepoint. 😞 (228) Using environment variables in Power Apps canvas apps and in Power Automate flows - YouTube

Was this reply helpful? Yes No
hongkong 277 on at

Like (0)

Report

After 1 year, the datasource environment variable is now supporting additional options. But still not supporting SQL server. May I know how do you work around it? I think the only workaround is using unmanaged solution for deployment and update the SQL server manually?

Was this reply helpful? Yes No
matthhias 48 on at

Like (0)

Report

Is there any article somewhere that explains best practice for SQL Connectors in DEV/TEST/PROD Scenarios?
I thought it is best practice but I can't find an answer anywhere.
Usage of environment variables would be also my first idea and I can't understand why this is not implemeted yet.
Or is it?

Was this reply helpful? Yes No
hongkong 277 on at

Like (0)

Report

In my opinion, use unmanaged solution for DEV/TEST/PROD is more safe.

Currently the "best practice" is not mature enough. It may work in model-driven app with dataverse but not for the other scenarios. I have tried using managed solution many times and there is no easy way to update a connection. You need to use tricks and hacks to update directly in dataverse. I think it is far more danger than using unmanaged solution.

Was this reply helpful? Yes No