Powershell SQLPS module not importing within C#

Question

I'm attempting to execute a SQL Query from within Powershell, within C#. I have been successful in doing so with ActiveDirectory cmdlets and wanted to take it one step further.

My first issue is while the following format works with ActiveDirectory (and in the ISE) it fails in C#:

using (PowerShell pS = PowerShell.Create())
    {
        pS.AddCommand("import-module");
        pS.AddArgument("sqlps");
        pS.Invoke();
    }

I've long since had the security set to Unrestricted, but the error I'm getting is:

CmdletInvocationException was unhandled

File C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\sqlps\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

However, if I run like this I get no error, though a later "Get-Module -all" call shows no sign of the module:

using (PowerShell pS = PowerShell.Create())
    {
        pS.AddScript("Import-Module sqlps");
        pS.Invoke();
    }

If I then try importing the ActiveDirectory module and calling Get-Module, it shows nothing.

What's going on here?

Answer 1

I'm not that great with C sharp but when calling scripts from outside of powershell there is a flag when executing the program to bypass the execution policy, i.e.

powershell.exe -executionpolicy bypass -command "& '\\somepath\somescript.ps1' "

This allows remote scripts to be called, as even with unrestricted set I still found that it wanted to prompt for the execution of some scripts so for instance in the task scheduler it would simply fail to run.

Also when importing SQLPS I've also found it's useful to add the -DisableNameChecking flag, you may also want to push your location beforehand and pop it afterwards otherwise you will end up in the SQLPS PSdrive with no access to local locations if you need it.

Answer 2

Did you try something like this?

        PowerShell ps = PowerShell.Create();
        ps.AddScript("set-executionpolicy unrestricted -scope process");
        ps.AddScript("import-module sqlps");
        ps.AddScript("get-module sqlps");

        var m = ps.Invoke();
        foreach (var mm in m.Select(x => x.BaseObject as PSModuleInfo))
            Console.WriteLine(new { mm.Name, mm.Version });

Answer 3

I had a similar issue with the sqlServer ps module. Looks like when executing from C# you need to load the modules manually into the runspace in order for this to work.

string scriptText = File.ReadAllText("yourScript.ps1");

//This is needed to use Invoke-sqlcommand in powershell. The module needs to be loaded into the runspace before executing the powershell.

InitialSessionState initial = InitialSessionState.CreateDefault();
initial.ImportPSModule(new string[] { @"SqlServer\SqlServer.psd1" });

Runspace runspace = RunspaceFactory.CreateRunspace(initial);
runspace.Open();

using (PowerShell psInstance = PowerShell.Create())
{
   psInstance.Runspace = runspace;
   psInstance.AddScript(scriptText);
   var PSOutput = psInstance.Invoke();
}

Also add all the references located in the SqlServer.psd1. This file is usually found in "C:\Program Files\WindowsPowerShell\Modules\SqlServer". I added to folder to my solution to be able to execute on remote servers. You need to add Microsoft.SqlServer.BatchParser.dll reference in order to execute invoke-sqlcommand from the Powershell. You should be able to do the same for sqlps module. Rather use SqlServer as it is newer.