Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Building A Remote Desktop Manager Connection List

Remote Desktop Connection Manager or RDCMan is a free download from Microsoft for managing multiple remote desktop connections. The functionality provided by RDCMan is above and beyond what you’ll find in the built-in Remote Desktop MMC and comparable to other 3rd party Remote Desktop utilities. If you’re connecting to multiple remote desktop sessions, RDCMan is a tool worth looking into.

Because my environment has hundreds of servers, one of the things I look for in management tools is a method to import an existing list of servers.  Fortunately the XML file called an RDCMan file used for the connection list in RDCMan is a simple structure you can build yourself. A quick web search turns up a post by Jan Egil Ring titled Dynamic Remote Desktop Connection Manager connection list. The approach Jan takes builds an RDCMan file from the computers in Active Directory using PowerShell which isn’t quite what I want, but it gives me an idea on using T-SQL/XQuery…

As a SQL Server DBA I maintain a list of SQL Servers and groups in a Central Management Server or CMS. A CMS is not only useful for maintaining a central list of SQL Servers, driving Policy Based Managment, and executing multi-server queries but can also be used as input for other things including building an RDCMan file. Because the CMS data is already in SQL Server tables it’s easier to use a T-SQL approach to shape the XML. The following T-SQL query creates an RDCMan XML file from CMS servers and groups. The same servers and groups in your CMS will be represented in the resulting RDCMan file:

DECLARE @xml XML
 
;WITH [file] AS
(SELECT
1 AS 'RDCMan'
,2.2 AS 'version'
,'CMS' AS 'name'
,'True' AS 'expanded'
,'Generated from CMS' AS 'comment'
,'FromParent' AS 'logonCredentials'
,'FromParent' AS 'connectionSettings'
,'FromParent' AS 'gatewaySettings'
,'FromParent' AS 'remoteDesktop'
,'FromParent' AS 'localResources'
,'FromParent' AS 'securitySettings'
,'FromParent' AS 'displaySettings'
)
,grp AS
(SELECT
 server_group_id
,name
,'True' AS 'expanded'
,'Generated from CMS' AS 'comment'
,'FromParent' AS 'logonCredentials'
,'FromParent' AS 'connectionSettings'
,'FromParent' AS 'gatewaySettings'
,'FromParent' AS 'remoteDesktop'
,'FromParent' AS 'localResources'
,'FromParent' AS 'securitySettings'
,'FromParent' AS 'displaySettings'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal
WHERE is_system_object = 0)
,srv AS
(SELECT
 DISTINCT server_group_id
,CASE
	WHEN PATINDEX('%\%',name) > 0 THEN
		SUBSTRING(name,1, (PATINDEX('%\%',name) -1 ))
	WHEN PATINDEX('%,%',name) > 0  THEN
		SUBSTRING(name,1, (PATINDEX('%,%',name) -1 ))
	ELSE
		name
END AS name
,CASE
	WHEN PATINDEX('%\%',name) > 0 THEN
		SUBSTRING(name,1, (PATINDEX('%\%',name) -1 ))
	WHEN PATINDEX('%,%',name) > 0  THEN
		SUBSTRING(name,1, (PATINDEX('%,%',name) -1 ))
	ELSE
		name
END AS 'displayName'
,'Generated from CMS' AS 'comment'
,'FromParent' AS 'logonCredentials'
,'FromParent' AS 'connectionSettings'
,'FromParent' AS 'gatewaySettings'
,'FromParent' AS 'remoteDesktop'
,'FromParent' AS 'localResources'
,'FromParent' AS 'securitySettings'
,'FromParent' AS 'displaySettings'
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal)
 
SELECT @XML = (
SELECT
(SELECT
 name
,expanded
,comment
,(SELECT logonCredentials AS "@inherit" FOR XML PATH('logonCredentials'), TYPE)
,(SELECT connectionSettings AS "@inherit" FOR XML PATH('connectionSettings'), TYPE)
,(SELECT gatewaySettings AS "@inherit" FOR XML PATH('gatewaySettings'), TYPE)
,(SELECT remoteDesktop AS "@inherit" FOR XML PATH('remoteDesktop'), TYPE)
,(SELECT localResources AS "@inherit" FOR XML PATH('localResources'), TYPE)
,(SELECT securitySettings AS "@inherit" FOR XML PATH('securitySettings'), TYPE)
,(SELECT displaySettings AS "@inherit" FOR XML PATH('displaySettings'), TYPE)
FROM [file]
FOR XML PATH('properties'),TYPE)
 
,(SELECT 
 
 (SELECT
 g.name
,g.expanded
,g.comment
,(SELECT g.logonCredentials AS "@inherit" FOR XML PATH('logonCredentials'), TYPE)
,(SELECT g.connectionSettings AS "@inherit" FOR XML PATH('connectionSettings'), TYPE)
,(SELECT g.gatewaySettings AS "@inherit" FOR XML PATH('gatewaySettings'), TYPE)
,(SELECT g.remoteDesktop AS "@inherit" FOR XML PATH('remoteDesktop'), TYPE)
,(SELECT g.localResources AS "@inherit" FOR XML PATH('localResources'), TYPE)
,(SELECT g.securitySettings AS "@inherit" FOR XML PATH('securitySettings'), TYPE)
,(SELECT g.displaySettings AS "@inherit" FOR XML PATH('displaySettings'), TYPE)
FROM grp g WHERE [group].server_group_id = g.server_group_id
FOR XML PATH('properties'),TYPE)
 
,(SELECT
 s.name
,s.displayName
,s.comment
,(SELECT s.logonCredentials AS "@inherit" FOR XML PATH('connectionSettings'), TYPE)
,(SELECT s.connectionSettings AS "@inherit" FOR XML PATH('connectionSettings'), TYPE)
,(SELECT s.gatewaySettings AS "@inherit" FOR XML PATH('gatewaySettings'), TYPE)
,(SELECT s.remoteDesktop AS "@inherit" FOR XML PATH('remoteDesktop'), TYPE)
,(SELECT s.localResources AS "@inherit" FOR XML PATH('localResources'), TYPE)
,(SELECT s.securitySettings AS "@inherit" FOR XML PATH('securitySettings'), TYPE)
,(SELECT s.displaySettings AS "@inherit" FOR XML PATH('displaySettings'), TYPE)
FROM srv s WHERE [group].server_group_id = s.server_group_id
FOR XML PATH('server'),TYPE)
FROM grp [group]
ORDER BY [group].name
FOR XML PATH('group'),TYPE)
 
FROM [file]
FOR XML AUTO, ELEMENTS, ROOT('RDCMan')
)
SET @XML.modify('
insert <version>2.2</version> 
as first
into (/RDCMan)[1]') 
 
SET @xml.modify('insert attribute schemaVersion{"1"} as last into (RDCMan)[1]')
 
SELECT @XML

To use run the query in the msdb database on your CMS and save the output as an .rdg file. Next, from Remote Desktop Connection Manager select File->Open and select the RDCMan file. Once you’ve imported the list of servers, you’ll want to take a look at the global settings and features of RDCMan. In particular you’ll want to set Logon Credentials which will allow you to autologon to various servers.
The code syntax highlighter I’m using seems to mess up the case of several items, so I’m including the SQL script for download:

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.