NCPA Doesn't Load SQLServer Powershell Module

[twakefield]

Hello,

I wrote a PS1 script that uses the SQLServer module to query the count of records from a single table and report back based on the results. I am using NCPA installed on the target SQL Server to run the script. When I run it directly from the NCPA\Plugins directory, the script works as expected and gives me the count of records. However, when I set up the check in Nagios it throws an error that is not able to load the SQLServer module because no valid module was found in the module directory. It doesn't seem to make a difference if I import the module by name or using the full path to the file. I'm also getting that the server principal NT AUTHORITY\\SYSTEM doesn't have access to the database, but I'm not sure if this is because it's failing to load the module or if it's a separate unrelated issue. The full output of the check is below with the appropriate redaction.

Any assistance is greatly appreciated.

File returned contained:
{
"returncode": 3,
"stdout": "Import-Module : The specified module 'SQLServer' was not loaded because no \
valid module file was found in any module directory.\
At C:\\\\Program Files (x86)\\\\Nagios\\\\NCPA\\\\plugins\\\\myscript.ps1:19 \
char:1\
+ Import-Module SQLServer\
+ ~~~~~~~~~~~~~~~~~~~~~~~\
+ CategoryInfo : ResourceUnavailable: (SQLServer:String) [Import- \
Module], FileNotFoundException\
+ FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Comm \
ands.ImportModuleCommand\
\
Invoke-SqlCmd : The server principal \\"NT AUTHORITY\\\\SYSTEM\\" is not able to \
access the database \\"mydatabase\\" under the current security context.\
At C:\\\\Program Files (x86)\\\\Nagios\\\\NCPA\\\\plugins\\\\myscript.ps1:24 \
char:18\
+ $indexRequests = Invoke-SqlCmd -ServerInstance $sqlServer -Query $sqlCommand\
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS \
hellSqlExecutionException\
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe \
ll.GetScriptCommand\
\
UNKNOWN script state"
}
Import-Module : The specified module 'SQLServer' was not loaded because no
valid module file was found in any module directory.
At C:\\Program Files (x86)\\Nagios\\NCPA\\plugins\\myscript.ps1:19
char:1
+ Import-Module SQLServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (SQLServer:String) [Import-
Module], FileNotFoundException
+ FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Comm
ands.ImportModuleCommand

Invoke-SqlCmd : The server principal "NT AUTHORITY\\SYSTEM" is not able to
access the database "mydatabase" under the current security context.
At C:\\Program Files (x86)\\Nagios\\NCPA\\plugins\\myscript.ps1:24
char:18
+ $indexRequests = Invoke-SqlCmd -ServerInstance $sqlServer -Query $sqlCommand
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
hellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
ll.GetScriptCommand

UNKNOWN script state

[scottwilkerson]

Sounds like you need to manually update the module path.

Check to see that this directory exists on the computer.

I would try add something similar to this, replacing the path with the actual path

Code: Select all

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

[twakefield]

Here's the contents of my $env:PSModulePath:

C:\Users\myusername\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\

I don't think it's an issue with the module path directly, since the script is able to import the module successfully when executed locally. I also made sure that the module exists at all of the paths above and it doesn't seem to have an effect. It only seems to fail when invoked through NCPA.

[scottwilkerson]

Here's the contents of my $env:PSModulePath:

C:\Users\myusername\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\

I don't think it's an issue with the module path directly, since the script is able to import the module successfully when executed locally. I also made sure that the module exists at all of the paths above and it doesn't seem to have an effect. It only seems to fail when invoked through NCPA.

The thinking was that the above may be the $env:PSModulePath: for your user, but not what NCPA runs as, and adding the addition of something like this above the import might allow NCPA to see the module

Code: Select all

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

[twakefield]

NCPA is running as LocalSystem on this server so I'm not entirely sure what module path it would be reading from. In any event, I was able to get the check to function properly by switching to the SqlPS module instead of SQLServer. No clue why this module imports and the other one does not but this seems to resolve the issue. Thanks!

[scottwilkerson]

NCPA is running as LocalSystem on this server so I'm not entirely sure what module path it would be reading from. In any event, I was able to get the check to function properly by switching to the SqlPS module instead of SQLServer. No clue why this module imports and the other one does not but this seems to resolve the issue. Thanks!

Glad to hear you resolved it!

Locking thread