mssqltips logo

Introducing PowerShell V2 Remoting for SQL Server

By:   |   Updated: 2011-09-06   |   Comments (1)   |   Related: More > PowerShell

Problem

 PowerShell can be used to for many SQL administrative tasks and in this tip we walk through how to run T-SQL code and admin tasks on remote servers.

Solution

Some of the PowerShell V1 and V2 cmdlets - those which include a "ComputerName" parameter - include access to remote systems, implemented using Remote Procedure Call (RPC) and Distributed COM (DCOM). If your run this code you can obtain a list of these cmdlets:

Get-Command | Where-Object {$_.CommandType -eq 'Cmdlet' -and` 
$_.Parameters.Keys -contains 'ComputerName' -and`
$_.Parameters.Keys -notcontains 'Session'}

Each cmdlet individually implements remoting, hence the implementation is not the same and has different requirements in each case.

PowerShell V2 comes with a new and consistent remoting technology, based on the WinRM service. PowerShell will execute your code in a separate session that runs on the remote system and returns the results to the calling system. Every cmdlet can be run remotely.

Prerequisites for PowerShell Remoting

As already mentioned, you'll need PowerShell V2 to benefit from the remoting feature. Check the PowerShell version you're currently running using the $psversiontable variable

PowerShell V2 remoting is based on the functionality offered by the WinRM service, which has to be "up and running".

By default PowerShell Remoting uses Kerberos authentication. However with additional configuration regarding "TrustedHosts" you can use it in a workgroup environment. Please refer to the guide I recommend in the "Next Steps" section for details on the additional configuration you'll need. I've run the examples form this tip in a domain environment.

You'll need no preparation on the "client" machine you use.  However, on any computer you want to make accessible from other machines, you'll need to run Enable-PSRemoting. Enable-PSRemoting runs the Set-WSManQuickConfig cmdlet, which will:

  • start the WinRM service
  • set the startup type on the WinRM service to Automatic
  • create a listener to accept requests on any IP address
  • enable a firewall exception for WS-Management communications
  • enable all registered Windows PowerShell session configurations to receive instructions from a remote computer.

By default you'll be prompted before making the changes, unless you run the cmdlet with the -Force parameter.

Enable PS Remoting

You will want to run Enable-PSRemoting from an elevated process - i.e. start Powrshell in the "run as admin" mode. Otherwise you'll receive an "access denied" error message.

Sessions

PowerShell remoting enables you to create sessions locally as well as on a remote machine. Many times you'll need only temporary access to the target. In this case you'll use Invoke-Command with the -ComputerName parameter. PowerShell will create a temporary connection that is used only to run the specified command and is then closed. If you need to establish persistent sessions, don't forget about the best practices described in the admin guide mentioned in the "Next Step" section.

WSMan configurations

If you run Get-PSDrive on your PowerShell V2 installation you'll notice in the list the WSman drive:

WSman

This drive contains the settings that the remoting currently uses. By default, it uses HTTP as transport protocol (using port 5985), accepts only Kerberos authentication and allows a maximum of 5 remote shells per user. Please refer to the admin guide if you need to change these default settings.

Example

Let's try to run a classical piece of TSQL code from Books Online against another machine in my domain. I'll use Invoke-Command and Invoke-Sqlcmd to run the piece of code against another computer in the domain.

On the target machine I've already run Enable-PSRemoting. In order to be able to use Invoke-Sqlcmd I need to do one more step. Even if I load the SQL Server snapins every time I start a PowerShell session locally, Invoke-Sqlcmd won't be recognized as a cmdlet when I try to use it against the target from a remote machine. I need to register a named session configuration on the target. This configuration will use a startup script that loads the SQL snapins.

Here is how I register the session configuration. Provide the full path of the script and enclose the path in double quotes if it contains white spaces.

Register-PSSessionConfiguration -Name SQLSession 
-StartupScript "C:\My Location\session_config.ps1"

The session_config.ps1 script will load the SQL snapins and for our example the script contains these lines of code:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100 

Here is the code I've run on the calling machine:

Invoke-Command -ComputerName DEV2008-2 -ScriptBlock{
$qry = "SELECT SERVERPROPERTY('ServerName') AS ServerName,
   SERVERPROPERTY('ProductVersion') AS ProductVersion,
   SERVERPROPERTY('ProductLevel') AS ProductLevel,
   SERVERPROPERTY('Edition') AS Edition, 
   SERVERPROPERTY('EngineEdition') AS EngineEdition;"
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession 

I'm using Invoke-Command in order to run Invoke-Sqlcmd on the target machine DEV2008-2. The ComputerName parameter specifies the machine on which I run the code. The ScriptBlock contains the commands I'm running on DEV2008-2, in this case an Invoke-Sqlcmd which runs this query:

SELECT SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition, 
SERVERPROPERTY('EngineEdition') AS EngineEdition;

Since I do not specify any server instance, the query will be executed against the default instance of the SQL Server installed on DEV2008-2. Remember that I'm working in a domain environment. I connect to the DEV2008-2 instance under my domain account. Notice that I'm using the session configuration registered above on the target machine.

Here is the result I've obtained:

PowerShell Example Output

By default, the output contains the name of the remote machine, given by the the PSComputerName property. You can use Invoke-Command with the -HideComputerName parameter to hide it as shown below.

Invoke-Command -ComputerName DEV2008-2 -ScriptBlock{
$qry = "SELECT SERVERPROPERTY('ServerName') AS ServerName,
   SERVERPROPERTY('ProductVersion') AS ProductVersion,
   SERVERPROPERTY('ProductLevel') AS ProductLevel,
   SERVERPROPERTY('Edition') AS Edition, 
   SERVERPROPERTY('EngineEdition') AS EngineEdition;"
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession -HideComputerName 
Next Steps
  • II have covered only the very basics of the remoting feature. The Administrator's Guide to Windows PowerShell Remoting fully describes the PowerShell v2 remoting.
  • In this article, Grant Fritchey will walk you through a more detailed example of using PowerShell remoting and Invoke-Sqlcmd. As far as I've seen, this is one of the very few resources about using Invoke-Sqlcmd and PowerShell remoting. Thank you, Grant.


Last Updated: 2011-09-06


get scripts

next tip button



About the author
MSSQLTips author Diana Moldovan Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, January 28, 2014 - 5:08:21 PM - Rob Back To Top

Why I try to run this command:

Invoke-Command -ComputerName ComputerName -ScriptBlock{
$qry = "SELECT SERVERPROPERTY('ServerName') AS ServerName,
   SERVERPROPERTY('ProductVersion') AS ProductVersion,
   SERVERPROPERTY('ProductLevel') AS ProductLevel,
   SERVERPROPERTY('Edition') AS Edition,
   SERVERPROPERTY('EngineEdition') AS EngineEdition;"
Invoke-Sqlcmd -Query $qry} -ConfigurationName SQLSession

from any computer to our sql server after allowing remote management on the sql server, I get:

[ComputerName] Connecting to remote server failed with the following error message : The WS-Management service cannot process the reque
st. The resource URI (http://schemas.microsoft.com/powershell/SQLSession) was not found in the WS-Management catalog. The catalog contains
 the metadata that describes resources, or logical endpoints. For more information, see the about_Remote_Troubleshooting Help topic.
    + CategoryInfo          : OpenError: (:) [], PSRemotingTransportException
    + FullyQualifiedErrorId : PSSessionStateBroken

I have not found anything searching that has helped.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools