Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Troubleshooting Windows Remote Desktop Connections

MSSQLTips author Sadequl Hussain By:   |   Read Comments (3)   |   Related Tips: More > Tools
Problem

Everyday throughout the course of their work, system administrators, DBAs, developers and IT support personnel remotely log into different servers in their network. The tool that most people use is Remote Desktop Connection, a nifty little application that comes with Windows XP Professional and later. Remote Desktop Connection communicates with the terminal services running on the remote server and allows the user to access another computer's applications and desktop as if logged in directly from the console . It's a fairly intuitive application, but there are few little tweaks that administrators should know. In this tip we will try to show some of these tweaks.

Solution

Scenario 1: When users do not log off and the maximum terminal services connection is reached

By default, Terminal Services allows two concurrent remote sessions. There may be situations when both these connections have already been used.  Simply, other professionals could be logged into the same server.  When you try to login, you receive an error message like the following:

"The terminal sever has exceeded the maximum number of connections"

If you receive this error message, you can try to login using the /admin option, as shown below:

Remote desktop session with admin option

There is an article on this topic on MSSQLTips.com which explains how to get around the problem. The admin option effectively logs in the user with console mode. In other words, it would be as if you are interactively logging in to the server physically from its console. Whatever you do in this mode (e.g. opening folders, moving mouse over) will be visible from the actual console of the server.

Now there may be another administrator who had already logged into the machine using this dedicated connection. When that happens, Windows will notify you that other users are already logged in as admin and if you wish, their connection can be terminated:

For Windows Server 2008 R2, the message dialog box shows the following:

"Select a user to disconnect so that you may log in". 

It also gives you the option to forcefully terminate the connection. If you do not choose this option, Windows sends a message to the other admin user and waits for their response.

For Windows Server 2003, the message looks like the following:

The user Doman\username is logged on remotely to this computer. The user has 
been idle for xx minutes. The desktop is unlocked. If you continue the user's 
Windows session will end and any unsaved data will be lost. Do you want to 
continue?

Rather than blindly kicking out an unsuspecting support member, you may want to find out who else has been working on the server and if you would really want to disconnect them. Exercise caution.

A simpler method would be to login to another server and start the Terminal Services Administrator application there. You can do so from the command prompt by entering the following command:

Starting the Terminal Services Administrator 

This will open the Terminal Services Manager (or Remote Desktop Service Manager as in Windows Server 2008 R2) window. From here, you can click on the local server name in the navigation pane and the detail pane will show you which users are concurrently logged in to the local server via remote desktop.

Since you want to use a backdoor to log in to another server, choose the "Connect to Computer" option from the Actions menu which will provide you with a dialog box. Type in the remote server name and click OK. You will now be able to see which connections are remotely logged in to the machine where you are being refused entry.

Terminal Services Manager in Windows Server 2003: Connecting to a remote computer 



Terminal Services Manager in Windows Server 2008 R2: Connecting to a remote computer 

At any time, a connection status can be Active or Disconnected. From here, you can right click on a user session and choose to send a message or disconnect that user.

Remote Desktop Service Manager in Windows Server 2008 R2 

For the latter case, there may be good reason for doing so: quite often people remotely log into a server and then just close the session without properly logging off. What they don't realize is that their session will still be running. In fact if you close a remote desktop connection without logging off, you will get a message like the following:

Disconnecting from remote desktop session
 

Now, when would you want to close a remote desktop session without logging off? Suppose you are running a long-running query on the remote server that would take a whole night to finish. If you log off, all the changes will be lost. You will need to close the remote desktop window and cluck "OK" from the message box. When you log back in again later with the same credentials, your previously running session will still be there.

 

Scenario 2: Mapping local drives to the remote machine

Mapping drives on a Remote Desk session can be useful sometimes. You may need to transfer files and folders between your local workstation and the remote computer. You can try to access the remote folder using UNC path names if it's shared:

\\remote_server\shared_folder

Or you can use the remote computer's drive letter with hidden character (this will ask you for credentials):

\\remote_server\drive$

Another method is to follow these steps:

  • Click the Options>> button in the Remote Desktop Connection window:

    Options button in the Remote Desktop Connection window

  • Then  choose the Local Resources tab and click on the "More..." button under "Local devices and resources" section:

    Configuring local drive mappings for remote desktop session

  • Then select the drives you want to map to the remote computer: 

    Mapping local drives to remote computer's desktop session

When you connect to remote desktop like this, the local workstation's drives become mapped drives in the remote computer and accessible from its "My Computer" or Windows Explorer.

Now where would you find this useful? Consider this scenario: you will have to upload a file to a vendor's server. You do not have direct access to that machine from your workstation, but you know another server in your network has access there. So you do a double hop. You remotely connect to the server in your network and copy the file there. Then from that server you remotely log into the vendor's server. In the second hop you map the first server's drives which then become available in the remote machine. You can now easily transfer the file to its destination.

 

Scenario 3: Cannot copy and paste text between local workstation and remote server

We all do it on a pretty regular basis: we copy and paste text and image data between a remote server's open window and the local workstation. Error log entries, screenshots, stored procedure text, modified query; everything that DBAs and Developers work with may involve some degree of data transfer between terminal service windows. The ease with which  we transfer this data does not become apparent until we find we are blocked from doing so in a server. When that happens, text or image copied from local workstation or another remote server cannot be pasted to the destination machine; neither can we copy anything from the remote computer and paste it locally. Depending on the size and the importance of the data being shared via clipboard, this can become quite frustrating at times. This issue is related to a local Group Policy setting in the remote computer and provided you have local administrative privilege to that machine, here is how you can change it:

In Windows Server 2003:

  • Open a command prompt in the remote server
  • Type the following command and press Enter:

    Group Policy Edit command

    This will open the Group Policy Object Editor (shown below):

    Group Policy Object Editor

  • From the navigation pane, choose Computer Configuration > Administrative Templates > Windows Components
  • Expand the folder and drill down to  Terminal Services folder. Expand it and click on the Client/Server data redirection folder.
  • From the right side detail pane, if you click on the "Do not allow clipboard redirection" item, you will see the following description beside it:

    Terminal Services Group Policy

  • Now if you double click on the item, a dialog box will appear. Choose the option "Not Configured" in the dialog box and click OK.

    Setting the clipboard sharing policy

  • You will now need to log off and log back in to the server to allow clipboard data sharing.

The procedure is almost the same for Windows Server 2008, except the following:

  • In the navigation pane of the Group Policy Object editor, under Computer Configuration > Administrative Templates > Windows Components > Terminal Services, expand the Terminal Server folder.
  • Click on the Device and Resource Redirection folder:

    Setting clipboard sharing in Windows Server 2008

  • From the right side detail pane, double-click on the "Do not allow clipboard redirection" item.

For Windows Server 2008 R2:

  • From the navigation pane, go to Computer Configuration > Administrative Templates > Windows Components > Remote Desktop Services
  • Expand the Remote Desktop Session Host folder
  • Click on Device and Resource Redirection folder
  • Double click on the "Do not allow clipboard redirection" item in the detail pane on right side.

    Setting clipboard sharing in Windows Server 2008 R2

Now the same folder where this setting is located also has another setting: "Do not allow drive redirection". This controls how the client side drive mapping (discussed above) will be configured.

One thing to be mindful of is that Group Policies are applied in a definite order and that order is: Local Computer> Site > Domain > Organizational Unit (OU). That means if your system administrators has created polices at a higher level that are different from the ones you have just changed, your configurations will be overwritten the next time polices are refreshed.

 

Scenario 4: User does not have permission for remote access

The task of granting remote access permission ultimately lies with the system administrators. Support personnel (DBA, application specialists or helpdesk staff) would often try to remotely access a server  for legitimate reasons and denied permission to log on. When that happens, the message would typically be the following:

To log on to this remote computer, you must be granted Allow log on through 
Terminal Services right. By default, members of the Remote Desktop Users 
group have this right. If you are not a member of the Remote Desktop Users group 
or another group that has this right, or if the Remote Desktop User group does 
not have this right, you must be granted this right manually.

This basically means the user needs to contact the system administrator of the server for remote access permission. Pretty often lenient and trusting system administrators would make the user a member of the local admin group simply because the user is a member of the support staff. This is where you need to be careful. You can fine tune the permission required and still allow the user remote access. Just make the user a member of the Remote Desktop Users group in the machine and that should take care of it.

 

Scenario 5: Disabling remote access and how to get in when you are locked out 

Windows allows system administrators to disable remote desktop connection for a server. Although there really does not seem to be any reason for doing so, here is how it's implemented:

For Windows server 2003:

  • Right click on My Computer and Choose Properties
  • From the Properties dialog box, choose the Remote tab
  • Un-tick the checkbox "Enable Remote Desktop on this computer" under Remote Desktop section and click OK.

    Disabling remote desktop connection in Windows Server 2003

For Windows Server 2008 R2:

  • From the Computer Properties of System window, click on the Remote settings link.
  • In the System Properties Window, click on the Remote tab.
  • Choose the option "Don't allow connection to this computer" from the Remote Desktop section

    Disabling remote desktop connection in Windows Server 2008 R2

If remote desktop is disabled in a server, users get the following error message when attempting to connect:

Remote desktop connection error message 
Obviously this will lock out everyone, including you, who implemented it. You can enable connection again by editing a key in the remote computer's registry. To enable remote desktop connection again:
  • Start the registry editor in your local workstation by entering regedit.exe in the Start menu's Run dialog box. The registry editor will load and show the registry of the local machine
  • Choose "Connect Network Registry..." option under the File menu
  • In the dialog box, type the name of the remote computer and click on the "Check Names" button. If the name is valid, click OK. The registry editor will load the remote server's registry values
  • For the remote computer's registry, Expand the node HKEY_LOCAL_MACHINE > SYSTEM > CurrentControlSet
  • Expand the Control folder under the CurrentControlSet folder
  • Click on the Terminal Server folder under the Control folder
  • On the right side pane, choose the key "fDenyTSConnections".
  • Double click the key to edit it, you will see a DWORD value of 1. Change it to 0

    Enabling remote desktop connection for a server using registry values
  • Click OK and close the registry editor. Remote Desktop Connection  should now be enabled for the server.

If you know a server to be online and if you are repeatedly getting the connection error message shown before, you may want to take this approach to check the registry value of the remote computer. This should show you if Remote Desktop access has been disabled there.

Next Steps
  • Use the tips described here in your own work environment as needed
  • Learn more about Windows Group Policies
  • Download and try out other remote access utilities like RealVNC, DameWare NT Utilities, Symantec PCAnywhere or Visionapp. Learn how they can help you manage SQL Server better.


Last Update: 2/6/2012


About the author
MSSQLTips author Sadequl Hussain
Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, February 07, 2012 - 8:36:27 AM - S.MacLaren Read The Tip

This is an excellent summary and reminds me of stuff I thought I knew all about, but now know some more.

Thank you!

 


Monday, October 07, 2013 - 5:10:00 AM - John Click Read The Tip

Regarding Remote Desktop Connection... I used it for a while, but I moved on to RHUB's setup for online collaboration — especially for remote access. Security is a big deal for me.

 


Wednesday, January 22, 2014 - 12:11:34 PM - Donaciano Read The Tip

I have trouble in Windows 2008 Server with pool printer redirections, it's hang and doesn't print. Anyone help me, or know how to fix this issue.

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.