Can't connect to Azure SQL Database with System Managed Identity on Windows Hosts

[sjwaight]

It does not seem like it is not possible to use standard .NET SQL Libraries to connect to Azure SQL Database with System-managed Identities in PowerShell Functions as the existing libraries on the Windows Hosts don't have the necessary .NET library (Microsoft.Data.SqlClient) installed and no easy way to get it onto the host and referenceable.

Options considered:

• function.proj (only for C# scripted Functions).
• dotnet restore in profile.ps1 (fails due to permisisons).
• load the SqlServer modules to see if they load this (does not seem to be the case).

The SQL Bindings say they support MSI, but I have not reviewed them as they do not suit the use case we are using Functions for.

[davidobrien1985]

The SQL Binding supports MSI (as advertised), but we need to build the connection string dynamically, and that doesn't seem to be possible with the PowerShell runtime.
Everything else is the same across all executions, it's only the connection string that'll be different, that's why we need to use the Microsoft.Data.SqlClient library.

[Francisco-Gamino]

Hello @sjwaight and @davidobrien1985 -- Unfortunately, Microsoft.Data.SqlClient does not run on PowerShell Core. However, as a workaround, you could try to use the Windows PowerShell in the worker VM.

In PowerShell Core (7.2 or higher), you can use the -UseWindowsPowerShell switch to run a Windows PowerShell module in PowerShell Core. This switch allows you to explicitly request the use of Windows PowerShell instead of PowerShell Core when importing or running a module that is not compatible with PowerShell Core (for more information, please see https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_windows_powershell_compatibility?view=powershell-7.3).

My suggestion would be to create a custom script module (psm1) that wraps the API calls to Microsoft.Data.SqlClient. In this module, you will have a function that creates a connection string, for example, New-ConnectionString, which will take the required parameters to build the connection string. In this module, you will load the Microsoft.Data.SqlClient assembly either using Add-Type -Path <pathToAssembly> or [System.Reflection.Assembly]::LoadWithPartialName('YourAssemblyName') and make the API calls in New-ConnectionString to build the connection string."

Here is how it would look like end to end:

# Import Windows PowerShell modules in PowerShell Core using the Compatibility feature (with the UseWindowsPowerShell switch). In this mode, PowerShell creates a remote session named WinPSCompatSession that is running in a background Windows PowerShell 5.1 process.

Import-Module -Name <MyModuleName> -UseWindowsPowerShell -ErrorAction Stop

# Option 1: Call directly into the cmdlet
New-ConnectionString -Param1 <Param1>  -Param2 <Param> ...

# Option 2: If option 1 does not work, you can always connect to the WinPSCompatSession session and use this session to call into the cmdlet

$s = Get-PSSession -Name WinPSCompatSession
Invoke-Command -Session $s -ScriptBlock {
    param(
        $Param1,
        $Param2
    )
    
    # Call the function with the parameters
    New-ConnectionString -Param1 $Param1 -Param2 $Param2
} -ArgumentList $Param1Value, $Param2Value

/cc @VasuBhog

[microsoft-github-policy-service]

This issue has been automatically marked as stale because it has been marked as requiring author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment.