SQL Server Express User Instances (ADO.NET)
Microsoft SQL Server 2005 Express Edition (SQL Server Express) supports a new feature, the user instance, 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) because each user has SQL Server system administrator (sysadmin) privileges over the instance running on her 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.
For more information, see "User Instances" in SQL Server 2005 Books Online.
Note
User instances are not needed for users who are already administrators on their own computers, or for scenarios involving multiple database users.
Enabling 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.
Connecting to a User Instance
The User Instance and AttachDBFilenameConnectionString keywords allow a SqlConnection to connect to a user instance. User instances are also supported by the SqlConnectionStringBuilderUserInstance and AttachDBFilename properties.
Note the following about the sample connection string shown below:
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.
Data Source=.\\SQLExpress;Integrated Security=true;
User Instance=true;AttachDBFilename=|DataDirectory|\InstanceDB.mdf;
Initial Catalog=InstanceDB;
Note
You can also use the SqlConnectionStringBuilderUserInstance and AttachDBFilename properties to build a connection string at run time.
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.
Note
System.Data.SqlClient resolves the substitution strings into full paths against the local computer file system. Therefore, remote server, HTTP, and UNC path names are not supported. An exception is thrown when the connection is opened if the server is not located on the local computer.
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.
Note
It may be necessary to increase the ConnectionTimeout value since user instances may take longer to load than regular instances.
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);
}
}
Note
User instances are not supported in common language runtime (CLR) code that is running inside of SQL Server. An InvalidOperationException is thrown if Open is called on a SqlConnection that has User Instance=true in the connection string.
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.
Note
If Min Pool Size is used in the connection string with a value greater than zero, the connection pooler will always maintain a few opened connections, and the user instance will not automatically shut down.
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.
Note
The .mdf and .ldf files represent the database and log files, respectively. These two files are a matched set, so care must be taken during backup and restore operations. The database file contains information about the exact version of the log file, and the database will not open if it is coupled with the wrong log file.
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.
.gif "Security note") Security Note: |
|---|
User instances should only be used in scenarios where all the applications using it are fully trusted. |
User instance scenarios include:
Any single-user application where sharing data is not required.
ClickOnce deployment. If the .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 Applications.
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.