A short post covering how to resolve the {"Status": 400, "message":Bad Data Source.\r\n inner exception: Not a valid data source...} , should encouter this in a Power App connected to a backend SQL Server Express 2017 virtual machine (VM), using a shared SQL Server Connector .
We noticed the issue after we sucessfully provisioned the VM , imported the Power App into the “Development and Testing” enviroment and then added the SQL Server Connector
Disable Enhanced Microsoft SQL Server Connector
The first thing you will need to do is to Disable the Enhanced Microsoft SQL Server Connector, but that is not simple. You need to remove the SQL Connector, and any SQL tables, referencing the connector. If your Power App calls a Power Automate, which also uses the SQL Connector, then remove it as well. Note, your Power Automate will be temporarily invalid, but you can fix that before re-adding it to the Power App, in one of the last steps.
Navigate to make.powerapps.com and login.
Select the eliplses menu (…) in the SQL Connection and delete.
Next open the Power App, and select Data page from the far left vertical navigation menu. You will need to manually remove each SQL Table reference. Note , If there is a Power Automate connector listed, using the same SQL Server connector, then you will need to remove this as well.
Enter the Browser Developer Tools “AppMagic” Commands
Power Apps also you inspect and remove connection references, should any still be cached in any way, using the following comands:
AppMagic.AuthoringTool.Runtime.powerAppsClient.referenceStore._references
delete AppMagic.AuthoringTool.Runtime.powerAppsClient.referenceStore._references ['XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'] (Replace the XXXX with the Id of connector/flow reference that you copied above)
1) Go to Chrome or Edge Chromium in Private mode
2) Open the app in Studio (Edit Mode)
3) Go to Dev Tools (More Tools – Developer Tools, as F12 does not work in Studio)
4) Go to Console (see image console)
5) Enter the following command at the bottom of the page, then hit enter: (see image command) AppMagic.AuthoringTool.Runtime.powerAppsClient.referenceStore._references
6) Click the arrow to open the list (Image arrow)
7) Identify the (SQL Server) connector and Power Automates that needs deleting – this Power App may have both.. In Copy the Id reference XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX of this connector.
8 – Then run the following command at the foot of the page again:
delete AppMagic.AuthoringTool.Runtime.powerAppsClient.referenceStore._references [‘XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX’] (Replace the XXXX with the connector/Power Automate reference that you copied above). A true response indicates the connection is sucessfully deleted.
9 – Save and publish.
