title: Need to open PSSession to each SQL Server to run a query
body: <p>I've got to pull SUM of a column from a table on 7 SQL Servers. I've never used SQL PS before.</p>
  
  <p>Seems the only way I can get it working is to open a PS Session to each server and run the query. Is that right?</p>
  
  <p>I'm trying to work out how to authenticate to each one and just open the PS Session once to get the cmdlets from the first server.</p>
  
  <p>I've tried just opening the first one and then running the query with the Credentials switch but that gives me a logon error.</p>
  
  <pre><code>[array] $SQLServers= @("SQLSERVER1","SQLSERVER2","SQLSERVER3","SQLSERVER4","SQLSERVER5","SQLSERVER6","SQLSERVER7")
  #$credentials = Get-Credential
  $report = @()
  $username = "domain\username"
  $password = Get-Content 'PathToSecureTextFile' | ConvertTo-SecureString
  $cred = new-object -typename System.Management.Automation.PSCredential `
           -argumentlist $username, $password
  
  $reportQuery = @"
  
  SELECT SUM(COLUMNNAME) FROM TABLENAME
  
  "@
  
  foreach ($SQLServer in $SQLServers){
  
      Switch ($SQLServer){
          "SQLSERVER1" {$pod = "POD1";
                        $database = "DATABASE01"}
          "SQLSERVER2" {$pod = "POD2";
                        $database = "DATABASE02"}
          "SQLSERVER3" {$pod = "POD3";
                        $database = "DATABASE03"}
          "SQLSERVER4" {$pod = "POD4";
                        $database = "DATABASE04"}
          "PDC2SQL071" {$pod = "POD5";
                        $database = "DATABASE05"}
          "PDC2SQL072" {$pod = "POD6";
                        $database = "DATABASE06"}
          "PDC2SQL073" {$pod = "POD7";
                        $database = "DATABASE07"}
  
      }
  
  
      Write-Host "you are using Server" $SQLServer "with database " $database
  
      $newSQLPS = New-PSSession -ComputerName $SQLServer -Credential $cred
      Import-Module -Name SQLPS -PSSession $newSQLPS
  
      [int]$dataMigrated = [math]::Round(((Invoke-Sqlcmd -ServerInstance $SQLServer `
           -Database $Database -Query $reportQuery).Column1/1024/1024/1024),2)
  
      $props = [ordered]@{
          Pod = $pod
          DataMigrated = $datamigrated
      }
  
      Write-host "Server $SQLServer Migated $datamigrated GB" -ForegroundColor Green
      Write-Host "==========================================" -ForegroundColor Yellow
  
      $reportRow = New-Object psobject -Property $props
      $report += $reportRow
  
  }
  
  #New-Object PSObject -Property $report
  $report
  </code></pre>
  
  <p>This does work and I get the results I want but I'm just thinking if I could authenticate properly then the script would run a lot quicker.</p>