Dataverse Virtual Table error: Connection Reference xxx does not reference to connector with Id /providers/Microsoft.PowerApps/apis/shared_sql

[Gary_Eden]

Hi
I'm trying to start using Dataverse Virtual Tables and have encountered some issues.

The ultimate goal is to use Virtual Tables with Oracle (on-prem) and following the guide here I have managed to connect to a table (that I know contains data) but I just get a message telling me that 'Something went wrong. We weren't able to open your table. Try reloading or reopening.' I've reloaded/reopened multiple times but no data.

As this is the first time using Virtual Tables maybe starting with Oracle wasn't the best idea so have tried the 'standard' Virtual Table connection method. For SharePoint lists it works perfectly and does everything expected.

However, when using the SQL Server connector to connect to Azure SQL everything maps okay but when you press finish to create the table you get the following error:

Connection Reference xxxx does not reference to connector with Id /providers/Microsoft.PowerApps/apis/shared_sql

I can't find any information on this error or how to fix it. 

Anyone on the forum know what's causing it and how to overcome it.

Hopefully, fixing the Azure SQL connection issue may provide a clue to what's blocking the Oracle connection from passing data.

Thanks in advance.

[shashankbhide]

you said the Oracle DB is OnPrem, is your azure sql also protected by some firewall rule or azure VPN?

[Gary_Eden]

Hi @shashankbhide 

For Azure SQL I can connect to the tables from with the Canvas App and run the app normally, without being connected to the VPN. However, to use SQL Server Management Studio to create tables etc. I have to be on the VPN.

For the Oracle tables I have to be on the VPN to connect to them. The app will connect and display data happily when connected directly both in studio and when in standard play mode. If not connected to the VPN you get the standard table not found error - which is what I'd expect.

I've tried connecting to the VPN first when creating the Virtual Tables in both cases. For Azure SQL (using the SQL Server connector) I get the above error. 

For Oracle I get a table and columns but no data.

I'm obviously missing something but going back over the notes on how to create both types of Virtual Table I can't see where.

[Gary_Eden]

Hi @shashankbhide 

Found the answer for the problem with the Azure SQL Virtual Table explained here.

Tried to remove the old shares but that didn't work so created a new connection and everything worked as it should.

I suspect that the age of connection meant something was missing under the covers.

Now back to sorting out the Oracle connection.

[shashankbhide]

Since your oracle is OnPrem as you said, you might need to discuss with your Infra team (if there's one), you might have to use a site to site VPN to connect your onprem network to the azure VPN. 

[Gary_Eden]

Hi
The aim here is to use Dataverse Virtual Tables directly with Oracle.
Daisy chaining from Oracle to Azure SQL to Dataverse would add additional complexity and shouldn't be needed if the Virtual Tables work with Oracle as they should.

[shashankbhide]

Ok, and how do you plan to connect the onprem Oracle to the cloud based Dataverse?

[Gary_Eden]

This workaround actually works: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/get-started-ve 

It connects to the Oracle tables, creates the Virtual Tables and displays the columns.

However, it's not returning any data from the Oracle tables in the Virtual Tables.

I'm trying to determine if this is a configuration or permissions issue at the moment. I suspect configuration as I can connect directly to the Oracle tables and data in PowerApps and Power BI without a problem. 

[Gary_Eden]

Hi

I've not managed to get the workaround working.

However, while searching for a solution I came across this post: https://www.linkedin.com/pulse/wave-1-2024-saleforce-oracle-external-data-sources-virtual-homola-atg... 

Reading through the replies there is this comment "...the Wave 1 2024 update from Dynamics that brings native Virtual Tables support to Salesforce and Oracle..."

Since starting to experiment with Virtual Tables the Salesforce and Fabric Virtual Table connectors have appeared but not the one for Oracle. In the LinkedIn post the screen shot shows the Oracle connector in the VT page and checking the information relating to the Wave 1 2024 release it suggests that the Oracle VT connector/support should be active.

I appreciate that there be some limitations with the Oracle VT connector but I'd like to find out if it does what I need it to but can't unless it's there.

The Salesforce & Fabric connectors just appeared. However, is there a setting somewhere that enables the Oracle connector and/or has anyone else had the same issue and knows how to fix it?  

[Gary_Eden]

I've gone through the OData workaround for creating Virtual Tables connected to Oracle step-by-step again and realised that nowhere does it ask for or explain where you put the username and password. 

The table columns are showing but no data... which makes sense if the connection is being rejected due to no username/password.

I have a standard Oracle connection working for direct table connection (in the same way as I have for Salesforce, Azure SQL, etc.) but this isn't available for the Virtual Table connection.

I've read back over the documentation several times now and made several searches but can't find any explanation on where to add username/password when creating the OData workaround connection.

Is anybody able to provide instructions on how to do this?

I can't be the only one who's come across this.