Enable Powershell Remoting on SQL Server Instances

By:   |   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.

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips



Comments For This Article




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

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 (15368)

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 (15343)

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 (15339)

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















get free sql tips
agree to terms