Thu, 12/17/2020 - 11:36 By Lloyd Sebag
2 comments

This article talks about the following topic : Dataverse access SQL Server database C# (Dynamics 365/CDS)
This is to follow up on our article on how to access the Dynamics 365 SQL Server database from SQL Server Management Studio.

Prerequisite

In fact, you can start from our previous article in order to configure correctly your environnement then continue here. 

So let's say that you can successfully run this query on your SSMS : 

Dataverse access SQL Server database C#

And now you would like to run the same query by using C#. 

And now with C# ?

This is very simple, you just have to use the famous SqlConnection class of System.Data.SqlClient .Net Library. But please not some important point that I met during my tests (explained at the end of the article). 

The C# code is : 

var connetionString = "Server=<servername>.crm4.dynamics.com,5558;Authentication=Active Directory Password;Database=<dbname>;User Id=<useremail_upn> ;Password=<password>;";
var sql = "select top 10 contactid from contact";
var connection = new SqlConnection(connetionString);
try
{
      connection.Open();
      var command = new SqlCommand(sql, connection);
      var dataReader = command.ExecuteReader();
      while (dataReader.Read())
      {
            Console.Write(dataReader.GetValue(0) + Environment.NewLine);
      }
      dataReader.Close();
      command.Dispose();
      connection.Close();
}

The result will be 

Dataverse access SQL Server database C#

That's it !  As you can see now you can call CDS / Dataverse / Dynamics 365 SQL Data from your C# code ! 

Considerations

This code works thanks to the ADAL libraries for SQL which you can install here. The Lib will support the Authentication=Active Directory Password that is on the connectionstring.

While testing I encountered an error saying: Unable to load adalsql.dll
After some research I noticed that my version of ADAL for SQL was not the right one.
You will simply have to remove the component installed on your computer and install the one from the link below.

The one to remove : 

adal remove

Conclusion

This new functionality will allow you to further extend your possibilities for integrating your systems. By now being able to rely on the power of the SQL Server engine. That's very good news. Thanks Mircosoft.

Dataverse access SQL Server database C#

Comments

Hi Lloyd , many thanks for providing this code. I assume the user connecting via this code must be a licensed user on D365/PowerPlatform. Would you know if there is way of connecting as application user ? That wouldn't require a license. We would still need to permission the application user on the environment itself

Thanks
Alen

Fri, 08/20/2021 - 09:48
Alen (not verified)

Hi Alen, my pleasure ! 

In fact, I never tried to connect by using an Application User but when reading some docs for your case I think it should be possible. 

This code uses ADAL librairies for SQL. As you can see on the connection string there is a authentication parameter set on ActiveDirectoryPassword .

If we refer to this doc : https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15 

It should be possible to set it to ActiveDirectoryServicePrincipal : can be used to connect to an Azure SQL Database/Synapse Analytics using the application/client ID and secret of a service principal identity... 

You should try on this way. 

Goog luck and please keep us informed of the result ;) 

Best, Lloyd

Mon, 08/23/2021 - 12:23

In reply to by Alen (not verified)

Add new comment

Image CAPTCHA
Enter the characters shown in the image.