By: Sadequl Hussain | Comments (3) | Related: > 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:
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:
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.
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.
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:
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:
- Then choose the Local Resources tab and click on the "More..." button under "Local devices and resources" section:
- Then select the drives you want to map to the remote computer:
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:
This will open the Group Policy Object Editor (shown below):
- 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:
- 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.
- 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:
- 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.
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.
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
If remote desktop is disabled in a server, users get the following error message when attempting to connect:
- 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
- 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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
