SQL Server Express user instances

SQL Server Express Edition supports the user instance feature, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. Each instance runs under the security context of the individual user, on a one-instance-per-user basis.

User Instance Capabilities

User instances are useful for users who are running Windows under a least-privilege user account (LUA). Each user has SQL Server system administrator (sysadmin) privileges over the instance running on their computer without needing to run as a Windows administrator as well. Software executing on a user instance with limited permissions cannot make system-wide changes because the instance of SQL Server Express is running under the non-administrator Windows account of the user, not as a service. Each user instance is isolated from its parent instance and from any other user instances running on the same computer. Databases running on a user instance are opened in single-user mode only, and it is not possible for multiple users to connect to databases running on a user instance. Replication and distributed queries are also disabled for user instances.

Enable User Instances

To generate user instances, a parent instance of SQL Server Express must be running. User instances are enabled by default when SQL Server Express is installed, and they can be explicitly enabled or disabled by a system administrator executing the sp_configure system stored procedure on the parent instance.

-- Enable user instances.
sp_configure 'user instances enabled','1'

-- Disable user instances.
sp_configure 'user instances enabled','0'

The network protocol for user instances must be local Named Pipes. A user instance cannot be started on a remote instance of SQL Server, and SQL Server logins are not allowed.

Connect to a User Instance

The User Instance and AttachDBFilename ConnectionString keywords allow a SqlConnection to connect to a user instance. User instances are also supported by the SqlConnectionStringBuilder UserInstance and AttachDBFilename properties.

Consider the following connection string.

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

In this connection string:

• The Data Source keyword refers to the parent instance of SQL Server Express that is generating the user instance. The default instance is .\sqlexpress.
• Integrated Security is set to true. To connect to a user instance, Windows Authentication is required; SQL Server logins are not supported.
• The User Instance is set to true, which invokes a user instance. (The default is false.)
• The AttachDbFileName connection string keyword is used to attach the primary database file (.mdf), which must include the full path name. AttachDbFileName also corresponds to the "extended properties" and "initial file name" keys within a SqlConnection connection string.
• The |DataDirectory| substitution string enclosed in the pipe symbols refers to the data directory of the application opening the connection and provides a relative path indicating the location of the .mdf and .ldf database and log files. If you want to locate these files elsewhere, you must provide the full path to the files.

Using the |DataDirectory| Substitution String

AttachDbFileName was extended in ADO.NET 2.0 with the introduction of the |DataDirectory| (enclosed in pipe symbols) substitution string. DataDirectory is used in conjunction with AttachDbFileName to indicate a relative path to a data file, allowing developers to create connection strings that are based on a relative path to the data source instead of being required to specify a full path.

The physical location that DataDirectory points to depends on the type of application. In this example, the Northwind.mdf file to be attached is located in the application's \app_data folder.

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

When DataDirectory is used, the resulting file path cannot be higher in the directory structure than the directory pointed to by the substitution string. For example, if the fully expanded DataDirectory is C:\AppDirectory\app_data, then the sample connection string shown above works because it is below c:\AppDirectory. However, attempting to specify DataDirectory as |DataDirectory|\..\data will result in an error because \data is not a subdirectory of \AppDirectory.

If the connection string has an improperly formatted substitution string, an ArgumentException will be thrown.

When the SqlConnection is opened, it is redirected from the default SQL Server Express instance to a run-time initiated instance running under the caller's account.

The following code fragment opens a new SqlConnection, displays the connection string in the console window, and then closes the connection when exiting the using code block.

Private Sub OpenSqlConnection()
    ' Retrieve the connection string.
    Dim connectionString As String = GetConnectionString()

    Using connection As New SqlConnection(connectionString)
        connection.Open()
        Console.WriteLine("ConnectionString: {0}", _
           connection.ConnectionString)
    End Using
End Sub

private static void OpenSqlConnection()
{
    // Retrieve the connection string.
    string connectionString = GetConnectionString();

    using (SqlConnection connection =
        new SqlConnection(connectionString))
    {
        connection.Open();
        Console.WriteLine("ConnectionString: {0}",
             connection.ConnectionString);
    }
}

Lifetime of a User Instance Connection

Unlike versions of SQL Server that run as a service, SQL Server Express instances do not need to be manually started and stopped. Each time a user logs in and connects to a user instance, the user instance is started if it is not already running. User instance databases have the AutoClose option set so that the database is automatically shut down after a period of inactivity. The sqlservr.exe process that is started is kept running for a limited time-out period after the last connection to the instance is closed, so it does not need to be restarted if another connection is opened before the time-out has expired. The user instance automatically shuts down if no new connection opens before that time-out period has expired. A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes.

How User Instances Work

The first time a user instance is generated for each user, the master and msdb system databases are copied from the Template Data folder to a path under the user's local application data repository directory for exclusive use by the user instance. This path is typically C:\Documents and Settings\<UserName>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. When a user instance starts up, the tempdb, log, and trace files are also written to this directory. A name is generated for the instance, which is guaranteed to be unique for each user.

By default all members of the Windows Builtin\Users group are granted permissions to connect on the local instance as well as read and execute permissions on the SQL Server binaries. Once the credentials of the calling user hosting the user instance have been verified, that user becomes the sysadmin on that instance. Only shared memory is enabled for user instances, which means that only operations on the local machine are possible.

Users must be granted both read and write permissions on the .mdf and .ldf files specified in the connection string.

To avoid data corruption, a database in the user instance is opened with exclusive access. If two different user instances share the same database on the same computer, the user on the first instance must close the database before it can be opened in a second instance.

User Instance Scenarios

User instances provide developers of database applications with a SQL Server data store that does not depend on developers having administrative accounts on their development computers. User instances are based on 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. It is intended to work in situations where the user is running under a least-privilege user account (LUA) and does not have administrative privileges on the server or local machine, yet needs to create database objects and applications. User instances allow users to create instances at run time that run under the user's own security context, and not in the security context of a more privileged system service.

User instance scenarios include:

• Any single-user application where sharing data is not required.

• ClickOnce deployment. If .NET Framework 2.0 (or later) and SQL Server Express are already installed on the target computer, the installation package downloaded as a result of a ClickOnce action can be installed and used by non-administrator users. Note that an administrator must install SQL Server Express if that is part of the setup. For more information, see ClickOnce Deployment for Windows Forms.

• 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. 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.

See also

• SQL Server and ADO.NET
• Connection Strings
• Connecting to a Data Source
• ADO.NET Overview