0

Is it possible to use environment variable in canvas Power Apps for SQL server data source?

If not, any work around so that we can update the SQL server name and database name in a managed solution?

I have tried Connection reference. But I am using AAD integrated Authentication, the connection doesn't have parameter for server name and DB name. Also, within Power App studio I cannot find a way to link the app to connection reference.

enter image description here

Improve this question
7
  • You can manage it using connection references. Create a solution, create a SQL connection in DEV environment, use it in your app. Create a SQL connection with required server/database name in QA/PROD environment. Export solution from DEV & import in QA/PROD environment. Set connection reference in PROD to point to the connection created in QA/PROD environment.
    – Ganesh Sanap - MVP
    Commented Aug 1, 2023 at 12:31
  • @GaneshSanap I have tried Connection reference but it doesn't have parameter for server name and DB name (please see the screenshot I just attached). Also, within Power App studio I cannot find a way to link the app to connection reference.
    – Mark L
    Commented Aug 2, 2023 at 8:03
  • You have "Connection" option at the end in connection reference settings. So, first you have to create a connection with server name and DB name and then use that connection in connection reference settings. Hope that helps!
    – Ganesh Sanap - MVP
    Commented Aug 2, 2023 at 8:05
  • @GaneshSanap sorry to not making myself clear. I am using AAD integrated so I am not allowed to use server name and DB name. Do you think I need to switch to SQL authentication?
    – Mark L
    Commented Aug 2, 2023 at 8:07
  • Yes, use SQL authentication while creating SQL connection.
    – Ganesh Sanap - MVP
    Commented Aug 2, 2023 at 8:15

1 Answer 1

Reset to default
1

SQL Server data type is not supported for Power Platform environment variables. Currently only SharePoint, Dataverse and SAP is supported as data type in Power Platform environment variables.

For your requirements, you can use connection references in Power Platform solutions. We are using the connections references for few of our Power Platform solutions connected to SQL Server.

Follow these steps:

  1. Create a Power Platform solution in DEV environment
  2. Create a SQL server connection in DEV environment with required server/database details - using SQL authentication
  3. Use above SQL server connection in your DEV environment app for fetching/updating data
  4. Create a SQL server connection with required server/database details in QA/PROD environment - using SQL authentication
  5. Export solution from DEV environment as managed solution
  6. Import solution in QA/PROD environment
  7. Set connection reference in QA/PROD to point to the connection created in QA/PROD environment (step #4)
  8. Make sure table names & column names/types you are using in your app should be same for all DEV/QA/PROD environment SQL database
Improve this answer
2
  • My connection is in SQL authentication. By some reason, when I perform step 7. When I edit the connection ref in PROD, I cannot pick any existing connection because the connection dropdown is empty. I am the owner of existing connection.
    – Mark L
    Commented Mar 14 at 16:56
  • 1
    To fix it, I need to edit the PROD App in App studio. Remove all SQL connection and re-add.
    – Mark L
    Commented Mar 14 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.