How to Add/Create Azure SQL Virtual Tables in Dataverse?

[sperry1625]

I am trying to create Virtual Tables in Dataverse that point to my Azure SQL Server.  I have found some documentation on creating the necessary Virtual Connection, but I cannot get it to work.  When I go and create a new Virtual Entity Data Source these are my only options:

I selected OData v4 Data Provider but am not sure how, or where, to get the information it is asking for:

O tried putting in the URL for my Azure SQL instance, xxx.database.windows.net, but it did not like that.  What am I missing?  How do I point it to a specific database?  I have been unable to find any good documentation on how to do this.

Thank you.

[DeviKrishna]

Hi sperry1625,

Please use the URL as -https://XXAZURESQLXX.database.windows.net/odata/

The below URL was helpful. Did you refer the same?

 https://docs.microsoft.com/en-us/powerapps/maker/data-platform/virtual-entity-walkthrough-using-odat...

Please mark my answer as verified if you find this useful.

My Blog: Dynamics 365 Key Topics – https://d365topicsbydk.com/
My Youtube Channel : https://www.youtube.com/channel/UCxSIryP2ah2VpEFr-Z72t1A

Regards

Devi

[sperry1625]

I have located what I was looking for. I do not want to setup or use an OData connector.  I want to use the SQL Server (Preview) Virtual Connector.  This link gave me the information I needed to get this process started.

I will follow up on this thread with my progress.

Thank you.

[sperry1625 · Accepted solution]

I was able to install the Virtual Connector Data Provider (Preview) into my Environments. I was able to create a Virtual Entity Data Source using the connector to my Azure SQL Instance. I was then able to create a Virtual Table that referenced my Azure SQL data. Woo hoo.

Still working on the details but this is promising.

Thanks.

[Sunidhigambhir1]

Hello,

1.Sign in to Power Apps, and then select Settings > Advanced settings.

2.Go to Settings > Administration > Virtual Entity Data Sources.

3.On the actions toolbar, select New.

4.On the Select Data Provider dialog box, select from the following data sources, and then select OK.

[NHelgren]

can see you already found the Virtual Connector Provider. I have recently added videos walking you through the process as well. We have released the same capabilities for SharePoint and Excel. https://docs.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/create-virtual-... 

[omelo]

Hi,

Even though I have created a PowerApp with a connection to SQL, I'm not having any success in creating a virtual table from external data. It doesn't see the existing SQL connection. (I know it exists and it's working)

I select add connection besides SQL server but get errors when entering credentials. I selected windows or server authentication, and nothing seemed to work.

Am I missing any previous steps to create the virtual table from SQL (on-prem)?
Thank you!

[NHelgren]

Hi @omelo , 

It may be the authentication you are using on your connector. Currently only SQL authentication and AAD authentication are supported for the new tool. We are working to review the remaining authentication types. 

You can find this documented here: Create virtual tables using virtual connectors (preview) (Microsoft Dataverse) - Power Apps | Micros...

[omelo]

Hi @NHelgren , thank you for responding!

It didn't work with the SQL authentication either. 

I'll keep testing!

[NHelgren]

The Virtual Table process uses the same connector infrastructure as creating a standard connection, we just reuse the Connection interface in the wizard so it should be identical and showing any existing SharePoint or SQL connection you have created. I would suggest opening a case with the connector team if the created connections are not appearing in the list.