An error starting a SQL Server 2005 User Instance when using Remote Desktop
Late last week, I was using the new 'User Instance' support in SQL Express 2005. I was connecting from home using Remote Desktop to my main office machine, and when trying to connect to SQL Express, I was becoming increasingly frustrated with the following error:
Error: 'Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.'
It turns out there is a bug in Windows XP Service Pack 2 (on my office machine) which prevents connecting to User Instances. It is documented in this knowledge base article (896613), although the article doesn't give the exact connection error message so I found it hard to find. The article also doesn't mention this is specific to 'user instances', i.e. connecting to the parent instance is fine. It is also SQL Express specific, regardless of whether the version of Express came with Visual Studio or you obtained it from somewhere else (i.e. the SQL Express download page here).
The problem exists if the session you are using was started using Remote Desktop. i.e. you rebooted you office computer from home and logged back on to it (from home) using Remote Desktop. The problem would exist at home over Remote Desktop and it would also still exist in the office if you just 'unlocked' the session which was started from home. You would need to logoff and log back on again on your office machine. Luckily there is a hotfix available for the bug (as described in the article), and it should be included in Windows XP Service Pack 3.
