跨服务器通过SQL Server执行PowerShell脚本遇模块缺失报错的技术问询

Alright, let's tackle this problem step by step. The core issue here is that your PowerShell script running on Server A can't access the module DLL stored on Server B—since Server A's local file system doesn't know about Server B's files by default. Here are several practical fixes you can try:

Option 1: Map Server B's Module Directory as a Network Drive #

This lets Server A access Server B's files through a mapped drive, which your script can reference easily.

• Critical prerequisite: Ensure the SQL Server service account on Server A has read access to the shared directory on Server B where the module DLL is stored.
• Modify your xp_cmdshell command to map the drive first, run the script, then clean up the drive:

  EXEC master..xp_cmdshell 'net use Z: \\ServerB\ModuleSharePath /user:YourDomain\SQLServiceAccount YourPassword && cd.. && "C:\Program Files\Powershell\6\pwsh.exe" -File "C:\Users\sprasad\Desktop\script\command1.ps1" && net use Z: /delete'
  

• Update the Import-Module line in your command1.ps1 to use the mapped drive path (e.g., Z:\Core.PowershellModule.TradeLoader.dll).

Option 2: Copy the Module Files to Server A Locally #

This is the simplest and most stable fix if you don't need the module to stay synced with Server B in real-time.

• Copy Core.PowershellModule.TradeLoader.dll (and any associated dependency files) from Server B to a PowerShell module directory on Server A. Common paths include:
  • C:\Program Files\WindowsPowerShell\Modules\ (for system-wide modules)
  • C:\Users\sprasad\Documents\WindowsPowerShell\Modules\ (for user-specific modules)
• Your script's Import-Module command will then work without any path changes, since it can find the module locally.

Option 3: Use a UNC Path Directly in Your PowerShell Script #

Skip mapping a drive entirely by referencing Server B's shared directory directly via UNC path.

• Prerequisite: The SQL Server service account on Server A must have read permissions to the UNC path.
• Update the Import-Module line in command1.ps1 to use the full UNC path:

  Import-Module "\\ServerB\ModuleSharePath\Core.PowershellModule.TradeLoader.dll"
  

• This avoids drive mapping overhead but requires careful permission setup to prevent access denied errors.

Option 4: Run the Script Directly on Server B via PowerShell Remoting #

Since your data files and batch script are already on Server B, why not execute the script there directly?

• Prerequisite: Enable PowerShell Remoting on Server B (run Enable-PSRemoting -Force in an elevated PowerShell session). Also, ensure the SQL Server service account has permissions to run remote commands on Server B.
• Modify your xp_cmdshell command to trigger the script remotely:

  EXEC master..xp_cmdshell '"C:\Program Files\Powershell\6\pwsh.exe" -Command "Invoke-Command -ComputerName ServerB -ScriptBlock { & \"C:\Path\To\command1.ps1\" } -Credential (Get-Credential)"'
  

• For automation, you can store credentials securely using Get-Credential | Export-Clixml instead of manually entering them each time.

内容的提问来源于stack exchange，提问作者shwetank prasad