4

I'm working with Dynamics365 CE in the cloud. I'm trying to run some rather involved queries that I've built up as SQL scripts (using the wonderful "SQL-4-CDS" plugin for the XrmToolBox).

Now I know I can connect to the Dataverse data store through the TDS endpoint (if enabled - it is in my case), and from SSMS, it works just fine:

Server Name    = myorg.crm4.dynamics.com,5558
Authentication = Azure Active Directory - Password
User Name      = my company e-mail

I can connect to Dataverse, and run my queries - all is great.

Now I'd like to do the same from C# code (running on .NET 6) that I'm writing, that should end up being an Azure Function in the end - so it's a "server-to-server", behind-the-scenes, no interactive login context kind of scenario.

I can connect to Dataverse via the TDS endpoint using this connection string - as long as I'm running the app interactively - as me, in my user context:

Server=myorg.crm4.dynamics.com,5558;Authentication=Active Directory Password;Database=my_dbname;User Id=my_email;Password=my_pwd;

However - this won't work with a server-to-server "daemon"-style setup.

Since I'm using .NET 6 (for the Azure Function), and since I want to run some custom SQL statements, I cannot use the "CRM XRM Client" tooling (with the IOrganizationService classes) - I need to use straight ADO.NET - any idea would I could define an ADO.NET compatible connection string, that would use a Client ID and Client Secret (which I both have at my disposal)?

I've tried a great many values for the Authentication=...... setting - but none have worked so far. Any place I can find a complete list of the supported values for this connection string parameter?

Thanks for any help or pointers!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • for .NET 6 you can use the SDK compatible with .NET Core https://www.nuget.org/packages/Microsoft.PowerPlatform.Dataverse.Client/ but if you don't want/can't use it I understand. Can't help you with your question but I suggest to change some tags, otherwise folks looking for dynamics-crm dynamics-365 can't find your question (I found your question after more than 10 days) – Guido Preite Mar 14 '22 at 14:59
  • @GuidoPreite: yes, I can use that SDK - but that still won't allow me to run SQL scripts (like I can when connecting with ADO.NET). The trouble with ADO.NET is that I cannot use any "daemon"-style login procedure - everything I find requires a manual / human interaction (2FA etc.) - I cannot use ClientId/ClientSecret to connect with ADO.NET :-( – marc_s Mar 14 '22 at 16:16
  • Check out the connection string being built in https://stackoverflow.com/a/60446207/529618 - The best-practice authentication method is for the Azure Function App's "RunAs" identity or some other AAD Application (Service Principal) to use its credentials to connect to the SQL server. – Alain Jan 04 '23 at 16:42
  • Similar: https://stackoverflow.com/questions/67225452 – Alain Jan 04 '23 at 16:44
  • @Alain: thanks for the links - but I honestly don't really see how these are related to my question - they have absolutely nothing to do with DataVerse and its connection strings, or the ability to run SQL scripts against DataVerse ..... – marc_s Jan 04 '23 at 16:46
  • You may be narrowing your search too much but seeking something "DataVerse" related. This is more generically an SQL Connection String / Authentication issue. Since you mentioned that you ultimately plan to run from an Azure Function, what you need to focus on is the correct pattern for Azure Functions to provide credentials to remote SQL Servers. Service Principals (RunAs credentials) are the way to do that. It's worth noting that whatever local solution you come up with will only solve your problem when running your solution locally. Once you move to an AZ function, the problem changes. – Alain Jan 04 '23 at 16:51
  • @Alain: but I ultimately **need** to access "DataVerse" - so it has to be something specific to that data platform - general SQL connection advice doesn't really help here, unfortunately. – marc_s Jan 04 '23 at 16:54
  • You said "I can connect to Dataverse via the TDS endpoint using this connection string - as long as I'm running the app interactively - as me". This is the solution right here. You need to set up a Service Principal in Azure. It behaves just like another "user" (but isn't you) and when Azure runs your code, it would "look" (to DataVerse) similar to when you are running the code. So try a "local user context" string that works when you personally run it, stick it in an Azure Function, and run that Azure Function as a service principal that has all the same permissions as you to use DataVerse. – Alain Jan 04 '23 at 17:02

1 Answers1

1

I know it's an old question, but in case it's still helpful to anyone: you should be able to use the SqlConnection.AccessToken property to authorize your connection to the TDS Endpoint. To get the access token you'd need to use your application user client ID & secret to obtain the token.

You can also use the SQL 4 CDS ADO.NET provider - once you've created the ServiceClient from the Dataverse SDK, pass that to the Sql4CdsConnection constructor:

using var con = new Sql4CdsConnection(service);
using var cmd = con.CreateCommand();
cmd.CommandText = "SELECT ...";
MarkMpn
  • 11
  • 1