Virtual table setup error with managed sql instance

(0) Share

Report

Posted on by DHoyt

When setting up a new table from external data in D365 CE I am able to establish the connection via a sql connection using sql authentication. This is attempting to connect into a managed sql instance. It works fine with a single sql server in the same azure environment.

However when I go to create the virtual table I get this error.

The sql user is a full server admin and I've tried adding to the database individually when that didn't work. Still the same error.

Any advice on how to resolve this would be appreciated.

I have the same question (0)

All responses (7)

Answers (0)

Mohamed GRAIB 2,504 Moderator on at

Like (0)

Report

Hi DHoyt,

Have you created the connection reference ? It's work correctly ?

I think that the error message is due to a connection reference

Can you create a connection reference and Check the connection settings ?

Hope that's helpful

Was this reply helpful? Yes No
DHoyt 5 on at

Like (0)

Report

The connection reference itself creates just fine, but which connection settings are you referring to?

Was this reply helpful? Yes No
Mohamed GRAIB 2,504 Moderator on at

Like (0)

Report

Hi,

Have you followed these steps ?

Step 1 : Create a connection reference from make.powerappas.com

Step 2 : Configure your connection Reference

Step 3 : Configure the connection settings (Click on New connection)

Hope that's helpful for you !

Tell us if that's work

Best regards,

Was this reply helpful? Yes No
DHoyt 5 on at

Like (0)

Report

I'm past that part that part, the step I'm getting the error on is when it's attempting to load the tables from the sql managed instance database I'm attempting to connect to.

Was this reply helpful? Yes No
Mohamed GRAIB 2,504 Moderator on at

Like (0)

Report

Hi,

In this case you must check your dataset in SQL Server

Base on the screen that you have shared, the database which you have the connection does not contain tables.

Regards,

Was this reply helpful? Yes No
DHoyt 5 on at

Like (0)

Report

The database definitely contains tables because they are being used by other apps, and I'm looking at them in SSMS logged in as the same user the connection is using.

Was this reply helpful? Yes No
Sayen Zhang on at

Like (0)

Report

Hi DHoyt,

To create a virtual table, you must have a Microsoft Dataverse license through Power Apps or Microsoft Dynamics 365. Microsoft 365 or Teams licenses can't be used to create virtual tables.

If so, have you enabled TDS Endpoint (Preview) feature in your environment? You can refer to this link: Connect D365 CRM CDS Database from SQL Server - Dynamics 365 Field Service Community

Here are links about create virtual tables using virtual connectors:

Create virtual tables using virtual connectors (preview) (Microsoft Dataverse) - Power Apps | Microsoft Learn

Create a Virtual Table for SQL with the Virtual Connector Provider - YouTube

Hope it helps you.

Best Regards,

Sayen Zhang

Was this reply helpful? Yes No