Enable Powershell Remoting on SQL Server Instances

By: Manvendra Singh   |   Updated: 2011-12-01   |   Comments (4)   |   Related: > PowerShell

Problem

Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? In this tip, I will describe a step by step process to enable PowerShell remoting on your SQL Server instances so that you can access all of your servers from your local machine via PowerShell.

Solution

We can ease some of the administrative headaches by using PowerShell remoting. PowerShell Remoting is a great feature in PowerShell 2.0 version because it allow us to manage any SQL Server instance remotely.  There is no need to login on the server locally for checking the Event Viewer or Windows Services. Once you are connected to a remote server through PowerShell Remoting you can perform any administrative task from your local machine.

Powershell Remoting was introduced in PowerShell 2.0 version and it runs on the WinRM protocol (Windows Remote Management). As such, the WinRM windows service should be running if you want to enable PowerShell Remoting. To get started, let's check the PowerShell version and winRM services as shown below.

Steps to Enable PowerShell Remoting

Step 1 - Run the command below to determine the PowerShell version:

get-host

In the image below you can see the PowerShell version is 2.0 on my machine. PowerShell remoting was introduced in version 2.0, so we are ready to move forward. If you have PowerShell version 1.0, then install PowerShell version 2.0 on the required machine.

Step 2 - Next we will check the status of the WinRM services by running the following command:

get-service winrm

As you can see in my circumstances, the service is stopped.

If the WinRM stopped enable it by running below cmd:

start-service winrm

Now we can see the WinRM service is started as shown below:

Enable-PSRemoting

As you can see there are 5 options to choose: 'Yes', 'Yes to all', 'No', 'No to All' and 'Suspend'. Type 'A' for the 'Yes to All' option then press the enter key.

Step 4 - The next step is to check all of the PowerShell providers. Windows Powershell providers provide access to data and components.  It is basically .NET framework-based programs that make the data in a specialized data store so that you can view and manage it.  The data that a provider provides appears in a drive, and you access the data in a path like you would on a hard drive. To access the providers issue the following command type 'Get-PSProviders' and press enter.

Get-PSProviders

Step 5 - In the screen shot above you can see all available providers. Wsman is the provider used for PowerShell remoting and we can browse the associated directories. Here we need to check Trustedhosts setting for the directory of Trustedhosts.

Step 6 - In this step we will set the value of the Trustedhosts. Trustedhosts is the container with all server name information to determine which machines you can access remotely. If you use the '*' option, then it will register all servers for that domain for remoting so you can access this server from every server which is hosted in your domain. If you want to access only one machine from your local machine or only from a specific machine then you can use that parameter rather than the '*' parameter.

set-item trustedhosts *

Step 7 - Now start winrm service as we did in step 2 (i.e. 'start-service winrm') to have all of the changes take commit.

Step 8 - Once we have PowerShell Remoting setup, lauch powershell on your local machine and type 'Enter-pssession' to connect to remote server where you have run the code as shown above. 

enter-pssession SERVERNAME

Once you are connected to the remote machine, the following screen will appear:

Next Steps

• Learn more about PowerShell.  The scripting features are great to manage servers.
• If you want to use PowerShell Remoting to manage your SQL Servers then enable PowerShell Remoting on your SQL Server boxes in order to perform the administrative tasks from your local machine. You also have the ability to run a script or batch file on your remote server with PowerShell Remoting.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips