Azure PowerShell script Add-Type "Kusto.Data.dll" give load exception for System.Runtime.dll

I have to load types defined in kusto.data.dll for making Kusto query to get data from Azure Data Explorer cluster.

When I use Add-Type -LiteralPath $packagesRoot, it fails with Unable to load one or more of the requested types for System.Runtime.dll and System.Data.Common.

These are standard .net dlls, how do I load them? If I have to specify -ReferencedAssemblies parameter, what should be path used for these system dlls?

My PS version is

Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      26100  2161

NOTE: This answer shows the basic steps you need to follow in order to query your Azure Data Explorer Cluster. The authentication mechanism used is UserPromptAuthentication, however the assembly does provide many authentication mechanisms, the most common ones are detailed in Connect-Kusto doc and the source code if you need details on the usage.

All the code shown below can be greatly simplified if you have the PowerShellKusto Module, however you should note this module requires PowerShell 7+. Using the module, all code would translate as follows:

$connectKustoSplat = @{
    Cluster  = 'https://mySourceCluster.eastus.kusto.windows.net'
    Database = 'myDb'
}
Connect-Kusto @connectKustoSplat

Invoke-KustoQuery 'SigninLogs | take 10'

Very likely you have downloaded the NuGet package Microsoft.Azure.Kusto.Data and this package has dependencies from other assemblies. In order to make a query to your Azure Data Explorer Cluster, the NuGet package you'll need is Microsoft.Azure.Kusto.Tools.

The first step would be to download the package and expand it:

# NOTE: The following downloads and expands the package in the current directory
Invoke-WebRequest https://www.nuget.org/api/v2/package/Microsoft.Azure.Kusto.Tools -OutFile Microsoft.Azure.Kusto.Tools.zip
Expand-Archive .\Microsoft.Azure.Kusto.Tools.zip

Once you have the assembly:

• If you're running Windows PowerShell 5.1, use the package compiled for net472.

• If you're running PowerShell 7+, which currently runs on .NET 9, you can use the net8 version of the package.

However, the Kusto Tools package depends on many external assemblies, which can lead to conflicts if you use modules relying on different versions of the same assemblies, a problem known as "DLL hell". To address this, I created the PowerShellKusto Module, which uses an assembly load context (ALC) to resolve these issues; ALC was introduced in .NET Core (now just .NET) and isn’t available in .NET Framework, the version used by Windows PowerShell 5.1, this is the reason why the module is only compatible with PowerShell 7+.

In summary, if you go the manual route, the code would be:

try {
    # NOTE: Here we assume the expanded package is in the current directory
    $assembly = Convert-Path .\Microsoft.Azure.Kusto.Tools\tools\net472\Kusto.Data.dll
    [System.Reflection.Assembly]::LoadFrom($assembly) | Out-Null

    # This details have to be updated to target your Cluster and the Database in said Cluster
    $clusterUrl = 'https://mySourceCluster.eastus.kusto.windows.net'
    $databaseName = 'myDb'

    # Here we use `UserPromptAuthentication()`, you should inspect the other
    # authentication mechanisms if you need to automate this process using for example
    # a System Managed Identity or a Service Principal
    $queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider(
        [Kusto.Data.KustoConnectionStringBuilder]::new($clusterUrl, $databaseName).
            WithAadUserPromptAuthentication())

    $crp = [Kusto.Data.Common.ClientRequestProperties]::new()
    $crp.SetOption(
        [Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout,
        [TimeSpan]::FromSeconds(30))

    # This is where you should update your KQL query,
    # the example shows a query to a table named "SigninLogs"
    $reader = $queryProvider.ExecuteQuery('SigninLogs | take 10')
    $ds = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader)
    [System.Data.DataView]::new($ds.Tables[0])
}
finally {
    if ($queryProvider) { $queryProvider.Dispose() }
    if ($reader) { $reader.Dispose() }
}