search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

Invoke-Sqlcmd throws an error: SqlColumnEncryptionAzureKeyVaultProvider ctor method not found #45

Closed cutecycle closed 5 months ago

cutecycle commented 1 year ago

Haven't started on reproducing outside of Azure Pipelines yet, but:

Copied from the issue in https://github.com/actions/runner-images/issues/7446:

Description

SqlServer 22.0.59 / Windows PowerShell 5.1 / windows-2022 / Azure DevOps

This occurs on Microsoft-hosted agents.

It may have happened after this update: https://github.com/actions/runner-images/commit/0f4a3567b189f2f0f60582cf99bd5c62c9b8f4e1

When attempting to connect to a Synapse On Demand pool with password authentication, this occurs:

with the following connection string characteristics: ;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False

##[debug]Invoke-SqlCmd : Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
##[debug]At D:\a\1\s\deploy\Common.ps1:31 char:27
##[debug]+ ...   $result = Invoke-SqlCmd -ConnectionString $connectionString -query  ...
##[debug]+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
##[debug]    + CategoryInfo          : InvalidResult: (:) [Invoke-Sqlcmd], MissingMethodException
##[debug]    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
##[debug] 
##[debug]Script stack trace:
##[debug]at Invoke-AzSqlQuery, D:\a\1\s\file.ps1: line 31
##[debug]at <ScriptBlock>, D:\a\1\s\file2.ps1: line 59
##[debug]at <ScriptBlock>, D:\a\_temp\04a7747b-46d3-4ec0-8b74-6a37641f9b6b.ps1: line 38
##[debug]at <ScriptBlock>, <No file>: line 1
##[debug]Exception:
##[debug]System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
##[debug]   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.SqlAKVProviderWithDisposableCache..ctor(TokenCredential tokenCredential)
##[debug]   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.SetCustomProvidersWithDisposableCache(Boolean useGlobalCredential)
##[debug]   at Microsoft.SqlServer.Management.PowerShell.Shared.AzureKeyVaultUtilities.RegisterCustomKeyStoreProviders(String keyVaultAccessToken, String managedHsmAccessToken, SqlConnection conn)
##[debug]   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
##[error]Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.

Platforms affected

  • [X] Azure DevOps
  • [ ] GitHub Actions - Standard Runners
  • [ ] GitHub Actions - Larger Runners

Runner images affected

  • [ ] Ubuntu 20.04
  • [ ] Ubuntu 22.04
  • [ ] macOS 11
  • [ ] macOS 12
  • [ ] Windows Server 2019
  • [X] Windows Server 2022

Image version and build link

https://github.com/actions/runner-images/commit/0f4a3567b189f2f0f60582cf99bd5c62c9b8f4e1

Is it regression?

Yes

2023-04-06T16:21:28.8314157Z Image: windows-2022
2023-04-06T16:21:28.8314374Z Version: 20230402.1
2023-04-06T16:21:28.8314693Z Included Software: https://github.com/actions/runner-images/blob/win22/20230402.1/images/win/Windows2022-Readme.md
2023-04-06T16:21:28.8315281Z Image Release: https://github.com/actions/runner-images/releases/tag/win22%2F20230402.1

Expected behavior

Invoke-SqlCmd connects to the server and executes the command

Actual behavior

[debug]System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.

Repro steps

Connect to a Synapse On Demand pool with password authentication, with the following connection string characteristics: ;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False

cutecycle commented 1 year ago

I'm having difficulty reproducing the scenario outside of Pipelines, but ran into something interesting on an unrelated project where I call Invoke-SqlCmd from a PowerShell context in a .NET 7.0 program, and in this case I'm just connecting to LocalDB:

local machine information

Name                           Value
----                           -----
PSVersion                      7.3.3
PSEdition                      Core
GitCommitId                    7.3.3
OS                             Microsoft Windows 10.0.22621
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

powershell context

 {
  "WSManStackVersion": {
    "Major": 3,
    "Minor": 0,
    "Build": -1,
    "Revision": -1,
    "MajorRevision": -1,
    "MinorRevision": -1
  },
  "PSCompatibleVersions": [
    {
      "Major": 1,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 2,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 3,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 4,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 5,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 5,
      "Minor": 1,
      "Build": 10032,
      "Revision": 0,
      "MajorRevision": 0,
      "MinorRevision": 0
    },
    {
      "Major": 6,
      "Minor": 0,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 6,
      "Minor": 1,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 6,
      "Minor": 2,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 0,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 1,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 2,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 3,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    }
  ],
  "Platform": "Win32NT",
  "PSRemotingProtocolVersion": {
    "Major": 2,
    "Minor": 3,
    "Build": -1,
    "Revision": -1,
    "MajorRevision": -1,
    "MinorRevision": -1
  },
  "OS": "Microsoft Windows 10.0.22621",
  "GitCommitId": "7.3.0",
  "PSVersion": {
    "Major": 7,
    "Minor": 3,
    "Patch": 0,
    "PreReleaseLabel": null,
    "BuildLabel": null
  },
  "SerializationVersion": {
    "Major": 1,
    "Minor": 1,
    "Build": 0,
    "Revision": 1,
    "MajorRevision": 0,
    "MinorRevision": 1
  },
  "PSEdition": "Core"
}
 ---> System.Management.Automation.RuntimeException: {
  "WSManStackVersion": {
    "Major": 3,
    "Minor": 0,
    "Build": -1,
    "Revision": -1,
    "MajorRevision": -1,
    "MinorRevision": -1
  },
  "PSCompatibleVersions": [
    {
      "Major": 1,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 2,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 3,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 4,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 5,
      "Minor": 0,
      "Build": -1,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 5,
      "Minor": 1,
      "Build": 10032,
      "Revision": 0,
      "MajorRevision": 0,
      "MinorRevision": 0
    },
    {
      "Major": 6,
      "Minor": 0,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 6,
      "Minor": 1,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 6,
      "Minor": 2,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 0,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 1,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 2,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    },
    {
      "Major": 7,
      "Minor": 3,
      "Build": 0,
      "Revision": -1,
      "MajorRevision": -1,
      "MinorRevision": -1
    }
  ],
  "Platform": "Win32NT",
  "PSRemotingProtocolVersion": {
    "Major": 2,
    "Minor": 3,
    "Build": -1,
    "Revision": -1,
    "MajorRevision": -1,
    "MinorRevision": -1
  },
  "OS": "Microsoft Windows 10.0.22621",
  "GitCommitId": "7.3.0",
  "PSVersion": {
    "Major": 7,
    "Minor": 3,
    "Patch": 0,
    "PreReleaseLabel": null,
    "BuildLabel": null
  },
  "SerializationVersion": {
    "Major": 1,
    "Minor": 1,
    "Build": 0,
    "Revision": 1,
    "MajorRevision": 0,
    "MinorRevision": 1
  },
  "PSEdition": "Core"
}

Code

            using (var powershellStatement = PowerShell.Create())
            {
                var script = $@"
    Invoke-SqlCmd -ConnectionString '{_connectionString}' -InputFile {newTempFile} -Verbose
";
                powershellStatement.AddScript(script);
                var combinedStreams = new PSDataCollection<PSObject>();
                powershellStatement.Streams.Error.DataAdded += ConsumeStreamOutput;
                var invocationResult = await powershellStatement.InvokeAsync();
                if (powershellStatement.HadErrors)
                {
                    //if those errors include the 'resetting' error, then we're good...
                    var errors = powershellStatement.Streams.Error;
                    var errorMessages = errors.Select(e => e.ToString()).ToList();
                    if (errorMessages.Any(e => e.Contains("Resetting the connection results in a different state")))
                    {
                        return;
                    }
                    var status = _commandStatus.ToString();
                    throw new InvalidOperationException($"SqlCmdPowershellScriptExecutor: Error executing upgrade script on {_connectionString}. \n Output: \n{status}.");
                }
            }
 ---> System.InvalidOperationException: SqlCmdPowershellScriptExecutor: Error executing upgrade script on Server=(localdb)\MSSqlLocalDb;Database=TestDataProject;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=SSPI;.
 Output:
Method not found: 'Void Microsoft.Extensions.Caching.Memory.MemoryCache..ctor(Microsoft.Extensions.Options.IOptions`1<Microsoft.Extensions.Caching.Memory.MemoryCacheOptions>)'.
deduefren commented 1 year ago

Hi, I've also started having the following error running Invoke-SqlCmd after Azure Pipelines updated SQLServer package from version 21.1.18256 to 22.0.59

##[debug]System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
(url)

I was only able to reproduce the error on Azure Pipelines with Az modules < 9.3.0. This error doesn't manifest with latest Az Modules. I am attaching a sample pipeline and it's logs just in case it helps with diagnostics. (Note that connection string has been modified to avoid leaking credentials). pipelineLog.txt azure-pipelines.txt

Working combinations

With SQLServer module version 22.0.59

With SQLServer module version 21.1.18256

JAK1047 commented 1 year ago

Same issue after moving from 21.1.18256 to 22.0.59, but in my case only if I load the PnP Powershell module first. So, I'm guessing there's a conflicting DLL or something.

Matteo-T commented 1 year ago

Hi @JAK1047 - yes, there are already known issues related to the interp of modules.

The issue is not simple and would required all modules to cooperate (I have an issue logged to do my part, but it's a prety extensive work item). I suspect that other modules (Az.*) have the same/similar issue.

Few things that I've observed (until a proper solution is implemented, at least in PS7 ... for PS5, I think we are kind of doomed):

In the interim, all I can try to do (aside from putting band aids to the module) is to see if I can play catch up with the Az.* modules and try to stay as close as possible to them. Not ideal, but... better than nothing.

JAK1047 commented 1 year ago

I assume when you mention the work to be done you're referencing using ALC?

https://learn.microsoft.com/en-us/dotnet/core/dependency-loading/understanding-assemblyloadcontext

That's how the PnP and MSAL modules eventually got around the dependency conflicts to my understanding.

Matteo-T commented 1 year ago

@JAK1047 - yup. precisely.

kelojok commented 5 months ago

I had the Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider error so I installed sqlpackage, then ran my script from Windows Terminal, as administrator.

I add this here in case someone find themselves in a similar situation.

Matteo-T commented 5 months ago

This is a duplicate of #65 which is most likely a special case of #31

hugocal commented 4 months ago

We were able to mitigate the issue using PowerShell version 10.4.1:

azurePowerShellVersion: '10.4.1'