Environment variable with SQL server

(3) Share

Report

Posted on by emil049

Im using SQL Server connector with different databases for Dev, Uat, and prod.

My question is how to change these data source while deploying it from one environment to another?

I checked environment variable but i could see only sharepoint, dataverse, SAP ERP options.

I couldn't see SQL server there,

So please help me to achieve this. How to change data source in different environment.

Thanks in Advance

Categories:

Governance & administering

All responses (11)

Answers (0)

Sort by

joe_hannes_col 1,843 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @emil049,

With SQL, you might not need environment variables when working with solutions. When you import a solution into Prod, you should be asked to specify a connection to SQL. Depending on the type of authentication you use, you will be asked to specify the server URL at this point, e.g. when using SQL username/password.
If your tables have the same names, your apps and flows should work.
hongkong 277 on at

Like
a
(0)

Report
Copy link

Link copied!

I have a test Power App connected to SQL server with authentication AAD - Universal with MFA. It is pointing to a Test SQL server.
When I export the solution and import to the production environment, no prompt up ask me for server name, login, etc. Therefore, with a managed solution, I have no way to update the SQL server connection to a production one.

What should we do for above scenario beside using a unmanaged solution?
arigold 4 on at

Like
a
(1)

Report
Copy link

Link copied!

I'm struggling with the same challenge. Did you progress with any solution or workaround for environmental variables for SQL Server?
emil049 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Not yet,
currently im changing the data source in the test environment, and export the app to prod.
hanzelmans on at

Like
a
(0)

Report
Copy link

Link copied!

What was the end result? I'm running into the same problem when I try to promote a solution between environments.

Do you modify the app in the next level environment (dev to test)? Since Test or Prod would be managed, you kind of "break" the solution by doing that.
velegandla 204 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@hanzelmans'

Can you try below steps using Power platform pipelines?

Deleted all old Solutions on Development environment.
Deleted all Connection references.
Created new Solution in development environment.
Add the existing Canvas App to the solution.
Added new connection reference to SQL server connection.
Deleted all Datasets(Tables) inside Canvas App which used the SQL Server connection.
Added the Dataset(Tables) again with for SQL Server connection.
Deploy the solution using Pipeline.
====================================================

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

https://www.linkedin.com/in/devendravelegandla/
hongkong 277 on at

Like
a
(0)

Report
Copy link

Link copied!

May I know which type of SQL server connection you're using? I believe only SQL authentication can be used in connection reference?

In Step 6 and 7, do you mean delete and re-add the "data connection" listed in below screen capture ?
velegandla 204 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@hongkong

That is correct.
hanzelmans on at

Like
a
(0)

Report
Copy link

Link copied!

What if I do not have pipelines configured?

Would a normal solution import/export do the trick?
velegandla 204 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@hanzelmans

I believe so; normal export and import should work similarly.

====================================================

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

https://www.linkedin.com/in/devendravelegandla/