Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable Powershell Remoting on SQL Server Instances


By:   |   Read Comments (4)   |   Related Tips: More > PowerShell

Attend these FREE MSSQLTips webcasts >> click to register


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.

PowerShell 2.0 get-host command example

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.

PowerShell 2.0 get-service command example

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:

PowerShell 2.0 start-service command example

Step 3 - Now we will enable the PowerShell remoting feature. Run the 'Enable-PSRemoting' cmndlet in your PowerShell window as shown below.
Enable-PSRemoting
PowerShell 2.0 enable-PSremoting command example

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
PowerShell 2.0 get-psproviders command example

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.

PowerShell 2.0 all_directories_under_wsman command example

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 *
PowerShell 2.0 set-trustedhostes command example

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:

PowerShell 2.0 enter-pssession command example
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.


Last Update:


signup button

next tip button



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

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, December 16, 2011 - 5:52:40 AM - Manvendra Back To Top

Make sure your box should not be behind any firewall.so check your firewall settings. Also WinRM service is running on port no 5985 whihc should be enabled and not behind any firewall.


Thursday, December 15, 2011 - 2:38:48 AM - Pravin Back To Top

Thanks for the update Manvendra. I believe trusted connection is already in place as I'm able to remotely login to SQL server through SSMS. Is there any specific port that PS expects to be open to establish connectivity with remote SQL servers that's in a different domain?


Tuesday, December 13, 2011 - 4:06:08 AM - Manvendra Back To Top

There should be a trusted connection to access any remote box inter domain. Check your ports which are disabled.


Monday, December 12, 2011 - 12:58:06 PM - Pravin Back To Top

How can I connect to servers that are in a differnt domain through PS, these steps didn't work for me


Learn more about SQL Server tools