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.

  • 3
    Your script reports any error type as "Error connecting to server" Are you sure that's the case? Does a simple SELECT 1 in a single line Invoke-SqlCmd work? – Forrest Dec 28 '16 at 22:07
  • 2
    Your should at least replace $_ | out-null with write-host $error[0].Exception, because | out-null makes the real failure message disappear. – jyao Dec 28 '16 at 22:16
  • Edited the code and trying a few things. Will update soon. Thank you. – Franklin Dec 28 '16 at 22:26
  • Thank you @jyao. I am still learning and over looked $_ | out-null. That gave me insight into my issue. – Franklin Dec 29 '16 at 15:39
  • Uness you are going to show what error you were actually getting on the other server this question and answer is not going to help anyone. – Shawn Melton Dec 29 '16 at 17:52
up vote -1 down vote accepted

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.

  • 1
    You don't state in your question what the actually issue was because you are overwriting what the error message was, which is not a good answer in my opinion. As well, no part of your script is using AD PowerShell commands so there was no reason to install that module. Installing SSMS 2014 will install SQLPS and SMO, so your install of SMO by itself was not needed. – Shawn Melton Dec 29 '16 at 17:53
  • @ShawnMelton Your help is most appreciated. – Franklin Dec 29 '16 at 20:42

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.