Introduction to SQL Server Express User Instances

By:   |   Comments   |   Related: > Express Edition


Problem

You may have heard about a feature of SQL Server Express called User Instances that allows users who are not administrators on their local computers to attach and connect to a SQL Server Express database.  In this tip I will explain to you what User Instances are and how you can take advantage of them.

Solution

Suppose you are a sysadmin or a DBA working in a large organization. It is not unusual that developers ask to install a local SQL Server Express instance on their desktop machines. They do so because they find it easier to test their own code on a separate environment in order to avoid overlapping their code with other team members or they may just want to test the code on a “clean” test environment without the delays involved in getting someone to create a new environment for them.

But there is a problem with allowing people to install SQL Server Express instance on their machines, which is bigger issue at large organizations. The problem is that organizations (especially larger ones) must comply with security policies. Just to mention the most significant:

In order to comply with these policies, companies block USB and disk drive access to employees as well as the ability to install new software on their machines. The last is not just a security violation issue, but it may lead to economic penalties to the organization if an employee installed a piece of software without the respective license. All of these matters are the cause for companies to use the least-privilege user account (LUA) principle.

SQL Server Express User Instances

A user instance is like a child instance of the SQL Server Express Database Engine. In other words, it is not a new instance of SQL Server Express, but it uses its resources. User Instances permits users who are not administrators on their local computers to attach and connect to a SQL Server Express databases without the need to attach the database to the SQL Server Express Database Engine. This allows each instance to run under the security context of each individual user. Something to note is that this feature is only available when using the .NET Framework Data Provider for SQL Server.

The way User Instances work is by creating a virtual single user instance of SQL Server Express where the user has full administrative privileges without changing the security settings of the parent SQL Server Express instance. This virtual instance allows the user to access a copy of the master and msdb system databases and of course, the database specified on the connection string. But the database must be specified by the name of the mdf primary file. This feature was created with the idea to replace the Access/Jet model, where the database application simply connects to a file, and the user automatically has full permissions on all of the database objects without needing the intervention of a system administrator to grant permissions.

According to what was previously said, you can’t connect to a remote User Instance. That’s why the connection must be done with named pipes provider.

Also, if you want to use User Instances you must have the database files (.mdf, .ndf, .ldf) and not a backup file. You can’t have a User Instance without an mdf file and you can’t restore a backup on them because User Instances are not a full instance of SQL Server Express, it is a shortcut to mount a database and connect to it.

User Instances are started when a user connects to it, if not already started. It has a lifetime of 60 minutes by default after closed. An Administrator can change this value by changing the “user instance timeout” setting as follows where n is the number of minutes between 5 and 65535:

sp_configure 'user instance timeout','n'  

Another interesting behavior of User Instances is that they don’t run as a service, in a fashion that you must start or stop them manually; but also, they don’t behave like a user process. I know it is hard to understand, but think about this, the fact that they are started on a user process context, but keep open after that process ends during a timeout period is worth noting.

When to use SQL Server Express User Instances?

You should use User Instances if you don’t have administrative permissions on your host machine and your application doesn’t need to share data. It could be the case that your application does need to share data, but you want to run tests without concurrency.

For dedicated ASP.NET hosting using Windows Authentication. A single SQL Server Express instance can be hosted on an intranet. The application connects using the ASPNET Windows account, not by using impersonation.

For ClickOnce project deployment, you can get more information about it here ClickOnce Deployment for Windows Forms Applications.

When not to use SQL Server Express User Instances?

You should not use User Instances if you have administrative privileges on your system. In such case you must use a regular SQL Server Express instance or a SQL Server Developer instance installed locally.

If the database you need to connect is bigger than 10GB you cannot use User Instances or SQL Server Express. In such case you must use SQL Server Developer which is also free.

If your application uses more than one database on the same connection, like when you join data from different databases.

User instances should not be used for third-party or shared hosting scenarios where all applications would share the same User Instance and would no longer remain isolated from each other.

If your application runs on a version of SQL Server greater than 2012 you should use LocalDB because User Instances is going to be deprecated in future releases of SQL Server.

Enabling User Instances

In order to enable/disable User Instances, a member of the administrators group, the SA account or someone with system administration rights on the SQL Server Express instance can change the status of User Instances by using the sp_configure stored procedure as follows.

-- Enable user instances.  
sp_configure 'user instances enabled','1'   
RECONFIGURE WITH OVERRIDE
-- Disable user instances.  
sp_configure 'user instances enabled','0'
RECONFIGURE WITH OVERRIDE

Additionally, you must check that the SQL Server Express instance to handle the User Instances has the Named Pipes network protocol enabled. You can do this by looking at SQL Server Configuration Manager as shown below.

Screen Capture 1 - Description: Named Pipes must be enabled.

Connecting to a User Instance

In order to connect to a User Instance, you must create an ADO .NET Data Provider for SQL Server (SqlClient) object with a connection string as follows.

Data Source=.\\SQLExpress;Integrated Security=true; User Instance=true;
AttachDBFilename= |DataDirectory|\app_data\Northwind.mdf; Initial Catalog=Northwind;

Following is a table with a description of each parameter.

Parameter Description
Data Source This is the parent instance of the User Instance.
Integrated Security Must be set to True to use Windows Authentication. User Instances don’t work with SQL Server Authentication.
User Instance Of course, must be set to true if you want to use this feature.
AttachDBFilename This is the path to the mdf database file.
|DataDirectory| This text is substituted with the path of the application that is opening the connection.
Initial Catalog The name of the database.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms