powershell issue

chetan Vishwakarma 146 Reputation points
2021-01-15T14:10:44.567+00:00

Hello Everyone ,

I am doing some automation through powershell for daily healthchecks for all the servers , we have around 200 servers , we do connect with Windows authentication to all the server but we have one more SQL server with SA authentication , so for that purpose i need to include that server also into our daily checklist, so for that purpose i have created a powershell script but it is giving me some error.

If you see the code i have used a loop to execute the stored procedure on all the server one by one and than that output is exported into html file.

foreach($Servers in $Computers)

{


    $Query =   'EXEC da..sp_DBStatus'


   if($Servers -eq "ServerwithSAAuthentication")
    {
        $Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers -Username "username" -Password "pwd123"

The above code is for the server which has SQL Server authentication enabled so i am passing username and password
$Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers
foreach ($Item in $Result)
{
At the end of the loop again same server(ServerwithSAAuthentication) is geting stored in the variable $Servers in above script, due to which i am getting error.ideally the server name next in the loop.

Can you please help me here..???

below is the full code for your referance.

set-executionpolicy unrestricted

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,362 questions
Accepted answer
  1. MotoX80 31,571 Reputation points
    2021-01-15T14:53:52.693+00:00

    You should remove the password from the .txt file that you posted.

    but it is giving me some error.

    It would be helpful if you posted the error and what statement it occurred on.

    It appears that you are processing hostedsql3 twice.

    If $result is always processed the same way, then maybe something like this.

    foreach($Servers in $Computers) {
        $Query =   'EXEC da..sp_DBStatus'

       if($Servers -eq "hostedsql3") {
            $Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers -Username "vishwakarmac" -Password "********"  
       } else {
            $Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers
       }   

       # Now process the results 
       foreach ($Item in $Result) {
            $ServerNames = $Item.ServerName
            $DBName = $Item.DBName
            $state_desc = $Item.state_desc
            $recovery_model_desc = $Item.recovery_model_desc
            $log_reuse_wait_desc = $Item.log_reuse_wait_desc
            $HTML += "<TR> 
            <TD>$($ServerNames)</TD> 
            <TD>$($DBName)</TD> 
            <TD>$($state_desc)</TD>   
            <TD>$($recovery_model_desc)</TD>     
            <TD>$($log_reuse_wait_desc)</TD>   
            </TR>" 
        }
}
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. chetan Vishwakarma 146 Reputation points
    2021-01-15T15:18:43.193+00:00

    I cannot use else in the code as i required to run the SP for all the servers, if i will add else condition then it either run the SP for SQL Server authentication Server or windows authentication server.

    This is the error

    Invoke-Sqlcmd : Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
    At C:\Chetan\healthchecksscripts\DB_Status_New.ps1:71 char:19

    • $Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers
    • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    • FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

    Invoke-Sqlcmd :
    At C:\Chetan\healthchecksscripts\DB_Status_New.ps1:71 char:19

    • $Result = Invoke-Sqlcmd ($Query) -ServerInstance $Servers
    • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
    • FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    0 comments
  2. MotoX80 31,571 Reputation points
    2021-01-15T15:48:59.02+00:00

    I cannot use else in the code as i required to run the SP for all the servers,

    My example does run the SP for all servers.

    if i will add else condition then it either run the SP for SQL Server authentication Server or windows authentication server.

    Well... yes. The way that you have it coded for the server named hostedsql3 you are trying to call the SP twice. Once with SQL authentication and the second time with integrated authentication.

    Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    Am I missing something? hostedsql3 only supports SQL authentication, correct? That means that you can't connect with integrated authentication, correct? So in the script you need to have a test to see what arguments need to be used in the Invoke-Sqlcmd statement.

    0 comments
  3. chetan Vishwakarma 146 Reputation points
    2021-01-15T16:35:17.457+00:00

    Thanks a lot your code helped me..

    0 comments