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
ddd1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
Provider=sqloledb;Data Source=xxxDBServer;Initial Catalog==xxxSQL;Integrated Security=SSPI
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ddd1234Author 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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the error?
0
ddd1234Author Commented:
Connection is not opened
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to call the Open() method for the connection before doing any operation on the database.
0
ddd1234Author 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()
0
ddd1234Author Commented:
Once i have this running with query results then only i can proceed further
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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”
0
ddd1234Author 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+++++++++++++====
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see a lot of Missing ')' in method call.
Check if you closed correctly all the parenthesis.
0
ddd1234Author 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 .”)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 .”)
0
ddd1234Author 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, isn't for close the parenthesis but the "
Check the code I posted above.
0
ddd1234Author 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  ...
0
ddd1234Author 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
0
ddd1234Author 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
0
ddd1234Author 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
0
ddd1234Author 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
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Alter the sql server to log both successful and failed logins.
Restart sql server service.
Try your code and check the sql errorlog
0
ddd1234Author Commented:
Alternate method works.
0
ddd1234Author 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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”)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.