Powershell Script Error Connecting To SQL Server

Question

Problem

I have a PowerShell script that pulls user information (Domain and/or SQL users, their groups and other information) and spits it out to an Excel/csv file. This script worked in my virtual lab environment; however, when executed from client issued laptop or when I remote directly into server I get the following error:

Error connecting to server [Server Name]

Notes

I have also gone into SSMS and opened PowerShell directly from within and get same error.

I mentioned my virtual lab environment. That is a virtual server cluster I have on my personal laptop. When I execute the script on my virtual environment it executes fine and spits out the Excel file. This environment is a 2 node cluster using Windows Server 2012. My SQL Server is the 2012 Developer version. Powershell version 4.0.

Environments

Client environment varies. On desktop using Windows 7, Powershell 5.0 and SQL Server 2014. The particular server I am connecting to is Windows 2008 R2 and SQL Server 2008 R2 and Powershell 4.0

Script

    clear 
Write-Host -ForegroundColor Green "Starting"  

$MaximumErrorCount = 15000
#import SQL Server module
Import-Module SQLPS -DisableNameChecking


$today=(get-date -f yyyyMMdd-HHmmss)

#a list of instances you want to send the query to
$instances = Get-Content "C:\Users\Jimmy.Johns\Desktop\Instance_list\instance.txt"
$query = 'TSQL. Left out full code for space/formatting'
$databasename = "master"
$instances | 
ForEach-Object {
try {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
     Invoke-Sqlcmd -ServerInstance $_ -Database $databasename -Query $query -QueryTimeout 15   | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today.csv 
} Catch {
    $_ | Out-Null
    "Error connecting to server  $server" | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today--Errors.csv 
    Write-host "Error connecting to server  $server"
 }
} 

Write-Host -ForegroundColor Green "Done"   

I have read http://www.sqlshack.com/connecting-powershell-to-sql-server/ and used connection string and still will not connect.

Update

After changing $_|out-null with $error[0].exception and running from the client desktop I got the following error:

Could not file or assembly 'Microsoft.SQLServer.BatchParser, Version= 12.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system could not load the file specified.

I also got this error when I ran the edited script inside the server.

Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

Answer 1

This particular issue I was able to resolve. I went and installed Active Directory Module Feature on the server thus removing the Import-Module- Active Directory error.

Even though I already had Microsoft.SQLServer.BatchParser assembly installed on the client desktop, I had read a few posts on StackOverflow how the reinstalled SSMS and it worked. So I re-installed SSMS 2014 with SP 2 and it still gave me the same error. My Processor Architecture was x86. So I also installed SQL Server 2014 SMO for 64-bit on my client issued desktop. I have been able to connect to the server and got the scripts running.