Receive-Job 返回的意外变量类型 [英] Unexpected variable type returned by Receive-Job

问题描述

我正在尝试执行 Invoke-Sqlcmd 命令(来自 SqlServer 模块) 以不同的 AD 用户身份运行查询.我知道有 -Credential 参数，但这似乎不起作用.

因此，我认为使用 Start-Job 可能是一种选择，如下面的代码片段所示.

$username = 'dummy_domain\dummy_user'$userpassword = 'dummy_pwd' |ConvertTo-SecureString -AsPlainText -Force$credential = New-Object -TypeName System.Management.Automation.PSCredential ($username, $password)$job = Start-Job -ScriptBlock {Import-Module SqlServer;Invoke-Sqlcmd -query "exec sp_who" -ServerInstance 'dummy_mssql_server' -As DataSet} -Credential $credential$data = Receive-Job -Job $job -Wait -AutoRemoveJob

然而，当查看作业返回的变量类型时，它不是我所期望的.

<代码>>$data.GetType().FullName系统.管理.自动化.PSObject>$data.Tables[0].GetType().FullNameSystem.Collections.ArrayList

如果我直接运行ScriptBlock中的代码，这些是PS返回的变量类型:

<代码>>$data.GetType().FullName系统.数据.数据集>$data.Tables[0].GetType().FullName系统.数据.数据表

我尝试将 $data 变量转换为 [System.Data.DataSet]，这导致了以下错误消息:

无法将值System.Data.DataSet"转换为类型System.Data.DataSet".错误:无法转换类型的System.Data.DataSet"值Deserialized.System.Data.DataSet"输入System.Data.DataSet".

问题:

1. 是否有更好的方法使用 Invoke-Sqlcmd 命令在不同的 AD 帐户下运行 SQL 查询?
2. 有没有办法在调用 Receive-Job 时获得正确/预期的变量类型返回?

更新

当我运行 $data.Tables |Get-Member，返回的属性之一是:

Tables 属性 Deserialized.System.Data.DataTableCollection {get;set;}

解决方案

2. 有没有办法在调用 Receive-Job 时获得正确/预期的变量类型返回?

由于使用后台作业，您失去了类型保真度:您返回的对象是原始类型的无方法模拟.

手动重新创建原始类型不值得付出努力，甚至可能无法实现 - 尽管使用仿真可能就足够了.

更新:作为根据您自己的答案，从使用 System 切换.DataSet 到 System.DataTable 为您提供了有用的模拟.^[1]

有关详细信息，请参阅底部部分.

<块引用>

1. 是否有更好的方法使用 Invoke-Sqlcmd 命令在不同的 AD 帐户下运行 SQL 查询?

您需要一个 in-process 调用方法来保持类型保真度，但是如果您想冒充其他用户.

例如，Start-Job 的进程内(基于线程)替代方案 - Start-ThreadJob - 没有 -Credential 参数.

因此，您最好的选择是尝试使 Invoke-SqlCmd 的 -Credential 参数为您工作，或者找到一种不同的进程内方式来使用给定用户的凭据运行您的查询.

---

后台作业/远程处理/迷你外壳中对象的序列化和反序列化:

每当 PowerShell 跨进程边界编组对象时，它都会在源头采用基于 XML 的序列化和反序列化在目的地.

这发生在 PowerShell 远程处理 的上下文中(例如，Invoke-Command 调用与
-ComputerName 参数)以及后台作业(Start-Job) 和所谓的 mini-shells(在调用 PowerShell 时隐式使用)来自 PowerShell 自身的 CLI带有脚本块；例如，powershell.exe { Get-Item/}).

这种反序列化仅为有限的一组已知类型维护类型保真度，如MS-PSRP，PowerShell 远程处理协议规范.也就是说，只有一组固定类型的实例被反序列化为原始类型.

所有其他类型的实例都模拟:类似列表的类型变为 [System.Collections.ArrayList] 实例，字典类型变为 [hasthable] 实例和其他类型 成为无方法(仅属性)自定义对象([pscustomobject] 实例)，其 .pstypenames 属性包含以 Deserialized. 为前缀的原始类型名称(例如，Deserialized.System.Data.DataTable)，如以及类型的基本类型(继承层次结构)的相同前缀名称.

此外，非-[pscustomobject]实例的对象图的递归深度仅限于1 level - 请注意，这包括 PowerShell 自定义类，使用 class 关键字创建:也就是说，如果输入对象的属性值不是众所周知的实例类型本身(后者包括单值类型，包括 .NET 原始类型，例如 [int]，而不是由多个属性组成的类型)，它们被它们的 替换.ToString() 表示(例如，类型 System.IO.DirectoryInfo 有一个 .Parent 属性，它是另一个 System.IO.DirectoryInfo> 实例，这意味着 .Parent 属性值序列化为 .ToString() 代表该实例的描述，这是它的完整路径字符串)；简而言之:非自定义(标量)对象序列化，使得本身不是众所周知类型实例的属性值被它们的.ToString()表示替换em>;有关具体示例，请参阅此答案.
相比之下，显式通过Export-Clixml 默认深度为 2.

根据原始类型，您可能能够手动重建原始类型的实例，但这不能保证.(您可以通过在给定的自定义对象上调用 .pstypenames[0] -replace '^Deserialized\.' 来获取原始类型的全名.)

不过，根据您的处理需求，对原始对象的模拟可能就足够了.

---

^{[1] 使用 System.DataTable 会产生可用的模拟对象，因为您获得了一个模拟表的 System.Collections.ArrayList 实例，并且自定义具有其 System.DataRow 实例的原始属性值的对象.这样做的原因是 PowerShell 具有将 System.DataTable 隐式处理为 其数据行数组 的内置逻辑，而同样不适用于 System.DataSet.}

I'm trying to execute the Invoke-Sqlcmd command (from the SqlServer module) to run a query as a different AD user. I know there's the -Credential argument, but that doesn't seem to work.

Thus, I thought using Start-Job might be an option, as shown in the snippet below.

$username = 'dummy_domain\dummy_user'
$userpassword = 'dummy_pwd' | ConvertTo-SecureString -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential ($username, $password)

$job = Start-Job -ScriptBlock {Import-Module SqlServer; Invoke-Sqlcmd -query "exec sp_who" -ServerInstance 'dummy_mssql_server' -As DataSet} -Credential $credential

$data = Receive-Job -Job $job -Wait -AutoRemoveJob

However, when looking at the variable type that the job returned, it isn't what I expected.

> $data.GetType().FullName
System.Management.Automation.PSObject

> $data.Tables[0].GetType().FullName
System.Collections.ArrayList

If I run the code in the ScriptBlock directly, these are the variable types that PS returns:

> $data.GetType().FullName
System.Data.DataSet

> $data.Tables[0].GetType().FullName
System.Data.DataTable

I tried casting the $data variable to [System.Data.DataSet], which resulted in the following error message:

Cannot convert value "System.Data.DataSet" to type "System.Data.DataSet". 
Error: "Cannot convert the "System.Data.DataSet" value of type 
"Deserialized.System.Data.DataSet" to type "System.Data.DataSet"."

Questions:

1. Is there a better way to run SQL queries under a different AD account, using the Invoke-Sqlcmd command?
2. Is there a way to get the correct/expected variable type to be returned when calling Receive-Job?

Update

When I run $data.Tables | Get-Member, one of the properties returned is:

Tables  Property  Deserialized.System.Data.DataTableCollection {get;set;}    

解决方案

2. Is there a way to get the correct/expected variable type to be returned when calling Receive-Job?

Due to using a background job, you lose type fidelity: the objects you're getting back are method-less emulations of the original types.

Manually recreating the original types is not worth the effort and may not even be possible - though perhaps working with the emulations is enough.

Update: As per your own answer, switching from working with System.DataSet to System.DataTable resulted in serviceable emulations for you.^[1]

See the bottom section for more information.

1. Is there a better way to run SQL queries under a different AD account, using the Invoke-Sqlcmd command?

You need an in-process invocation method in order to maintain type fidelity, but I don't think that is possible with arbitrary commands if you want to impersonate another user.

For instance, the in-process (thread-based) alternative to Start-Job - Start-ThreadJob - doesn't have a -Credential parameter.

Your best bet is therefore to try to make Invoke-SqlCmd's -Credential parameter work for you or find a different in-process way of running your queries with a given user's credentials.

---

Serialization and deserialization of objects in background jobs / remoting / mini-shells:

Whenever PowerShell marshals objects across process boundaries, it employs XML-based serialization at the source, and deserialization at the destination.

This happens in the context of PowerShell remoting (e.g., Invoke-Command calls with the
-ComputerName parameter) as well as in background jobs (Start-Job) and so-called mini-shells (which are implicitly used when you call the PowerShell CLI from inside PowerShell itself with a script block; e.g., powershell.exe { Get-Item / }).

This deserialization maintains type fidelity only for a limited set of known types, as specified in MS-PSRP, the PowerShell Remoting Protocol Specification. That is, only instances of a fixed set of types are deserialized as their original type.

Instances of all other types are emulated: list-like types become [System.Collections.ArrayList] instances, dictionary types become [hasthable] instances, and other types become method-less (properties-only) custom objects ([pscustomobject] instances), whose .pstypenames property contains the original type name prefixed with Deserialized. (e.g., Deserialized.System.Data.DataTable), as well as the equally prefixed names of the type's base types (inheritance hierarchy).

Additionally, the recursion depth for object graphs of non-[pscustomobject] instances is limited to 1 level - note that this includes instance of PowerShell custom classes, created with the class keyword: That is, if an input object's property values aren't instance of well-known types themselves (the latter includes single-value-only types, including .NET primitive types such as [int], as opposed to types composed of multiple properties), they are replaced by their .ToString() representations (e.g., type System.IO.DirectoryInfo has a .Parent property that is another System.IO.DirectoryInfo instance, which means that the .Parent property value serializes as the .ToString() representation of that instance, which is its full path string); in short: Non-custom (scalar) objects serialize such that property values that aren't themselves instances of well-known types are replaced by their .ToString() representation; see this answer for a concrete example.
By contrast, explicit use of CLI XML serialization via Export-Clixml defaults to a depth of 2.

Depending on the original type, you may be able to reconstruct instances of the original type manually, but that is not guaranteed. (You can get the original type's full name by calling .pstypenames[0] -replace '^Deserialized\.' on a given custom object.)

Depending on your processing needs, however, the emulations of the original objects may be sufficient.

---

^{[1] Using System.DataTable results in usable emulated objects, because you get a System.Collections.ArrayList instance that emulates the table, and custom objects with the original property values for its System.DataRow instances. The reason this works is that PowerShell has built-in logic to treat System.DataTable implicitly as an array of its data rows, whereas the same doesn't apply to System.DataSet.}

这篇关于Receive-Job 返回的意外变量类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！