We help IT Professionals succeed at work.

Run SQL Query with Oledb connection string using RunAsAccount

Trying to run Powershell script SQL query using Oledb connection string , New-Object -comobject ADODB.Connection and New-Object -comobject ADODB.Recordset
Need to run this query with RunAsAccount credentials.

Running this in Powershell window by opening it with RunAsAccount Credentials

 $myconnectionstring = "Provider=SQLOleDb;Integrated Security=true;Database=xxxSQL;Server=xxxDBServer"

Getting following error
  Exception calling "Open" with "1" argument(s): "Invalid authorization specification"

***
(for creating monitors in System Center Operations Manager 2012  )
Trying to create rules/monitors based on PowerShell Script using VSAE (Visual Studio Authoring Extensions)

What needs to be corrected.
Thanks
Comment
Watch Question

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Try this:
Provider=sqloledb;Data Source=xxxDBServer;Initial Catalog==xxxSQL;Integrated Security=SSPI
SQL Expert/Infrastructure Architect
Commented:

Author

Commented:
Tried this but getting error in opening OleDb connection.
I think need to use connectingstring with runas account Id which has access to Database.

Can you please suggest a way to test my Powershell script with a run as account??
I am trying to use a PowerShell script to run a SQL query (output of which is 0 or 1)

I need to test the script runs correctly with runas account and gives the required result before processing further.

I am kind of stuck here


Thanks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
What's the error?

Author

Commented:
Connection is not opened
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You need to call the Open() method for the connection before doing any operation on the database.

Author

Commented:
Here is my script, I am trying to create a two state monitor in scom based on the results of the Sql query which is just 0 or 1.
My script just runs thru but does not give any results.

$oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Preparing query against $SQLInstance on Database $Database with query: $SQLQuery .”)

$ADOCon = New-Object -ComObject “ADODB.Connection”
$oResults = New-Object -ComObject “ADODB.Recordset”
$adOpenStatic = 3
$adLockOptimistic = 3
$ADOCon.Provider = “sqloledb”
$ADOCon.ConnectionTimeout = 60
$nowInUTC = (Get-Date).ToUniversalTime()
$conString = “Server=$strServer;Database=$Database;Integrated Security=SSPI”
try {
    $ADOCon.Open($conString)
}
catch {
    $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,1,”Error connecting. Constring: $conString Error: $error”)
}
if ($ADOCon.State -ne 0)
{
   
    $time=measure-command {
        try {    
           
            $oResults.Open($SQLQuery, $ADOCon, $adOpenStatic, $adLockOptimistic)
            $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Successfully executed query against $SQLInstance on Database $Database”)
            If (!$oResults.EOF)
            {
                $oBag.AddValue(‘RecordCount’,$oResults.RecordCount)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)

            }
            else
            {
                $oBag.AddValue(‘RecordCount’,0)
                $oBag.AddValue(‘TransactionTimeMS’, $time.Milliseconds)
            }
            $oBag
        }
        catch
        {
           #write-host “Error running query”
           $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,1,”Error executing query against $SQLInstance on Database $Database with query $SQLQuery”)
        }
    }
    $oResults.Close()
    $ADOCon.Close()

Author

Commented:
Once i have this running with query results then only i can proceed further
Marten RuneSQL Expert/Infrastructure Architect

Commented:
Do a runas in a cmd prompt and open a new cnd window as the desired User.
Ie
Runas /user:domainuser@domain cmd
Type the pwd hit enter and a new cmd opens as that User.
Type powershell and execute your code. Make sure this User has permission to the PS1 file.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I can see that you didn't use my suggestion:
Provider=sqloledb;Data Source=xxxDBServer;Initial Catalog=xxxSQL;Integrated Security=SSPI

Try to change the line:
$conString = “Server=$strServer;Database=$Database;Integrated Security=SSPI”

to:
$conString = “Provider=sqloledb;Data Source=$strServer;Initial Catalog=$Database;Integrated Security=SSPI”

Author

Commented:
I get the following error after changing the connection string and adding parameters to the script like
Param
([string]$SQLInstance= "xxxxDB",[string]$strQuery ="My Query" [string],$Database="xxxSQL")and connectionstring as follows

$conString = “Provider=sqloledb;Server=$strServer;Initial Catalog=$Database;Integrated Security=SSPI”
==========error================
PS D:\temp\03> .\PSQLProbe.ps1
At D:\temp\03\PSQLProbe.ps1:13 char:47
+ $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Preparing query against $SQLInstan ...
+                                               ~
Missing ')' in method call.
At D:\temp\03\PSQLProbe.ps1:13 char:47
+ $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Preparing query against $SQLInstan ...
+                                               ~~~~~~~~~
Unexpected token 'Preparing' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:26 char:105
+ ...  Error: $error”)
+                    ~
Unexpected token ')' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:27 char:1
+ }
+ ~
Unexpected token '}' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:35 char:59
+             $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Successfully executed  ...
+                                                           ~
Missing ')' in method call.
At D:\temp\03\PSQLProbe.ps1:35 char:59
+             $oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Successfully executed  ...
+                                                           ~~~~~~~~~~~~
Unexpected token 'Successfully' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:32 char:13
+         try {
+             ~
Missing closing '}' in statement block.
At D:\temp\03\PSQLProbe.ps1:52 char:144
+ ... uery $SQLQuery”)
+                    ~
The Try statement is missing its Catch or Finally block.
At D:\temp\03\PSQLProbe.ps1:31 char:27
+     $time=measure-command {
+                           ~
Missing closing '}' in statement block.
At D:\temp\03\PSQLProbe.ps1:29 char:1
+ {
+ ~
Missing closing '}' in statement block.
Not all parse errors were reported.  Correct the reported errors and try again.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall

PS D:\temp\03>

++++==end error+++++++++++++====
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I can see a lot of Missing ')' in method call.
Check if you closed correctly all the parenthesis.

Author

Commented:
All the missing ') in method call seems to be around the logging event statement as follows
Not sure what needs to be done
Line13 char:47
here is line 13
$oAPI.LogScriptEvent(“PSSQLProbe.ps1?, 555,0,”Preparing query against $SQLInstance on Database $Database with query: $SQLQuery .”)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I can see 3 ". Shouldn't be 4? (missed to close after the ?)
$oAPI.LogScriptEvent(“PSSQLProbe.ps1?", 555,0,”Preparing query against $SQLInstance on Database $Database with query: $SQLQuery .”)

Author

Commented:
Closed after ? as below but now 2 more errors (as marked) one for Missing and other one for unexpected Token
Similar in all 3

At D:\temp\03\PSQLProbe.ps1:13 char:48

+ $oAPI.LogScriptEvent(“PSSQLProbe.ps1?), 555,0,”Preparing query against $SQLInsta ...
                                                                                           ~
Please notice ~ under "P - closing call                                                                               ~
+                                            
Missing ')' in method call.
At D:\temp\03\PSQLProbe.ps1:13 char:48
+ $oAPI.LogScriptEvent(“PSSQLProbe.ps1?), 555,0,”Preparing query against $SQLInsta ...
+                                                                                         ~~~~~~~
Unexpected token 'Preparing' in expression or statement.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
No, isn't for close the parenthesis but the "
Check the code I posted above.

Author

Commented:
Thanks, some of the errors are gone but here are a few.  Appreciate your help

At D:\temp\03\PSQLProbe.ps1:34 char:132
+ ... ase $Database”)
+                    ~
Missing ')' in method call.
At D:\temp\03\PSQLProbe.ps1:35 char:13
+             If (!$oResults.EOF)
+             ~~
Unexpected token 'If' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:47 char:5
+             }
+              ~
The Try statement is missing its Catch or Finally block.
At D:\temp\03\PSQLProbe.ps1:56 char:2
+     }
+     ~
Unexpected token '}' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:6 char:23
+ [string]$SQLInstance= "xxxxDB",
+                       ~~~~~~~~~
Assignment expression is not valid. The left hand side of an assignment operator needs to be something that can be
assigned to like a variable or a property.
At D:\temp\03\PSQLProbe.ps1:7 char:20
+ [string]$strQuery ="select count(*) from xxxx_process_queue with (nolock) where  ...

Author

Commented:
Line 34
$oAPI.LogScriptEvent((“PSSQLProbe.ps1", 555,0,”Successfully executed query against $SQLInstance on Database $Database”)

checking Try and Catch - for missing method call

Line 6 is my Variable $strQuery -
I have run the query separately and it gives the results (0 or 1) depending on the state.
Not sure why
Assignment expression is not valid. The left hand side of an assignment operator needs to be something that can be
assigned to like a variable or a property.
At D:\temp\03\PSQLProbe.ps1:7 char:20
+ [string]$strQuery ="select count(*) from xxxx_process_queue with (nolock) where

Author

Commented:
here are the new errors..
At D:\temp\03\PSQLProbe.ps1:34 char:132
+ ... ase $Database")
+                    ~
Missing ')' in method call.
At D:\temp\03\PSQLProbe.ps1:35 char:13
+             If (!$oResults.EOF)
+             ~~
Unexpected token 'If' in expression or statement.
At D:\temp\03\PSQLProbe.ps1:7 char:23
+ [string]$SQLInstance= "xxxxDB",
+                       ~~~~~~~~~
Assignment expression is not valid. The left hand side of an assignment operator needs to be something that can be
assigned to like a variable or a property.
At D:\temp\03\PSQLProbe.ps1:8 char:20
+ [string]$strQuery ="select count(*) from xxxx_process_queue with (nolock) where  ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Assignment expression is not valid. The left hand side of an assignment operator needs to be something that can be
assigned to like a variable or a property.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall

Author

Commented:
Here is what i did, I removed the Param Section of the script and added the values directly for the server, and query, all the above errors are gone except this error.

At D:\temp\03\PSQLProbe1.ps1:34 char:132
+ ... ase $Database")
+                    ~
Missing ')' in method call.
At D:\temp\03\PSQLProbe1.ps1:35 char:13
+             If (!$oResults.EOF)
+             ~~
Unexpected token 'If' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall

Author

Commented:
Found the above script error and fixed it, the script runs but got this message
Exception calling "Close" with "0" argument(s): "Operation is not allowed when the object is closed."
At D:\temp\03\PSQLProbe1.ps1:57 char:5
+     $oResults.Close()
+     ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Did following:
 #$oResults.Close() [as $oResults.Close() was already closed as per above error,]

Script completed.
used $oAPI.Return($oBag) to check Datatype for next module but got the following results..
What does it mean??
<DataItem type="System.PropertyBagData" time="2015-03-03T12:57:45.1862745-06:00" sourceHealthServiceId="2881A81E-3BA6-EB9F-309E-34078084A993"/>PS D:\temp\03>

When the query was run using connection string
$conn=new-object System.Data.SqlClient.SQLConnection
the results obtained were
# Item  System.Data.set
Column1
0

I am completely at a loss here
Marten RuneSQL Expert/Infrastructure Architect
Commented:
Alter the sql server to log both successful and failed logins.
Restart sql server service.
Try your code and check the sql errorlog

Author

Commented:
Alternate method works.

Author

Commented:
Thanks Vitor for helping me debug the script.
Marten's suggestion took me on a different path.
found an alternate way of running script using SQL.Client.Connection.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Sorry, was off until now but I'm glad that you resolved this issue.
Anyway, you should have more attention with the parenthesis and quotes in your code. For example, in this error you opened parenthesis twice and closed only once:
Line 34
 $oAPI.LogScriptEvent((“PSSQLProbe.ps1", 555,0,”Successfully executed query against $SQLInstance on Database $Database”)