cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

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?

0 Kudos
16 Replies

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.

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

0 Kudos

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.

0 Kudos

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)?

0 Kudos

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

}

0 Kudos
Level 9

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.

0 Kudos

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

0 Kudos

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

0 Kudos

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"

0 Kudos

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

0 Kudos

PS Version 4.0

.NET v4.0.30319

On Server 2008 R2.

0 Kudos

mikesky  Add

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

Then Log the variable $scriptuser.

that will validate which account is running the scriptl.

0 Kudos

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.

0 Kudos

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.

0 Kudos
Level 11

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.

0 Kudos

open powershell on the poller and run this command

get-module -listavailable

does sqlps show up in the list?