Connecting to SQL Express User Instances in Management Studio

Many of you are working with User Instances, sometimes without even realizing it. Briefly, a User Instances is special process of SQL Express that can be started by an application at runtime and allows SQL Express to emulate some of the characteristics of embedded databases. User Instances were added to SQL Express in order to better server developers creating ClickOnce applications in Visual Studio 2005.

As long as you work completely within Visual Studio, you'll likely never even notice that anything special is going on. Visual Studio gives you the data tools you need to handle most tasks required for creating a database that sits behind an application. In some cases, you might want to move to a richer tool set, such as SQL Server Management Studio or SQL Server Management Studio Express, which is when you're going to hit a road bump. What road bump you ask?

1. You can't find your database in Management Studio.
2. You can't attach to your database in the location where Visual Studio creates it.
3. If you manage to attach the database in Management Studio, your application can not longer user it.

There are a myriad of solutions to each of those problems individually, but the real solution to all of them is to learn how to connect Management Studio to your User Instance so that you can work with the database using the same instance of SQL Server that your Visual Studio application is using. As always, there are a couple things you have to be aware of when working with User Instances.

• User Instances are only supported in SQL Express.
• You can not start a User Instance from Management Studio. A User Instances is spawned by the parent instance of SQL Express when an application makes a special connection to the server.
• If the User Instance isn't running, you will not be able to connect to it from Management Studio.
• User Instances are only accessible through the Shared Memory connection protocol, and are only exposed through a Named Pipe.
• Each user on a computer has their own User Instances that is unique to them.

The rest of this post describes how to start a User Instance, how to determine the State and Pipe Name for a User Instances and how to connect to a running User Instances using Management Studio.

Starting a User Instance

A User Instances is automatically created and started by SQL Express when an application makes a connection to the parent instances using a special connection string. Specifically, the connection string must contain the keyword "User Instance=TRUE". Read the white paper if you're interested in the gory details, for this post all you need to know is that SQL Express knows what to do with that connection string keyword. When you add a database to your Visual Studio project, a connection string is automatically generated that contains the right information to start the User Instance when the application is run. Since it is most common that you will want to work with your database while you are working with your application, the easiest way to start your User Instances is to start the application using Start Debugging or the F5 key. If you don't want to bother opening Visual Studio and loading your application, you can download a tool called the SQL Express Utility that is capable of starting your User Instances. SSEUtil is a tool written by the Visual Studio team to help troubleshoot User Instance issues, you can read more about it in the read me file that is installed with the utility.

Determine the State and Pipe Name of a User Instance

You can get information about the User Instances on a machine that are associated with a specific instance of SQL Express by using the dynamic management view sys.dm_os_child_instances. I say "associated with a specific instance" because User Instances are unique to both the user than starts them and the instance of SQL Express where they are started. For example, if I have two instances of SQL Express on my computer named SQLEXPRESS and MIKESOTHERINSTANCE, I could actually have two separate User Instances running for me, one for each parent instance. Back to the DMV. There are a number of columns available from this View, but I'll focus on three of them and let you explore the rest yourself. The three columns of interest are owning_prinicipal_name, instance_pipe_name and heart_beat, as in:

SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances

Here is the purpose of each column:

owning_principal_name: The name of the Windows User account that owns the User Instance in the form MacineName\UserName or DomainName\UserName.

instance_pipe_name: The Named Pipe reference to the User Instance, which will be important in the next section.

heart_beat: Denotes the State of the User Instance as Alive if it's running or Dead if it is not running.

Connecting to a User Instance in Management Studio

Now to the point, go ahead and start Management Studio and connect to your parent instance of SQL Express, it's probably named SQLEXPRESS. Open a new query windows and run the query from the section above. If you've never created a User Instance before you won't see any results returned, but if you have, you'll see something like this:

Domain\mike	\\.\pipe\191E06DB-55EE-41\tsql\query	dead
Computer\OtherUser	\\.\pipe\B7FA2FCD-4C3E-47\tsql\query	dead

As you can see, I have two User Instances on my computer, neither of which is running at the moment. I can see that the first User Instances belongs to me, while the second User Instance belongs to OtherUser, which is a local user on my computer that I use for testing. The Pipe Name is interesting because that is how I'm going to connect to the User Instance once I start it. For this example, I'd just use SSEUtil by opening a Command Prompt to the directory where I've saved the tool and running SSEUtil -L. If you've read the read me file, you know this command line will start the current users User Instances and list all the databases currently attached. The important thing is that my User Instances is now started, and if I ran the DMV again, it would now look like this:

Domain\mike	\\.\pipe\191E06DB-55EE-41\tsql\query	alive
Computer\OtherUser	\\.\pipe\B7FA2FCD-4C3E-47\tsql\query	dead

Now that my User Instance is alive, I can connect to it in Management Studio just like any other SQL Server.

1. Copy the Pipe Name out of the query results.
2. On the File menu, click Connect Object Explorer to open the Connect to Server dialog.
3. Paste the Pipe Name from step 1 into the Server Name text box.
4. You have to use Windows Authentication, that's the only authentication supported by User Instances.
5. Click Connect.

You will see a new Server show up in your Object Explorer that represents the User Instance. You can work with this User Instance just like any other SQL Server. Since this is the same User Instance that is being used by your Visual Studio applications, you won't have any conflicts between having your embedded database open in the application and opened in Management Studio.

Rather than running the DMV every time you want to connect to your User Instance, it's a real time saver to add your User Instance to the Registered Servers list once you discover it's Pipe Name. When SQL Express creates the User Instance the first time a random Pipe Name will be created, but once it's created, that same Pipe Name will be used every time the same user starts the User Instance. Once you've added your User Instance to the Registered Servers list, you can connect to it by just double clicking it, as long as it is already running. Have fun!

Mike

Comments

• Anonymous
  November 28, 2006
  When I use the following connection string without "User Instance=TRUE", I noticed my database is attached to the SQLEXPRESS instance itself and is operable. Why I need to bother adding "User Instance=TRUE" and following your steps. connectionString="Data Source=.SQLEXPRESS;AttachDBFilename=|DataDirectory|Northwind.mdf;Integrated Security=True; Second question, it seems that once the user instance goes alive, it will not go back to dead even though I restart the SQL service. Can you explain this? Thanks, Ross

• Anonymous
  November 28, 2006
  The comment has been removed

• Anonymous
  November 28, 2006
  Opps, forgot the second question... You're right. A User Instances is a completely separate process from the parent instance of SQL Express. Once it has been started, it is completely autonomous from the parent, shutting down the parent will have no impact on the User Instance. A User Instance will automatically time out after 60 minutes, and it will get shut down and not come back if you shut down your computer. You can see the User Instance process in the Windows Task Manager, do something that starts a User Instance and then take a look at the Processes tab. You should see two occurances of sqlservr.exe, one will be running as the SQL Express service account (Network Service by default) and the second will be running under your own user account. It is interesting to note that a User Instances runs as a Process, not as a Service as the parent instance does.

• Mike

• Anonymous
  April 26, 2007
  how to connect to SQL Express User Instances

• Anonymous
  May 15, 2007
  hi I am writing applications in VB6 and using SQL Express as a backend. My client network is working on some Windows98 machines and some on Win XP. I can easily connect to the database (SQL Express)  from winXP client machines using SQL Native Client,SQLNCLI I need to know.. whether i can connect to the Database from Windows98 OS.? If it is possible, how to establish a connection string to work on both win98 and Win XP machines.

• Anonymous
  June 10, 2007
  The comment has been removed

• Anonymous
  July 05, 2007
  Response for Aslan: Hi Aslan, User Instances do not support remote connections at all. You cannot connect to them in the manner you are trying. Mike

• Anonymous
  February 25, 2008
  My problem is that when I log into my development PC as workstation only/local machine, everything works.  However, when I log in to the domain account I get a message stating that User does not have permission to run the action. (or something like that)  I've tried everything I can think of.

• Anonymous
  March 16, 2008
  I know this problem has been reported all over the place, but none of the threads have helped so far. I'm running SQL Express on Vista.  I have a database file set up for ASP.Net Authentication.  I can connect to this database through Visual Studio, IIS 7.0 User Administration, and SSEUtil.  However, I cannot connect to the database when accessing it from a Web request.  Upon attempting to access the database, the web site returns the 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." I'm running SQLExpress as "Network Service" as intended.  Any ideas what could be wrong? Derek

• Anonymous
  March 17, 2008
  Response for Derek: You should check out the blog post at http://blogs.msdn.com/sqlexpress/archive/2008/02/22/sql-server-express-and-hosting.aspx. In short, User Instances provide some great developer productivity features, but are not an appropriate technology for Hosted databases for exactly the types of issues you are experiencing. In order to correctly access the database correctly through the web site, you should move the database to be connected to the parent instances and change your connection string. There are links in the blog post that show how to use the Database Publishing Wizard to do this, even to the point of combining your Membership database with your data database. You can use SQL Express as the server to host your application databases, but be aware that the physical limitations on SQL Express may impact your performance as the load on your web site increases. Hope this helps, Mike

• Anonymous
  March 17, 2008
  The comment has been removed

• Anonymous
  March 18, 2008
  Response to Derek - The short story is that I don't know what is wrong with your system. We've not been able to track down the root cause of this error. I do know that it has nothing to do with the way you installed SQL Express or the source of your SQL Express installation. This particular error is returned when SQL Express sends a request to Windows to have a new process started and Windows returns a NULL rather than starting the process. We haven't been able to determine why Windows can't start the process. There are a couple conditions that may be impacting your specific case:

• User Instances only work for interactive users because system files need to be copied into folders in the user profile. I notice you are running your Application Pool as Network Service and I don't believe there is a profile for Network Service, so there is nowhere to copy the system files. You could try changing your Application Pool to run as an interactive user for which a set of profile folders exists.
• If there is a profile for Network Services, you can try deleting the user instance folder so that it is recreated the next time you start the user instance. This frequently works for normal user accounts. The user instance folder is located at C:Users<username>AppDataLocalMicrosoftMicrosoft SQL Server DataSQLEXPRESS Again, user instances are not appropriate for hosted applications. I know you're on a mission to figure out why something is failing, but does it make sense to spend your time on this given that there is a better solution available to you? Just a thought. Mike

• Anonymous
  March 18, 2008
  To answer your question about whether it makes sense to spend time trying to solve this problem, I would say the answer to this question depends on one's perspective.  It's a bit of a "Why climb the mountain" issue in my case.  I can't say its always been easy to have the type of personality that drives me to figure out stuff like this, but overall its certainly benefited me professionally. Concerning your statement about user instances only working with interactive user accounts, if this is true then I'm really baffled.  My Web site was working with user instances just a few days ago and I know for certain I haven't changed the account my Application Pool runs under or the Application Pool associated with my site. Concerning there being a profile for Network Services, I know that there isn't currently a C:UsersNetwork Service folder so I assume it wasn't there in the past either. If only I had a time machine ...

• Derek

• Anonymous
  August 26, 2008
  You've been kicked (a good thing) - Trackback from DotNetKicks.com

• Anonymous
  October 02, 2008
  A few of you have run into problems when trying to add a new Service-based database to your project in

• Anonymous
  November 24, 2009
  Hi I found this very much helping and interesting. I am having a similar problem related to this thread. I have created a small business application by visual c sharp 2005. After reading this thread i happened to know that i have created an user instance. I installed sql management studio 2005 express manually and even thought my application is not running the heat beat still shows it s alive.(But i created an exe file to deploy the application in client's machine) So far everything is fine.But i need to do some backup taking at the client machine. So when i tried to connect to the database which i have deployed into my application, i could not find that database from the management studio. Is there any possible solution you have with this issue Mike? It would be really helpful if you can suggest me something on this matter Thank you.

• Anonymous
  December 26, 2010
  i have built an application through visual studio 2008, and when the database connection is needed i built a new database using the new connction wizard. the database type was "sql server database file". i created the database and handled the tables and data inside the visual studio. how can i generate the connectionstring and how to handle the application in the client machine?

• Anonymous
  November 13, 2011
  Very nice. Thanks

• Anonymous
  April 29, 2012
  The comment has been removed

• Anonymous
  April 29, 2012
  Chris, please describe the problem you are having in greater detail. I am just speculating here, but Visual Studio may be detecting that the database is in use (by SSMS).

• Anonymous
  January 10, 2013
  ! saved me a lot of time moving from mysql .... Thanks for your valued time

• Anonymous
  January 10, 2014
  So my application does start a user instance but it says Login failed. When i try to connect to the USer instance like you mentioned  through SQL Management studio using Windows Authentication it says Login failed. Heart beat does say alive and owning_principal_name is accurate. i dont understand what can go wrong that i am not able to connect to my own user instance.   and i dont know what else can go wrong that i am not able to connected to it. any suggestion?

• Anonymous
  April 21, 2014
  Hey, not sure if you are still monitoring this post, but I ran into the "bump" you described.  AKA - Not seeing db in SSMS, attaching file, application no longer being able to hit DB.  You mention that there are a myriad of solutions, but don't go into much detail.  Any suggestions on where to look to find a solution to this problem?  Thanks!