Issue running Invoke-SQLCmd in PowerShell

I’m very novice in PowerShell and I have a ps script that executes DB queries. But it is failing at the very beginning, and I cannot figure out what is wrong. It seems that Syntacs is correct.

I’m getting 2 errors: the main error is a Parsing error, and the 2nd one is less important (although help would be appreciated too),
Here is the error I’m getting (I suspect the Parsing error, is not because of the Certificate error):

PS C:\Admin> .\Cancel.ps1 -Client  MyClient
================ Define Cancel Type ================
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
Verifying Client...
Invoke-SQLCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The
certificate chain was issued by an authority that is not trusted.)
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-SQLCmd : Incorrect syntax was encountered while parsing ''.
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
PS C:\Admin>

And here is the beginning of my script (that seems where it is failing at):

[CmdletBinding()]

param( [string][Parameter(Mandatory=$true, Position=0, HelpMessage="Missing defined client")]
       [ValidateNotNullOrEmpty()]$Client,
       
       [string][Parameter(Position=1)]$Secondary
)

Install-Module sqlserver
Update-Module sqlserver
Import-Module "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.ps1" –DisableNameChecking

Function Verify-Client {Param([string]$Client)
    $Query = "SELECT * FROM DBO.MyTable WHERE Name="$Client""
    $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Output
    
    If ($Output -eq $null) {
        Write-Error 'FATAL ERROR: Client does not exist!'
        Break
    }
    Else {
        ## Build Client Array
        [ARRAY]$Script:ClientDetails = $Output
        Write-Host '  Client Found!'
    }
}

Here is my PowerShell version:

Major Minor Build Revision


5 1 17763 4720

  • You may need to use Invoke-Sqlcmd -TrustServerCertificate.

    – 

  • What version of PowerShell are you using? $PSVersionTable.PSVersion.ToString()` Why are you using SQLPS.ps1?

    – 

  • Use SQL Server Management Studio and check log files in the explorer under management. The logs will show the user/group that is being used for each login which occurs for each query to the server. The error message is bad. If TLS failed than a connection would not complete. The error says a connection completed. The permission to access either the server or database need to be modified for the user, or you need to a different user. Check the connection string. You should be using a window credential which is Integrated Security = true, not a user and password.

    – 

Leave a Comment