SqlServer Module - Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version 4.0.0.0...

tparikka commented 1 year ago

Prerequisites

[X] Write a descriptive title.
[X] Make sure you are able to repro it on the latest released version
[X] Search the existing issues.
[X] Refer to the FAQ.
[X] Refer to Differences between Windows PowerShell 5.1 and PowerShell.

Steps to reproduce

I am trying to run a local development environment setup script for my solution that includes the use of the SqlServer module to make changes. It uses the following command to connect to the default SQL instance:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT

This worked on a previous laptop, but does not work on my newly issued laptop. I don't have access to the old laptop anymore to isolate differences.

Expected behavior

The `Set-Location` command should connect successfully to the database to allow the subsequent actions to complete.

Actual behavior

The following exception is thrown in response to `Set-Location SQLSERVER:\SQL\localhost\DEFAULT`:

Set-Location: C:\src\repos\MYPATH\setup-database.ps1:7
Line |
   7 |  Set-Location SQLSERVER:\SQL\localhost\DEFAULT
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | SQL Server PowerShell provider error: Could not connect to 'localhost\DEFAULT'. [Could not load type
     | 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral,
     | PublicKeyToken=b77a5c561934e089'.]

While troubleshooting I also found that using Set-Location SQLSERVER:\SQL followed by Get-ChildItem as described in the Microsoft docs (https://learn.microsoft.com/en-us/sql/powershell/navigate-sql-server-powershell-paths?source=recommendations&view=sql-server-ver16) also produces an error:

PS SQLSERVER:\SQL> set-location SQLSERVER:\SQL
PS SQLSERVER:\SQL> Get-ChildItem
WARNING: Could not obtain SQL Server Service information. An attempt to connect to 'INT-P16-TJP' failed with the following error: Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.


### Error details

```console
PowerShell 7.3.3
PS C:\Users\thomas.parikkaADM> Import-Module SqlServer
PS C:\Users\thomas.parikkaADM> Set-Location SQLSERVER:\SQL
PS SQLSERVER:\SQL> Get-ChildItem
WARNING: Could not obtain SQL Server Service information. An attempt to connect to 'INT-P16-TJP' failed with the following error: Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
PS SQLSERVER:\SQL> Get-Error
PS SQLSERVER:\SQL>


### Environment data

```powershell
PS C:\> $PSVersionTable

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

Visuals

No response

Matteo-T commented 1 year ago

Hi @tparikka - what version of the module are you using? Can you confirm you are on the latest v22 prerelease version? I'm getting close to get out of "preview", so I'd rather avoid having to look into the ancient v21 :-)

Could you capture the callstack when the original error happens? $Error[0].Exception.ToString() usually get it for you.

tparikka commented 1 year ago

Hi @Matteo-T, I appear to be on 21.1.18256. I have installed 22.0.49-preview and this one seems to work successfully. Any thoughts on what was fixed so I can adjust documentation on my side for devs who consume these setup scripts?

I did try grabbing $Error[0].Exception.ToString() but it returned InvalidOperation: You cannot call a method on a null-valued expression. Get-Error returned a null response.

Matteo-T commented 1 year ago

The error you are seeing makes me thing of some mismatch on DLL loading (or maybe corrupt installation?)

Hard to tell without seeing what you are doing in setup-database.ps1 before you get to line 7 (I don't expect much to happen in those 6 lines, but who knows...). Can you share those? Are you importing other modules (or running scripts/cmdlets that may be pulling in newer versions of SMO and the SqlClient driver?

Maybe there is a conflict between modules or DLLs loaded before getting to line 7. Particularly, do you have an explicit "Import-Module SQLServer" at the beginning of the script? That's usually a good thing to do.

As for the $Error[0].Exception.ToString() erroring out:

Do you have anything at all in $Error and $Error[0]?
If those are not null, what's $Error[0].ToString()? Remember: you need to do it right after line 7 before the $Error is cleared.

Also getting the list of the loaded DLLs in the pwsh.exe process could help. And Fusion logs too. Or attaching a debugger. Otherwise, it's super hard to guess. The issue is environement-specific, i.e. something that for some bizarre reason is not happy on your machine.

A ton of stuff changed between v21 and v22, particularly: new version of SMO and new version of the SqlClient managed driver. SMO (and it's dependencies) is most likely what is going wrong on your machine.

tparikka commented 1 year ago

I can provide more context, absolutely. This runs first from a script called setup-install-modules.ps1:

Write-Host "Installing SqlServer module"
Install-Module -Name SqlServer -Scope AllUsers -AllowClobber

Then in setup-database.ps1:

# Stub in databases as needed, creates Login/Users, and grants Roles
$StartingLocation = Get-Location
Write-Output "Installing / Importing required SQL cmdlet modules."
Import-Module SqlServer

# Initial Database Setup: Login, Databases, Users, Roles
Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Write-Output "Creating SQL Server Logins for SOLUTIONNAME"
Invoke-Sqlcmd -InputFile "setup-database-login-create.sql"

Write-Output "Creating IdentityServer Users/Grants for SOLUTIONNAME"
Invoke-Sqlcmd -InputFile "setup-database-identityserver.sql"

# Return to original working directory
Set-Location $StartingLocation

Write-Host "Database setup is complete. Enter any key to continue past database setup."
[Console]::ReadKey() | Out-Null

There are a number of SQL scripts that are called that I omitted for brevity. The failure occurs when trying to set-location to the SQL server PS drive.

BeniFreitag commented 1 year ago

I'm having the same error in Backup-SqlDatabase since a few days in PowerShell 7.3.3 with SqlServer Module 21.1.18256.

Message        : Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data,
                 Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
TypeName       : Microsoft.SqlServer.Server.SqlContext
TargetSite     : Boolean CallerHavePermissionToUseSQLCLR()
Data           : {}
InnerException :
HelpLink       :
Source         : Microsoft.SqlServer.ConnectionInfo
HResult        : -2146233054
StackTrace     :    at Microsoft.SqlServer.Management.Common.ConnectionManager.CallerHavePermissionToUseSQLCLR()
                    at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor(IRenewableToken token, Boolean
                 removeIntegratedSecurity)
                    at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor()
                    at Microsoft.SqlServer.Management.Common.ServerConnection..ctor(String serverInstance)
                    at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ConnectToServer(String instance,
                 PSCredential cred, Nullable`1 timeout)
                    at Microsoft.SqlServer.Management.PowerShell.BackupRestoreCmdlet`1.ResolveTargets()+MoveNext()
                    at Microsoft.SqlServer.Management.PowerShell.SqlCmdlet`1.ProcessRecord()
                    at Microsoft.SqlServer.Management.PowerShell.SmoCmdlet.ProcessRecord()
                    at System.Management.Automation.CommandProcessor.ProcessRecord()

Uninstall and reinstall the PowerShell-Module didn't solve it. In PowerShell 5.1.22621.963 with SQLPS-Module 14.0 Backup-SqlDatabase works.

Matteo-T commented 1 year ago

Folks - all these issues are very environment-specific. Extremely hard to troubleshoot in isolation without knowing the exact sequence of steps that led to the problem.

I can only speculate that it has to do with .Net7 and its' unification logic when it comes to loading assemblies.

In v22 I've been trying to address a bunch of these issue with a ton of workarounds and heuristics which seems to help the scenarions when multiple modules are loaded (in different orders). As far as I understand, this is a fundamental problelm in PowerShell and it is not specific the the SQLServer module. It's just that PS7 ((or better, .Net7) aggravated it a little. Now, in theory, .Net7 offers a way to get out of trouble, but that's something that all the module implementers (including myself) would have to eventually adapt to - and it's incredibly complex to do.

Can you move away from SQLPS and 14.0 or even v21 and instead try on v22? I won't be able to fix the old ones, but at least I can probably do something for v22.

pluim003 commented 1 year ago

Encountering the same issue after installing SQLServer 2022 CU2 (Express-edition) with SQLModule 21.1.18256. Working on following an online course where we were advised to use a release-version and no pre-release-version although I prefer the most recent versions. Unfortunately uninstalling CU2 doesn't solve the issue. Will give it a try with a newer version of the module.

Update: with 22.0.49 it's working again.

BeniFreitag commented 1 year ago

I also installed SQL Server 2022 (16.0.1000.6) two weeks ago. The occurrence of the PowerShell-problem corresponds well with the installation of SQL 2022.

Just tested SqlServer-Module 22.0.49-preview and Backup-SqlDatabase is working with this version.

But with 22.0.49-preview Invoke-Sqlcmd is no more working:

Invoke-Sqlcmd: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

I'm not tryin to make an encrypted connection to SQL Server. This error didn't occur with SqlServer Module 21.1.18256.

Matteo-T commented 1 year ago

But with 22.0.49-preview Invoke-Sqlcmd is no more working:

Invoke-Sqlcmd: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

This is by design and it is a breaking change mentioned in the RelNotes for v22. It's something that the module is picking up from the new Microsoft.Data.SqlClient 5.x, which is new default (more secure defaults) for connection.

If you want the same (less secure) behavior you had in v21, you need to pass -TrustServerConnection to the Invoke-Sqlcmd. It's the result of addressing #12.

I need to make sure it gets properly outlined on the Wiki as I'm sure a lot of other folks are going to run into it...

BeniFreitag commented 1 year ago

Thanks @Matteo-T. Unfortunately there's currently no ideal solution: Update to v22 requires changes to other scripts and breaks their backward-compatibility. So far, different versions of SQL Server could coexist with no issues. Our system administrators won't be happy to rollout a prerelease version.

When Invoke-Sqlcmd stops working because of an unencrypt connection, shouldn't this apply to all SQL Cmdlets to make that security improvements consistent?

Matteo-T commented 1 year ago

Thanks @Matteo-T. Unfortunately there's currently no ideal solution: Update to v22 requires changes to other scripts and breaks their backward-compatibility. So far, different versions of SQL Server could coexist with no issues. Our system administrators won't be happy to rollout a prerelease version.

When Invoke-Sqlcmd stops working because of an unencrypt connection, shouldn't this apply to all SQL Cmdlets to make that security improvements consistent?

Yes, ideally that should all happen at once. The reality is happening incrementally. I hope to have another batch done by the time I push v22 GA and the remaining added in minor updates (if they are small) or in v23 if it's too big of a breaking change.

bluehenve commented 1 year ago

PowerShell newbee with sql server 2022 - what I'm I doing wrong:

Matteo-T commented 1 year ago

Hi @bluehenve,

Use -TrustServerCertificate instead.

Consider:

Invoke-Sqlcmd -Database tempdb -ServerInstance MYSQLSERVER -Query 'select DB_ID() AS db_id'

Invoke-Sqlcmd: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
Invoke-Sqlcmd:
Line |
   1 |  Invoke-Sqlcmd -Database tempdb -ServerInstance MYSQLSERVER  -Query  …
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Incorrect syntax was encountered while parsing ''.

whereas

Invoke-Sqlcmd -Database tempdb -ServerInstance MYSQLSERVER  -Query 'select DB_ID() AS db_id' -TrustServerCertificate

db_id
-----
    2

By the way, did you get the wrong parameter name from the documentation? If so, could you kindly let me know so I can fix it?

bluehenve commented 1 year ago

Hi @Matteo-T Thanks - it works! I got the wrong parameter from ... see pic below 😊 Can you guide me to where I find the official documentation? Regards, Henrik

Matteo-T commented 1 year ago

One way would be ask PowerShell itself - it's the so-called "offline" documentation, which is included with the module.

Get-Help Invoke-Sqlcmd -Parameter TrustServerCertificate

-TrustServerCertificate [<SwitchParameter>]
    Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

    > This parameter is new in v22 of the module. For more details, see `Strict Connection Encyption` under Related
    Links (#Related-Links).

    Required?                    false
    Position?                    named
    Default value                False
    Accept pipeline input?       False
    Accept wildcard characters?  false

The other option would be the "online" documentation, which I found out few ours ago to be a little out of date. Folks are looking at the reason why that is the case.

bluehenve commented 1 year ago

@Matteo-T Great, thanks

Matteo-T commented 1 year ago

I'm closing this issue, as v22 (22.0.59) moved out of preview and it is now GA.

tparikka commented 1 year ago

Came back to this one today and wanted to thank you for this fix and your work on this module, I'm so grateful for the help!

microsoft / SQLServerPSModule