Xp_CmdShell Powershell Script No Valid Module Found

Question

I'm trying to run a PS1 file in T-SQL using XP_CMDSHELL, like so:

exec xp_cmdshell 'powershell -ExecutionPolicy bypass -command "C:\Users\sleven\Documents\DimAcctImport.ps1"'

The powershell script is as follows:

import-module dbatools
Import-DbaCsv -SqlInstance 'MSSQL' -Database 'Test' -Table 'Account' -Path "R:\Data\Account.csv" -Delimiter ',' -Quote '"' -KeepNulls -NoProgress

This script uses the cmdlet Import-DbaCSV of module DbaTools to import the CSV to the target table.

Here is the error I receive in SSMS:

import-module : The specified module 'dbatools' was not loaded because no valid module file was found in any module 
directory.
At C:\Users\sleven\Documents\DimAcctImport.ps1:1 char:1
+ import-module dbatools
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (dbatools:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

The module is installed and runs as expected if I copy the PowerShell script and run it in PS ISE.

I'm using SQL Developer Edition on the same PC as I'm using to run the sp - my local PC.

What am I missing?

EDIT: Adding output from get-module and $PSVersionTable

PS> (Get-Module -ListAvailable dbatools).Path:
C:\Users\sleven\Documents\WindowsPowerShell\Modules\dbatools\0.9.834\dbatools.psd1

PS> $PSVersionTable

Name                           Value                                                                                                  
----                           -----                                                                                                  
PSVersion                      5.1.18362.145                                                                                          
PSEdition                      Desktop                                                                                                
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                
BuildVersion                   10.0.18362.145                                                                                         
CLRVersion                     4.0.30319.42000                                                                                        
WSManStackVersion              3.0                                                                                                    
PSRemotingProtocolVersion      2.3                                                                                                    
SerializationVersion           1.1.0.1

Can you add the line (get-module -ListAvailable dbatools).path list the output? Also, what version are you using ($PSVersionTable)? Does it work if you import module using its path (Import-Module C:\Path\to\module.psm1)? — Robert Dyjas
– Robert Dyjas, Commented Jul 17, 2019 at 17:50
Ok, change first line of script to Import-Module 'C:\Users\sleven\Documents\WindowsPowerShell\Modules\dbatools\0.9.834\dbatools.psd1' and see if it works. — Robert Dyjas
– Robert Dyjas, Commented Jul 17, 2019 at 17:56
That did work! I had to move the file because SQL Server doesn't have permission to R:\, so I moved the file to C:\ and it worked perfectly. @robdy — sleven
– sleven, Commented Jul 17, 2019 at 18:02

Robert Dyjas · Accepted Answer · 2019-07-17 19:20:36Z

4

Using Import-Module cmdlet tells PowerShell to look for cmdlet only in certain folders. You can check these folders by checking environment variable PSModulePath (splitting added for better visibility):

$env:PSModulePath -split ';'

In that case, when running PowerShell by xp_cmdshell, that folder is not included in PSModulePath (might happen as well if you use different account as your module is currently in your profile folder). Now you have two options:

Reference the module using its path, not name. You can get path using (get-module -ListAvailable dbatools).path:

# Replace the path with the path you found with Get-Module
Import-Module 'C:\path\to\module\dbatools.psd1

Modify your $env:PSModulePath. As that topic is more broader, let me give you the link to the docs. Remember that you should set that variable for the user running xp_cmdshell. As RThomas mentioned, by default it's not your user account. See linked answer for explanation on that topic.

NOTE (credits go to @David Browne - Microsoft from his comments):

If you're going to set the environment variable, it should be a system environment variable, as you wouldn't want this process to break if you change the SQL Server service account. And remember, setting a system environment variable requires a reboot for services to see the change.

edited Jul 17, 2019 at 19:20

answered Jul 17, 2019 at 18:09

Robert Dyjas

5,2373 gold badges22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Browne - Microsoft Over a year ago

If you're going to set the environment variable, it should be a system environment variable, as you wouldn't want this process to break if you change the SQL Server service account. And remember, setting a system environment variable requires a reboot for services to see the change.

Robert Dyjas Over a year ago

Thanks David, I added your important remark to my answer!

RThomas · Accepted Answer · 2019-07-17 18:05:08Z

2

It's likely an account issue. Keep in mind that running xp_cmdshell by default runs everything as the service account behind the SQL Server instance. So you'll want to verify what account this is.

If it's a built in system account this can cause strange behavior when it comes to rights.

If it's a windows or a domain account then you'll want to test externally to SQL Server by running the PS file not as yourself but as the same account SQL Server uses as a service account. You can do this easily by opening the cmd shell with shift/right click and specifying the other account.

Your other option is to set up a proxy account for the xp_cmdshell call to use. Instructions on how to do this can be found in Microsoft documentation.

answered Jul 17, 2019 at 18:05

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

2 Comments

RThomas Over a year ago

Sounds like you came to that same conclusion with robdy in the comment section. I'll leave the answer simply for the link to running xp_cmdshell under a proxy account.

sleven Over a year ago

Thanks for the info. I didn't even consider that xp_cmdshell runs under the service account. Makes sense though. I just tried to install another module and I needed to add -Scope CurrentUser to get around access restrictions.

Collectives™ on Stack Overflow

Xp_CmdShell Powershell Script No Valid Module Found

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related