Unable to remove deleted Session Hosts from RDS2012 Deployment

I’ve had a few issues with RDS in Server 2012, most of it because there is little to no documentation on a lot of the errors I’ve faced. Quite annoying when you jump on to Google and there is no answer available, anyway, I found some quite interesting stuff whilst trying to fix this particular issue, so read on…

I added a couple of Terminal Servers to my 2012 Broker to do some testing, but then didn’t remove them cleanly when they were no longer required, infact I just deleted the VM’s when I no longer needed them.

But then when opening Server Manager and heading to Remote Desktop Services I was told that it couldn’t load because some servers were missing, the ones I had just deleted – doh!

This was a bit of a problem as they weren’t around any more, so in a desperate act, I added a couple of fresh servers back to the domain, with the names of those that were deleted and at least then I could add these into server manager and access the Remote Desktop Services section.

This however left me with a server that I couldn’t get rid of.

When I tried to remove the RD Session Host server I received the error:

“Could not remove the configuration. Object reference not set to an instance of an object.”

So it was back to Google to find where RDS stored its configuration data in Server 2012, it must be somewhere right? I did an extensive search of the registry initially but this resulted in nothing.

Then I came across the ‘Windows Internal Database’ (mentioned here and here).

Firstly, download Microsoft SQL Server Management Studio, then run it as an administrator (If you don’t you will get a login authentication error when you try to connect)

Then connect to:

\\.\pipe\MICROSOFT##WID\tsql\query

At which point you will see a database called ‘RDCms’:

Interesting eh? I looked at every table in there and found that this was the back end configuration for my RDS setup, so that answer to my problems must be in here.

Anyway when looking at the table rds.RoleRdsh (Remote Desktop Session Host) I found:

I also cross-referenced this entry to the table rds.Server:

And it looked to be my orphaned server, so I took a full backup of the RDCms database and then ran the following SQL:

use RDCms delete from rds.RoleRdsh where ServerId = ‘1005’

This executed successfully and then when I loaded server manager my orphaned host server was now gone, yippee!

I also had an issue with a VM in a pool of desktops that I had renamed an I couldn’t delete, although when I went to take the screenshots to write this blog post the issue had disappeared and it let me delete it with no issues. If that issue occurs again, I’ll be sure to get screenshots and add it to this post.
Good luck!

Post navigation

Just curious if you were able to add a second Connection Broker after going through this. I was able to get the old servers removed from the deployment, but I’m still having issues adding my second connection broker.

the error is a follow:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint “Server_RoleRdsh_ServerId_FK1”. The conflict occurred in database “RDCms”, table “rds.RoleRdsh”, column ‘ServerId’.
The statement has been terminated.

Mohamed, it looks like a Foreign Key issue although I would like to suggest a fix without looking at it in more detail.

Frank

Mohamad,

I have the same problem, did you find a fix?

Brian Platz-Marcum

Check your rds.RoleRdwa table. I had the same problem and noticed my second non-existent server still existed in the rds.RoleRdwa table. Once I deleted the second servers record from rds.RoleRdwa, I was able to delete the record from rds.Server.

Thanks for this thread, it saved me a huge headache!

Todd

Excellent post! I knew there had to be a way to forcefully remove a server without contacting Microsoft.

I also had to run the following to remove the old server from the server table. Otherwise I was getting an error about the server missing from the pool when I tried to manage the collections.
use RDCms
delete from rds.server where Id = ‘X’ (x is the id of the server you want to remove)

No worries, glad it was useful 🙂 PS, don’t forget about the donate button lol

Todd Kieffer

I’ve once again had to come back to this thread for some cleanup in my lab and wanted to thank Dave yet again. For those that have a collection that can’t be deleted, you can manually delete the collection from rds.pool using the same syntax as abov.

Issue the following to find your pool in question:

select * from rds.pool

Find the collection you need to delete and note the Id then

use RDCms
delete from rds.pool where Id = ‘X’ where X is the id you noted above

You would think if you removed the RD Session Host role from a server through the Server Manager/Remove Features, it would remove it from the collection. I had a server that I no longer used. I removed it from all published collections, then went into server manager and removed the role. Dis-joint it from the domain, then recycled it. Although nothing was wrong with my deployments, everything still worked, and could manage everything, I was left with an orphaned server in my server collection list. It wasn’t doing any harm, just annoying. Could not remove it using Remove-RDServer. So I followed this page. Worked like a charm. I ran two commands.

use RDCms
delete from rds.RoleRdsh where ServerId = 2
delete from rds.Server where Id = 2
In short, I THOGUHT I removed the server the proper way, guess I didn’t. Or Microsoft is just shotty on how they manage things… (I suspect the latter)

Sorry for the delay, been tied up with the family, glad you sorted it! 🙂

Adam Adamou

Hi Dave, Thanks for sharing.

I have followed your blog in my RDS setup and removed an orphaned server, but now I have an odd issue. Whenever I add a new collection and try to access it via web access. when I click on the remote desktop icon thats been published, instead of the username and password prompt I get a standard mstsc window.

Has this happened to anyone I would greatly apreciate some help.

I have a backup of the database prior to deleting the Orphaned server but not sure how to restore this.

I deleted the server from the rds.RoleRdsh table and also rds.Server.

The method I used to remove the rows in the table was to right click the table and edit 200 rows then select the row and right click delete.

Afraid I’m not running that setup any more so can’t really assist you. Hope you find an answer!

Robert Magrogan

Worked for me, thanks!

I had to run two SQL Statements:
DELETE [RDCms].[rds].[Server] WHERE ID = 7;
DELETE [RDCms].[rds].[RoleRdsh] WHERE ServerID = 7;
… in order to get back into our Remote Desktop Servers GUI.

We retired one of many RDSH servers from our domain but forgot to remove it from the RDSH Session collection, and that prevented us from running the Remote DeskTop Services Manger. Further, the PS cmdlet Remove-RDServer did not work either. YUK!

This worked as described the first time I ran it (running the query just on rds.RoleRdsh). Apparently rds.Server doesn’t automatically update, however, so the second time I ran into this issue I had a mismatch between the two tables (I’m surprised the Connection Broker worked for weeks without a problem). I ended up having to delete/re-add existing servers so that the tables matched up again (and that in itself was painful).

So long story short, run both:

DELETE [RDCms].[rds].[Server] WHERE ID = #;
DELETE [RDCms].[rds].[RoleRdsh] WHERE ServerID = #;
to make sure the tables stay in sync.
(You’d think Microsoft would have a better fix in place by now, wouldn’t you….)

Thanks for the extra info Kim, just checked back and i wrote this article over 3 yeas ago lol, they should really have sorted this by now )

Alex Birk (BirkinatorTV)

I’m going to go ahead and give this a try later today, since none of the recommendations I’ve come across, as of yet, have worked. This includes Microsoft’s, and I’m going to call it this with confidence, attempt at extorting 500$ out of it’s business customers to do this themselves, without offering it’s clients a, or publishing information that could lead to a possible, resolution.

Alex Birk (BirkinatorTV)

Just a quick update, this worked splendidly! Executed in an HA setup, the only additional information i have to state is that HA configurations utilize a central database that may differ from the original DB name of “RDcms”. In addition, as others have pointed out, deleting the coorelated rds.Server ID is also necessary to kill the association.

use DBNameHere
delete from rds.Server where Id = #
delete from rds.RoleRdsh where ServerId = #

# equals the number of the associated table ID

I hope this helps! FYI, the fix was EASY, taking ~25min(15min research, 10min implementation) to execute. Save yourself the money!

This article, along with comments from others about cleaning up both tables,
[RDCms].[rds].[Server]
[RDCms].[rds].[RoleRdsh]
and the referenced social technet article really helped me get this corrected yesterday. Truly appreciate finding this blog, Dave. And it is indeed amazing Microsoft has not provided PowerShell cmdlets to sort this out, with their insistence that Powershell is the tool for all remote management, I clearly needed my W12R2 Desktop GUI and the ability to install SSMS on the connection broker to get this fixed.

Per von Zweigbergk

This was exactly what was needed. Totally not regretting not making my RD Broker server a Windows Server Core instance 🙂 Although I’m pretty sure that if you could get SQLCMD over on the core server, you’d be able to this as well. I tried connecting to the named pipe over the network, that didn’t work either, maybe another possible approach if you didn’t want to install SSMS locally would have been to tweak WIM’s config to allow that? Not sure. Either way, installing it locally totally worked.

After experiencing the exact same problem where i deleted a server from the server manager and domain, and not from the collection i was stuck. I first tried the powershell, but i quess because of the NULL entry in the rds.RoleRdsh that didn’t work. So i installed the SQL Management software and tried your entries first, but needed to use the syntax from Kim Kovacs to get it properly removed. So i will drink a beer with @ryanyanez:disqus at ignite on you then 😀

Chris Benjamin

This article was exactly what I needed to fix my problem. When following this guide, my orphaned server did not appear in rds.RoleRdsh, but it did in rds.Server. When I ran the delete query I used the script below. Id 15 was the Id number of the orphaned server by name in the rds.Server table.