Coeamyd's

If you've ever built an ASP.NET application using a User Instance database, you may have come across the problem, that you would have liked to access the database using the Management Studio or similar programs, but just couldn't find the database, because it is in the user instance and not in the database server itself.

I'd like to issue my thanks to Mike of the Sql Server Express Weblog for publishing how it's done. I looked for hours trying to get the database attached to the regular server, but all that is not neccessary. You can directly connect to the user instance by using a named pipe. The original explanation can be found here: http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx. Below are the required steps in short.

• Connect to the server normally.
• Issue a new query:
  SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances
• Locate the user instance you wish to connect to and copy the instance_pipe_name column's value.
• Open a new server connection and use the copied value as the server name.

And presto, you can access everything as you would in the regular server.

Note: The pipe name is generated, when the user instance is first created. After that, it will always remain the same, so you can store it for later use. Access is only possible, when the user instance is active (see the heart_beat column). If it is not, you must first launch the application that uses it (i.e. the web site).