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?

You can't find your database in Management Studio.

You can't attach to your database in the location where Visual Studio creates it.

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:

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.

Copy the Pipe Name out of the query results.

On the File menu, click Connect Object Explorer to open the Connect to Server dialog.

Paste the Pipe Name from step 1 into the Server Name text box.

You have to use Windows Authentication, that's the only authentication supported by User Instances.

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!

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.

User Instances were designed to allow non-administrative users to be able to attach a database "on the fly" during applicaiton runtime. (This explains the name of the feature, which is Run As Normal User.) This is in order to support ClickOnce deployment of single user, Windows application that use locally stored data.

Two things have to be true for AttachDBFilename to work:

1. The user has to have CREATE privledges on SQL Server.

2. SQL Server has to have access to the path represented by |DataDirectory|

Under default conditions, non-Administrator users will not have CREATE privledge, so the command line you specify would fail for them. Additionally, the default service account used by SQL Express do not have permissions for any of directories in a user profile, such a My Documents, which is the default location for databases created by VS. In your case, either you have given the Network Service account permissions for your user profile directories, or you are running SQL Express under a different account that does have permissions for those directories. (Or |DataDirectory| is pointed to somewhere that is not in a user profile.)

As an interesting side note, the connection string you reference will probably fail when run on Vista with User Account Control turned on because SQL Express will no longer percieve you as an Administrator on the computer. UAC automatically removes your Admin Tokens and treats you as a normal user until you do something to elevate the privledges of the application you are running. There are post else where in this blog discussing Vista and SQL Express.

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.

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.

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?

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.

I appreciate the recommendation, but I’m interested in solving this problem more for academic reasons than anything else. I’m not running any sort of major Web site.

I recently ran into some installation problems with SQL Standard Edition on Vista which required me to reinstall SQL Express. Prior to this reinstall, I had a Web site configured to use ASP.Net authentication with SQL Server Express which was working fine. After reinstalling SQL Express, I started getting the "cannot create user instance" error message. My previous installation was the result of a VS2005 install. This process must have installed SQL Server Express differently than I did, because everything was working just fine with user instances.

That said and recommendations for not using user instances duly noted, I would like to determine what is wrong with my present configuration which would cause this problem. I’m not interested in solving the problem by avoiding the use of SQL Express or avoiding the use of user instances as the purpose of my quest is to determine what went wrong and how this works.

Here is a list of information if you think you can help with this problem:

* Event Viewer reports: “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. [CLIENT: <local machine>]” for user “Network Service”

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.

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.

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

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?

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?

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!