Why doesn't my SQL executed with Start-Job return the response I expect?

I have a database hosted on SQL Server 2008 that my regular user account does not have access to. To request it, I need to use a special โ€œadminโ€ account (just another AD account, but in different groups from my regular user account).

I came up with using Powershell's background jobs via Start-Job to run queries on this database, since you can run a task with different credentials from your logged in user, and therefore the integrated security in the database is working correctly, since my problem , I watched this day many times and saw that some people use this approach for the same reason, but their results seem to really work, while mine does not for some reason.

I have the following powershell code:

[scriptblock]$sql_block = {
    $Query = "select * from some_table"
    $CW_DBConnection = New-Object Data.SqlClient.SQLConnection
    $CW_DBConnection.ConnectionString = "Data Source=someserver;Initial Catalog=some_database;Integrated Security=SSPI;"
    $CW_DBConnection.Open()

    $Command = New-Object Data.SqlClient.SqlCommand($Query,$CW_DBConnection)
    $Adapter = New-Object Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object Data.DataSet

    $Adapter.SelectCommand = $Command
    [void]$Adapter.Fill($DataSet)

    $CW_DBConnection.Close()

    return $DataSet
}

What do I do through:

$mySQLJob = Start-Job -ScriptBlock $sql_block -Credential $(Get-Credential -UserName AD\MyAdminAccount -Message "Enter Admin Password")
Wait-Job $mySQLJob
$results = Receive-Job $mySQLJob

It all goes smoothly. However, when I come to poll the results object, I see the following:

$results
RunspaceId              : 975030ec-d336-4583-9260-48439bb34292
RemotingFormat          : Xml
SchemaSerializationMode : IncludeSchema
CaseSensitive           : False
DefaultViewManager      : {System.Data.DataViewManagerListItemTypeDescriptor}
EnforceConstraints      : True
DataSetName             : NewDataSet
Namespace               :
Prefix                  :
ExtendedProperties      : {}
HasErrors               : False
IsInitialized           : True
Locale                  : en-GB
Site                    :
Relations               : {}
Tables                  : {System.Data.DataRow}
Container               :
DesignMode              : False
ContainsListCollection  : True

and when I try to go to the Tables bit:

$results.Tables[0]
System.Data.DataRow

$results.Tables[0].GetType()
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     ArrayList                                System.Object

$results.Tables[0][0]
System.Data.DataRow

$results.Tables[0][0].GetType()
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     String                                   System.Object

So, literally, the result is just the string "System.Data.DataRow".

Where did I go up?

. powershell, Admin Start-Job (.. SQL-) , .

+4
1

Powershell Receive-Job DataSet?, , , , - DataSet , Tables [0] .

-, , , SQL.

, , Receive-Job , , , (, , - . UPDATE: . ).

,

return $DataSet

to

return $DataSet.Tables[0]

.

UPDATE: " ", , , . , script, :

$Serialized_DataSet = [System.Management.Automation.PSSerializer]::Serialize($DataSet,2)
return $Serialized_DataSet

, :

$results = Receive-Job $mySQLJob
$deserialized_results = [System.Management.Automation.PSSerializer]::Deserialize($results)

, $deserialized_results.Tables [0] , .

0

All Articles