Run .net script to query SQL table

Steve_O'Connor

So I'm working on leveraging the Configuration Manager Software Approval function to create an SR in service manager with an approval step and then an orchestrator runbook activity to approve the said software request.

Now anyone who's tried this before will probably have come up against the fact that orchestrator will just log the same request over and over again.

So to get over this, I have created a SQL DB with one table and one column, GUID from the approval request from ConfigMgr. I've locked the table to prevent any duplicate GUIDS going in there.

Over to the runbook, it will monitor ConfigMgr every 30 minutes for requests, then next step it will look up the SQL DB for GUIDS, if it doesn't match anything in the table, it passes it onto the rest of the runbook which creates the SR using the template with the approval steps and the final approve in ConfigMgr. It then adds the processed GUID into the DB table so it can't duplicate the request.

Now as usual everything works fine until I get to Orchestrator run .net script. It fails with the powershell version. So I enclose it in the -scriptblock to run.

</code>
<code>

<pre class="CodeBlock"><code>$ReturnValue = Invoke-Command localhost -ScriptBlock  {<br>try {<br><br>$sqlserver = "MYSQLSERVER"<br>$approvaldatabase = "Software Approval GUID"<br>$UserSqlQuery = "DECLARE @DUP VARCHAR(MAX)<br>SET @DUP = 'GUID FROM CONFIGMGR'<br><br>SELECT TOP 50 [RequestGUID], ""DUP"" = <br>CASE <br>WHEN RequestGUID = @DUP THEN 'YES'<br>--WHEN RequestGUID != @DUP THEN 'NULL'<br>ELSE 'NO'<br>END<br>  FROM [Software Approval GUID].[dbo].[RequestGUID]<br>  --where RequestGUID = @DUP"<br><br>$conn = New-Object System.Data.SqlClient.SqlConnection("Server = $sqlserver; Database = $approvaldatabase; Integrated Security = True")<br>$conn.Open()<br>$sqlcmd = $conn.CreateCommand()<br><br># declaration not necessary, but good practice<br>$resultsDataTable = New-Object System.Data.DataTable<br>$resultsDataTable = ExecuteSqlQuery $sqlserver $approvaldatabase $UserSqlQuery <br><br>function ExecuteSqlQuery ($sqlserver, $approvaldatabase, $SQLQuery) {<br>    $Datatable = New-Object System.Data.DataTable<br>    <br>    $Connection = New-Object System.Data.SQLClient.SQLConnection<br>    $Connection.ConnectionString = "server='$sqlserver';database='$approvaldatabase';trusted_connection=true;"<br>    $Connection.Open()<br>    $Command = New-Object System.Data.SQLClient.SQLCommand<br>    $Command.Connection = $Connection<br>    $Command.CommandText = $SQLQuery<br>    $Reader = $Command.ExecuteReader()<br>    $Datatable.Load($Reader)<br>    $Connection.Close()<br>    <br>    return $Datatable | where{$_.DUP -eq "NO"}<br>}<br>#validate we got data<br>$resultsDataTable<br>$RequestGUID = $resultsDataTable.RequestGUID<br>$RequestDUP = $resultsDataTable.DUP<br>}<br>catch<br>{<br>throw $_.Exception.Message<br>}<br>} 

So this then can't pass the credentials, I get a Exception calling "Open" with "0" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." error. It seems that when it runs the script block it uses anonymous login rather than the correct one. I have tried changing the SQL connection string to specify credentials but it still fails login for that user, its as if it can't pass the password or something.

I'd appreciate any help with this one, I'm not a powershell expert by any stretch, so I'm struggling to just get the final touches on this.

Happy to release this one to the community if anyone thinks it would be of any use if and when I get it working.

Geoff_Ross

Hi Steve,

Two things to try. If its just a PowerShell version issue, you can configure Orchestrator to use the latest version of PowerShell (by default its fixed to use version 2). Please see this blog on how to do this.

http://www.sc-orchestrator.eu/index.php/scoblog/128-run-the-powershell-version-of-windows-executing-the-orchestrator-runbook-service-in-run-net-script-activity

If you still need to run it in an inline script then you can first create a new PowerShell session on the server and provide credentials to that, then you can pass that session in when you invoke the script block.

$Session = New-PSSession -ComputerName localhost -Credential $Cred

$ReturnValue = Invoke-Command -Session $session -ScriptBlock  {
...

Geoff

Geoff_Ross

Hi Steve,

Two things to try. If its just a PowerShell version issue, you can configure Orchestrator to use the latest version of PowerShell (by default its fixed to use version 2). Please see this blog on how to do this.

http://www.sc-orchestrator.eu/index.php/scoblog/128-run-the-powershell-version-of-windows-executing-the-orchestrator-runbook-service-in-run-net-script-activity

If you still need to run it in an inline script then you can first create a new PowerShell session on the server and provide credentials to that, then you can pass that session in when you invoke the script block.

$Session = New-PSSession -ComputerName localhost -Credential $Cred

$ReturnValue = Invoke-Command -Session $session -ScriptBlock  {
...

Geoff

Steve_O'Connor

Thanks Geoff, I've got it running the latest version of powershell.

I'll try that credential option next

Geoff_Ross

Great.
Why it doesn't behave like that by default, we'll never know but always happy to help.