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.


Comments (8)

  1. Chris, Cambridge, UK says:

    I have just spent a frustrating hour with this problem. Thanks for posting this.

  2. Samboy LIms says:

    Of course if you can not get the patch, you can always do this (Note: You must have  WinVnc server running in your remote computer)

    1.  In the remote desktop session, reboot your computer.

       Click Start->Windows Security->Shutdown->Restart.

       Wait for some time to get machine up.

    2.  Use your vncviewer to login to your remote computer. You can choose  to do all work via VNC or go to next step.

    3.  Use remote desktop to login (this automatically closes the winvnc session).

    There you go, You did not create a remote session, but reused your local session.

  3. wendy says:

    I had to temporarily turn off User Instance to get remoting to work.  Thank you for pointing me to the hotfix.  I would have never connect this problem to the article.  This is a poorly documented article – no mention of the error message, "User Instance" nor "Remote Desktop" directly (other than mstsc.exe).

  4. Tim says:

    This is an example of why SQLEXpress is free.

  5. Dan says:

    I am grateful for your post!  I have been wrestling with this issue for some time now, and had given up working remotely from home.  Now maybe I can boost my productivity like I’d like to.

    Thanks again!

  6. Denise says:

    On remote Vnc desktop (my computer) connects SQL Server 2005 in our Server room, I cann’t do any configuaration and setup Report Services and Notification Services.  Is that any other to solve this problems?