- Use Azure Bastion as a jump host for RDP and SSH - Tue, Apr 18 2023
- Azure Virtual Desktop: Getting started - Fri, Apr 14 2023
- Understanding Azure service accounts - Fri, Mar 31 2023
Let's say you want to use PowerShell to interact with a remote SQL Server computer on your network. Sure, you could use Enter-PSSession to log in to the remote server. You could also use Invoke-Command to send a scriptblock there. But no--you want to run SQL PowerShell (SQLPS) module commands on your local Windows 10 workstation.
If you're like me, you'll probably mount the SQL Server installation media and install the client tools. That procedure works fine and dandy with SQL Server but doesn't work at all with, say, SharePoint Server.
Today I'd like to show you a little-known PowerShell trick called implicit remoting. With implicit remoting, we can load modules from a remote server into our local system's PowerShell session and run the commands "natively."
Implicit remoting is especially powerful when you realize that you can use it with open-source PowerShell v6.0 on your macOS or Linux system to work with Windows Server roles and services that would otherwise be unavailable to you locally. Let's get started.
Establish the remote session
Let's imagine you're sitting at a Windows 10 Enterprise Edition workstation. Your organization consists of a single Active Directory domain, and you have a member server named SQLSERVER1 that runs an instance of the SQL Server 2014 database engine.
From an elevated PowerShell session on your computer, let's define a new remoting session with the database server:
$s = New-PSSession -ComputerName 'sqlserver1' -Credential (Get-Credential)
If you're already logged on to your machine as a SQL Server admin (which you shouldn't be in keeping with the IT security principle of least privilege) then you won't need the ‑Credential parameter.
What's cool about creating persistent sessions in PowerShell as opposed to the Enter-PSSession and Exit-PSSession interactive remoting approach is that we have so much more control over the session itself.
Create a new PowerShell module
SQL Server 2014 has a single module named sqlps that contains the database engine cmdlets.
Let's dump the sqlps cmdlets into our remoting session like so:
Invoke-Command -ScriptBlock { Import-Module -Name sqlps } -Session $s
At this point the SQL Server commands still exist solely (a) on the remote server and (b) in our remoting session. Our coup de grâce now is to export those session-stored commands into our local PowerShell console instance:
PS C:\> Export-PSSession -Session $s -CommandName *-Sql* -OutputModule RemSQL -AllowClobber
Directory: C:\Users\tim\Documents\WindowsPowerShell\Modules\RemSQL
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 3/14/2017 3:52 PM 99 RemSQL.format.ps1xml
-a---- 3/14/2017 3:52 PM 571 RemSQL.psd1
-a---- 3/14/2017 3:52 PM 124714 RemSQL.psm1
Here's what happened in that previous pipeline:
- We rounded up all the PowerShell commands on the remote server that began with the Sql noun prefix (in other words, all the sqlps module contents). This is one reason why PowerShell uses a consistent syntax!
- We created a special kind of PowerShell module on our local computer named RemSQL.
It's important for you to know that we haven't actually copied the remote SQL Server's PowerShell commands to our local system. Rather, our RemSQL module contains proxy commands that stand in place of the remote commands. Interesting concept, isn't it?
Test the proxy commands
In our Windows 10 client computer's PowerShell session, let's remove the remoting session with SQLSERVER1:
Remove-PSSession -Session $s
Doing so ensures that we've removed the "umbilical cord" between our client device and the SQL Server box. Because our proxy module exists in the local file system and in the proper $PSModulePath location, we can import it manually like so:
Import-Module -Name RemSQL -Prefix Rem
The prefix part isn't strictly speaking necessary. However, it's a good way to keep us mindful of the fact that these commands all execute remotely and not locally. Check it out:
PS C:\> Get-Command -Module RemSQL | Select-Object -Property CommandType, Name -First 6
CommandType Name
----------- ----
Alias Decode-RemSqlName
Alias Encode-RemSqlName
Function Add-RemSqlAvailabilityDatabase
Function Add-RemSqlAvailabilityGroupListenerStaticIp
Function Add-RemSqlFirewallRule
Function Backup-RemSqlDatabase
After the first two aliases in the previous list, notice that the other commands are proxy functions and not honest-to-goodness cmdlets like you'd see if you were local to the SQL Server computer.
When you run one of these imported proxy functions, PowerShell re-creates the remoting session with SQLSERVER1 under the hood, runs the command remotely, and then serializes/deserializes the data into your local PowerShell session.
As you can see in the following screenshot, I specify "localhost" in my Get-RemSqlDatabase statement even though I know darned well I'm querying the remote SQLSERVER1 server.
Calling a proxy function in PowerShell implicit remoting
Closing thoughts
In summary, implicit remoting provides an excellent way to use PowerShell modules locally that you don't (and may not want to) install on your local system. As I said in the beginning of this tutorial, PowerShell now runs on Linux and macOS, so you may find implicit remoting more useful than you originally realized.
I'd like to leave you with a few hand-curated resources to help fill in any conceptual gaps you may have concerning PowerShell remoting.
