Welcome to Day 28 of my “A Month of PowerShell” series – the last day. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

During this series, we’ve learned how to do a lot of things in SQL Server using PowerShell, primarily with the Database Engine and the Job Engine. While there are other areas that were not covered, times up and we have just today left.

So far through this series, when I’ve dealt with working with multiple instances of SQL Server, I’ve been working with instances that are all on one server. In my opinion, the real power of PowerShell is being able to loop through a list of servers to work on multiple servers all at once. So, today we’ll wrap up the Month of PowerShell series by learning how to use PowerShell to connect to a remote server.

What is Windows PowerShell Remoting?

What’s the big hubbub about with Windows PowerShell Remoting? After all, we already have COM / RPC (Report Procedure Calls) tools that can work with remote computers. The main issues with these tools that PowerShell resolves are consistency, security and scalability.

• Consistency – the different tools have different ways for specifying parameters, or dealing with quoted strings. PowerShell uses one consistent method. Additionally, some tools behave differently when being run locally versus being run remotely. When you use Windows PowerShell Remoting, your session is connected to a session on the remote server, and the commands that you issue are sent to the remote server, executed locally, and then the results are returned to your system.
• Security – these various tools all have to punch holes in firewalls, deals with intrusion prevention systems, and the like. Yes, a hole still needs to be put in place in the firewall for PowerShell… but not one hole for every RDP port.
• Scalability – When you execute a tool against multiple servers, you have a loop that runs the command on each server one by one. In contrast, PowerShell allows you to execute the same command against multiple servers concurrently.

Enabling Windows PowerShell Remoting

If you are running SQL Server 2012 on Windows Server 2012, Windows PowerShell Remoting is already enabled. If you are running Windows Server 2008 or Windows Server 2008R2, then you need to enable this from an elevated PowerShell window (elevated… meaning it’s running as an Administrator) by typing the following:

Source code

Enable-PSRemoting -Force

The –Force parameter isn’t necessary, but it does simplify things… if you omit it, you will be prompted numerous times for the various actions necessary to implement Windows PowerShell Remoting. As a side-note, The Scripting Guy (Ed Wilson) at Microsoft has many blog posts about Enabling Windows PowerShell Remoting.

Connecting to a remote server that is on the same domain with the same credentials.

Once you have Remoting set up on the server, you can access it across the domain relatively simply. First, you run a command to retrieve your current credentials, which are stored in a variable (running this first command will prompt you for the password). Secondly, you create a PowerShell session to the server utilizing these credentials. Again, thanks to The Scripting Guy for showing how this is performed.

Source code

$cred = Get-Credential nwtraders\administrator
Enter-PSSession -ComputerName sql1 -Credential $cred

Connecting to a remote server that is not on the same domain

When trying to run a PowerShell script on a server not on the same domain, you will likely get the following error:

      ERROR:  The WinRM client cannot process the request. If the

      authentication scheme is different from Kerberos, or if the client

      computer is not joined to a domain, then HTTPS transport must be used

      or the destination machine must be added to the TrustedHosts

      configuration setting.

Perhaps the easiest way to correct this is to add the remote server to the local servers TrustedHosts configuration setting. (The following example will add all computers to the TrustedHosts configuration. See help About_Remote_Troubleshooting for how to add specific computers to an existing list, or allow all computers from specific domains.) This command needs to be run from an elevated PowerShell window.

Source code

Set-Item WSMan:\localhost\Client\TrustedHosts -Value * -Force

Running PowerShell commands remotely.

Now you can run PowerShell commands from the remote computer. In the following example, I have two virtual machines (SQL2005-Peer1 and SQL2005-Peer2). I will establish a remote connection to one (from my VM host), and ping the other, in the IPv4 format:

Source code

$cred = Get-Credential SQL2005-Host1\Administrator
Invoke-Command `
    -ComputerName SQL2005-Peer1 `
    -ScriptBlock {ping SQL2005-Peer2 -4} `
    -Credential $cred

Wrap-Up

Today we have seen how to establish a remote PowerShell connection between different computers, and how to run a command on the remote server. It should be simple by now to read a file of servers (and sql instances) that connects to these servers to perform additional actions.

For more help with PowerShell Remoting, try the help cmdlet for the following topics:

• Help About_Remote
• Help About_Remote_Requirements
• Help About_Remote_Troubleshooting

As referenced throughout todays post, The Scripting Guy has many blog posts dealing with PowerShell in general and PowerShell remoting specifically. One of The Scripting Guy guest bloggers, Jason Hofferle, has a five part series on an introduction to PowerShell remoting that I encourage you to read for more into PowerShell remoting.