dbatools
dbatools copied to clipboard
dataplat
"unable to load DLL 'Microsoft.Data.SqlClient.SNI.dll'" errors with pwsh and credentials
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
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.
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 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.
Thanks for the feedback. I will leave this open for a while, happy to hear from you.
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.
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.
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).
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.
@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
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.
Oh also, the CreateFile is a misnomer if I had to guess. I think it is actually just trynna open it.
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.
Yes, that account is not aware of that path which alluserscope would fix, I'm guessing. testing now!
I added the github path via registry to all users to emulate alluser scope and tested with success
AW BRUH, I did this all with powershell.exe. BRB
no idea how to work around this :( anyone know more about interoperability?
It is only the core library module that has to be installed in all user scope, follow now. That is a pretty big breaking change for those users that don't use Install-Module or Install-PSResource in their environment. (Not sure we have that many anymore as we used to)
yeah, i drew the line there and wont support install.ps1 anymore. they'll have to find a machine with install-module or save-module or download the zips manually.
This will need some docs if that is the solution. Or like, figure out how to do 7 to 7 without much fuss. Any idea?
?
Not sure remoting plays into here because there is no session created. I'm local on my machine doing this and the impersonation is a thread within pwsh running under that cred context.
I dont believe you can do Invoke-Command -ComputerName localhost -Credential $cred without remoting being enabled. I had to enable it for my machine. Maybe the underlying framework (or we) skips it? or doesnt skip it?
but a session must be created to downgrade to 5.1 from 7? ohh lemme check the pid
innnnnnnnnnnnnterresting
[7ms] C:/github❯ $pid
11952
[1ms] C:/github❯ Invoke-Command -ComputerName workstation -Credential $cred -ScriptBlock { $pid }
8684
[272ms] C:/github❯ Invoke-Command -ComputerName workstation -Credential $cred -ScriptBlock { $pid }
20568
We may need to separate this to another issue because there is a difference between PS Remoting and the issue with credential from our Connect function.
User awareness is responsible for invoke calls to a remote Windows devices. PowerShell remote configuration on their environment has to be changed and managed in order for them to get a PS7 to PS7 session.
The connect using a credential and impersonating is something with the library and/or something PS is doing locally in the same thread/session.
If a user wants to run the module in Windows PowerShell from PS 7 on their local machine they can Import-Module ... -UseWindowsPowerShell. That will create a runspace in PS7 session with a remote session to the 5.1 host on their local machine.
Unfortunately, that's probably not going to be too helpful.
PowerShell 7.3.1
PS C:\github> Import-Module -UseWindowsPowerShell dbatools
WARNING: Module dbatools is loaded in Windows PowerShell using WinPSCompatSession remoting session; please note that all input and output of commands from this module will be deserialized objects. If you want to load this module into PowerShell please use 'Import-Module -SkipEditionCheck' syntax.
PS C:\github> Connect-DbaInstance -SqlInstance sqlcs
ComputerName Name Product Version HostPlatform IsAzure IsClustered ConnectedAs
------------ ---- ------- ------- ------------ ------- ----------- -----------
sqlcs 15.0.4236
Out-Default: urn could not be resolved at level OleDbProviderSetting.
I'm using 7.3.1 and whether I'm passing in a credential or creating a connection with credential I don't see the issue occurring with PS Remoting. A credential being passed to SMO does not interact with PowerShell nor PS Remoting, it is thread based and doing an impersonation only.
