SQL query connection issue - Anonymous authentication scheme

[JK2020]

Hello,

I am trying to query a dataverse table using the "Execute a SQL query (V2)" connector, but getting the follwoing error already on saving:

I do have access to the environment/table - i.e. when the offending step is removed, the "List rows (legacy)" step executes just fine.

Also, I am able to run the same query using Power Query or SSMS:

The error message suggests something is wrong with the connection, but it looks fine to me.

Same issue in PowerApps with its SQL connector:

And again, I am able to access this data just fine in PowerApps using the Dataverse connector.

As can be seen in the first screenshot, the Flow is not in the same environment as the table I try to query (though not sure if this is relevant). Same with the Power App. These aren't actual environment names, I did doctor these screenshots up a bit to better illustrate the issue. The cases/incident table is also just an example, the actual table I want to query is ActivityParty table which is not available via the Dataverse Legacy connector, but it is available via SQL (as seen in Power Query and SSMS screenshots) so I am expecting it to be available via SQL connector in Power Automate, once that connection issue is resolved - any idea how it can be fixed, please?

[cchannon]

Dataverse does not support anonymous or username/password authentication. It only accepts claims-based authentication connections. What's more, the Azure SQL Data Warehouse connector is meant for querying Azure SQL Data Warehouse: an entirely different product. And even beyond that, Dataverse doesn't actually expose a SQL connection at all! It exposes a TDS endpoint that just emulates a SQL connection.

What you are doing will never work. Convert your query to Fetch and use the Dataverse connector instead.

[jk_2019]

@cchannon thank you, this is helpful. So how do I convert the query to Fetch?

[cchannon]

I suggest using the FetchXML Builder tool. It is a free community tool in the XRM Toolbox, and it makes it easy to convert queries between Fetch, SQL, C# QueryExpression, OData, WebAPI, and PowerAutomate parameters.

[jk_2019]

@cchannon  alright, will give it a go, but my concern is that at the end, I am still going to have to execute the Fetch query via Power Automate, right?

[cchannon]

I don't know why that would cause you concern. You can execute a fetch query many ways, but yes, Power Automate is a particularly easy one. You will want to use the Dataverse connector, specifically the List Rows action, which accepts as an input a Fetch XML string or OData-like query parameters (you can use either one, but if your query is very complex, I'd definitely recommend Fetch). 

[jk_2019]

@cchannon ok, so the reason I'm afraid this will not work for me is the following: Dataverse connector + List Rows action only works in the current environment, and, as mentioned in my initial post, my flow is not in the same environment as the ActivityParty table I am trying to query. I do not have the necessary access level to create my flow in the same environment as the table. Furthermore, when I try the new List rows from selected environment action in the Dataverse connector, I am getting a Data Policy violation error, so I cannot use that either. Which leaves me with the Dataverse (legacy) connector, but that one does not offer the Fetch query option:

Nor is the ActivityParty table available via Dataverse (legacy) connector.
But seeing how I could easily access this table via SSMS or PowerQuery, I thought that I could get there using the SQL connector in Power Automate - which you've now explained is not actually possible.

My query isn't complex, it's actually super simple - select 2 columns from ActivityParty table for a given (single) activity id. But unless there is some magic with the Fetch query you suggested that I am unaware of, I don't think it's going to work.

[cchannon]

OK, in that case, what you're looking for is the "List rows from selected environment" action, not "List Rows":

It lets you pick the environment target and use Fetch or Odata, whichever you prefer:

[jk_2019]

@cchannon you didn't read my last message fully, right?

[cchannon]

A Data Policy Violation is the result of a DLP boundary: Most likely, the issue is that your different environments are in different DLP boundaries and the Dataverse connector isn't permitted to reach across.

But it could also be that the Dataverse connector is considered "business data" which cannot be mixed in a single flow with other connectors considered "non-business."

It is also possible, if the Environment you are trying to connect to is in a different tenant, that one or both of the tenants in question have Tenant Isolation policies in place preventing the connection.

In any case, you should consult your admins to find out what the permitted connection patterns are; it could be that you can make this connection with a Service Principal, or you might need to make the API call directly using a custom connector or your own coded connection.

If all else fails, then you can also take more creative approaches to this, such as using that same Excel approach you demonstrated in an Excel sheet kept in OneDrive, and use the Power Automate actions to refresh its connection and pull latest, or a Power Automate Desktop flow that logs in as your user and queries/exports the data. These would be very kludgy approaches though, so don't go this way unless you have no other choice.

[jk_2019]

@cchannon thank you again for all this useful info, much appreciated. I've logged a ticket with our admins a few days ago re the DLP error and awaiting a reply now.
I don't think it's the mix of business and non-business data or the tenant isolation thing that causes the DLP error in this particular case, because the error appears as soon as I add any of the "... from selected environment" actions, even before specifying the environment/table I want to connect to.

The Excel workaround is what I have in place for over a year now, it works OK, but I'm aware that it's a sub-optimal solution and was hoping to remove this dependency. Thanks again for your time, I will report back when I hear from the admins.

[cchannon]

OK, that's good information. If it is erroring out immediately upon trying to use that action, it's because the blocked the action itself in DLP Connector action control - Power Platform | Microsoft Learn

The good news here is that DLPs are very flexible, so it is straightforward for your Admins to create special exception policies just for your one environment or even just this one flow, assuming that is permitted by their policies and procedures. Resource exemption - Power Platform | Microsoft Learn