"unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll'" errors with pwsh and credentials

[paschott]

Verified issue does not already exist?

Yes

What error did you receive?

Started getting "unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' " errors w/ dbatools in pwsh when trying to run commands using credentials.

e.g., Get-DbaMaxMemory machinename -SqlCredential $cred throws that error.

I seem to get it whenever passing in a credential w/ a dbatools function, but that same credential works for a native command like "Enter-PSSession".

Steps to Reproduce

Get-DbaMaxMemory machinename -SqlCredential $cred

Get "unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll' " error

Are you running the latest release?

Yes

Other details or mentions

This command worked within the last month or so as I use dbatools functions pretty regularly and have to pass in alternate credentials to connect to various machines. Here are the changes I can think of:
SSMS v18.11.1 upgrade
dbatools upgrade (to latest releases)
pwsh.exe upgrade to v7.2.2

These functions work on the same machine using Windows PowerShell v5.1

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.2.2
PSEdition Core
GitCommitId 7.2.2
OS Microsoft Windows 10.0.19043
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

n/a

.NET Framework Version

.NET 6.0.3

[paschott]

Quick note - Get-DbaDiskSpace seems to work with the -Credential parameter, but Get-DbaMaxMemory does not work with a credential parameter. Tested using the same credential and the same target machine using pwsh.

[paschott]

Same behavior w/ v1.1.86 and pwsh.exe.

[paschott]

I was able to load the file in the dbatools bin\smo folder by using "add-type" for the file, but still get an Access Denied error when trying to use the -SqlCredential option with dbatools.

[andreasjordan]

Hi @paschott - can you still reproduce the issue?

[paschott]

@andreasjordan I ended up working around it by granting permissions to that "admin" account for the PS Modules folder, so I stopped getting the error there. That's the only machine I really use that for right now so the problem is mitigated, but that's with a workaround. I'll have to try removing permissions from that folder to see if I'm still getting the error w/ the latest pwsh + dbatools module.

[andreasjordan]

Thanks for the feedback. I will leave this open for a while, happy to hear from you.

[potatoqualitee]

I think the new library in the upcoming dbatools 2.0 will suit you a lot better. no idea why the admin thing worked (cuz i dont understand dlls) but im doing it very differently and it should be more reliable.

[paschott]

Well, it was some way it handled the permissions. If I called a dbatools function that queried some server but used credentials. For some reason, those credentials seemed to be used to access my local files. Once I granted permissions to that credential account to my local folder, the calls worked. I hadn't thought about it too much since making that tweak. Of course, that wouldn't work for cases where the domain isn't trusted or similar. I'll re-test shortly once I get over to that machine.

[paschott]

Just verified - I removed permissions for that admin account from my local PowerShell\Modules folder and got the same error. Latest dbatools + pwsh (updated today for both).

[potatoqualitee]

Ahhhh, okay. There will be nothing I can do on my end -- I never considered this as a side effect of installing to the current user scope! Please continue with this workaround or install to the system scope.

[wsmelton]

@potatoqualitee @andreasjordan
I can confirm this issue still exists even in the new code changes coming in 2.0 release.

The issue that is happening is once a credential is provided PowerShell is performing an impersonation trying to create the DLL file in a location within the user's PATH environment. You can observe in the screenshot below all the CreateFile operations occurring where it is going through the paths in my PATH environment trying to create that DLL file.

This is not normal but I'm not aware of why it is happening yet.

The error being returned as ConnectionError is coming from this code section, so I'm not 100% sure why this triggers the DLL being created across the PATH values.

https://github.com/dataplat/dbatools/blob/b8fe96052245edab3e568c11bd43c468a8b0fc8a/functions/Connect-DbaInstance.ps1#L920-L929

[potatoqualitee]

I opened up PowerShell and did nothing (I was planning to import SqlServer) and had similar results

Weirdly, when I import SqlServer, i get no hits for SNI. I just have no clue how they import things but I imagine it's drastically different because everything is compiled.

Ultimately, the permission denied thing makes sense to me if the module is installed in the user scope. Enter-PSSession works because it's available in the global scope. Get-DbaDiskSpace uses purely remotely available calls and can be run against non-SQL Servers, while Test-DbaMaxMemory makes a call to SQL Server and thus needs the SqlClient DLLs. If this worked previously, it's because we used System.Data and not Microsoft.Data. System.Data is available system-wide whereas Microsoft.Data is available in our module. Ultimately, I'd say to install with the AllUsers scope and there should be no issue.

[potatoqualitee]

Oh also, the CreateFile is a misnomer if I had to guess. I think it is actually just trynna open it.

[wsmelton]

All user scope won't fix this because I'm using the module from a saved path (c:\git\dbatools\dbatools.psd1). Recall I'm using the development branch of GitHub (local development) so the module exist under a completely separate path that is not even part of the PSModule paths.