Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Powershell SQLPS question

Hello!

I'm trying to implement a Powershell Monitor script but I keep getting the error:

Errors: ==============================================

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

I installed the module on the poller which has the server I'm intending to run this against. However I continue to get this error.

Is there a reason why?

Find more posts tagged with

monitor

sql

sqlps

PowerShell

Accepted answers

All comments

bsciencefiction.tv

open powershell on the poller and run this command

get-module -listavailable

does sqlps show up in the list?

micheal100

are you planning on running the script locally on the Orion server, or remotely on the client? If from the Orion server then it will need to be available there.

mikesky

I have the same problem with SQLPS and SQLASCMDLETS. Both are loaded on the server. Both load under my user account without having to import anything. They show up under get-module -listavailable when I run it from powershell.

When I add get-module -listavailable to the top of my script in the monitor screen, the list it returns is entirely different than what I see in powershell. I've turned on impersonation, but I have a feeling it's not working and I'm still under the SYSTEM account.

I've tried x64 and x86. I've tried loading the modules manually; it always says they can't be found. I put them in C:\Windows\System32\WindowsPowerShell\v1.0\Modules\.

Everything has been a dead-end.

bsciencefiction.tv

mikesky Add

$scriptUser = [Security.Principal.WindowsIdentity]::GetCurrent().Name

Then Log the variable $scriptuser.

that will validate which account is running the scriptl.

bsciencefiction.tv

mikesky Also which version of Powershell are you running and what .net version?

mikesky

NT AUTHORITY\SYSTEM

I have credentials specified in the monitor. I have "Run the script under specified account" checked. When I open up "Edit Script" and run it, I have the correct server and credentials selected.

bsciencefiction.tv

mikesky try running this in your script before

When I add get-module -listavailable to the top of my script in the monitor screen, the list it returns is entirely different than what I see in powershell.

$env:PSModulePath = $env:PSModulePath + ";C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules"

And see if your get-module returns the correct information

mikesky

PS Version 4.0

.NET v4.0.30319

On Server 2008 R2.

mikesky

Same result. Still only seems to load from C:\Windows\system32\WindowsPowerShell\v1.0\Modules\ (but no SQL modules).

mikesky

I think this is the bigger problem since I need it to run under the specified user. SYSTEM will not have access to the data I am querying.

bsciencefiction.tv

The only other thing I can think is to try to import from the fully qualified path

Import-Module "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\SQLPS.PS1"

mikegale

When you launch a powershell prompt make sure you are launching the 32-bit one titled "Windows PowerShell (x86)". If you add your modules in the 32-bit powershell your scripts should work.

mikesky

Everything works perfectly under PS x86 when I'm logged in. I discovered that it doesn't care what credentials I select in SAM, even a deliberately bad password. The script still executes and the parts that don't require SQL modules report data correctly. I opened a ticket with support and my new problem is debug logging isn't recording anything at all for this app ID

mikegale

You'll probably need to load the Snapin in your script so you're sure that the cmdlets are available. Here's one that I use when I'm connecting back to Orion:

if (!(Get-PSSnapin -Name "SwisSnapin" -ErrorAction SilentlyContinue))

{

Add-PSSnapin SwisSnapin -ErrorAction SilentlyContinue

}

hussod

baneland, mikesky - I was having issues with SQLPS a while back as well. What ended up getting it to work for me was using "SetExecutionPolicy unrestricted -confirm:$false" at the beginning of the script. The next line, I just used "Import-Module SQLPS". I never went back to fully clean up the script since my use case ended up cancelling, but I just tested and it still is at least able to execute and pull data back.

Since it does run as system, passing credentials to invoke-sqlcmd wasn't the most fun either. I set it up with:

$cred = get-credential -credential '${CREDENTIAL}'

$pass = $cred.GetNetworkCredential().password

Then in the actual invoke-sqlcmd, I used these: -Username "${USER}" -Password "$pass"

${USER} and ${CREDENTIAL} both are default variables that pull from the SAM template, based on what credential you assign there.

mikesky

Unfortunately, passing the credentials to invoke-ASCmd doesn't work for me because I don't have servers set up to allow remote powershell connections.

Am I mistaken in thinking that checking "Run the script under specified account" should actually run the script as that user (and not just pass their credentials in the variables)?