SQL Server 2005 Express Edition User Instances

Artikel
11/15/2011

Roger Wolter

November 2005

Applies to:
SQL Server 2005 Express Edition

Summary: This paper describes user instances in SQL Server 2005 Express Edition and how you can use them to simplify adding database functionality to your Visual Studio projects. (11 printed pages)

You can download the Microsoft Word version of this article.

Introduction
Attaching Database Files
AttachDBFilename
User Instances
Opening a User-Instance Connection
SSEUtil
Off By Default Configuration
Connecting to User Instances from Other Clients
User Instance Limitations
Common Issues
Distribution and Deployment
RANU
Converting a User Instance Database
Conclusion

Introduction

A design goal for the new Express Edition of SQL Server 2005 was to implement much tighter integration with Visual Studio database design features. The Visual Studio project system is very good at managing the set of files that make up the project. To fit smoothly into this toolset, SQL Server databases must be manipulated as files. Fortunately, a SQL Server database is a collection of files, so managing the file collection as part of your Visual Studio project is not complicated. Unfortunately, connecting to those files as a database from your application is not as simple and automatic as it ought to be. The User Instance feature makes that operation easier.

Attaching Database Files

Understanding user instances in Microsoft SQL Server 2005 is easier if we understand the problems they were designed to solve. This section describes attaching database files to a SQL Server database instance—one of the things that user instances make easier.

For the purposes of this paper, there are two types of SQL Server database files (there are more, but for our purposes, two is enough). These are data (.mdf) and log (.log) files.

The file that contains the database data has an .mdf file name extension—for example, AccountsReceivable.mdf is a data file. Each data file has a corresponding log file that contains the transaction log. The log file has an .ldf file name extension. So, for example, the log file for our database would be named AccountsReceivable_log.ldf.

These two files are very tightly coupled to each other. The database file contains information about the exact version of the log file. If you restore the data file from backup without restoring the same version of the log file, the database won't start. When you manipulate the database files in your project, it is important to treat these two files as a matched set. For example, if you revert to a previous version of the .mdf file, you must also revert to the same version of the .ldf file.

Before you can connect to a SQL Server database, the server must know about the database files. The server opens the files, validates the version, makes sure the log file matches the database file, and does any recovery operations necessary to get the database file synchronized with the log file. The process of letting a server that is running SQL Server know about a database file is called attaching the database. If Sally has a database file that she needs to access through SQL Server on an enterprise server, she gives the .mdf and .ldf files to her database administrator (DBA). The DBA will:

Attach the database files to the server by using a CREATE DATABASE ... FOR ATTACH command.
Create a login for Sally on the server.
Create a user for Sally's login in the database.
Grant the user the permissions that Sally requires to run her application.

This amount of effort makes sense if this is a major application, but if Sally is a developer building applications on her own machine, this amount of work may be excessive. Note that if Sally is a member of the Administrators group on the computer where the SQL Server instance is running, the last three steps aren't necessary. This is because an administrator can always log in and has administrator rights on all databases attached to the server.

AttachDBFilename

Fortunately, the SQL Server client code includes an option called AttachDBFilename, which eliminates the need to have a DBA attach the database files to a server before they can be used. When the AttachDBFilename keyword is included in a connection string, the specified file is attached to the SQL Server instance and the client is connected to the newly attached database. The argument to the AttachDBFilename option is the name of the file to attach. Here is an example.

          AttachDbFilename=|DataDirectory|\Database1.mdf;

The |DataDirectory| is a shortcut for the directory where the program opening the connection lives. To attach a file in a different directory, you must provide the full path to the file. The log file in this case is named Database1_log.ldf and is located in the same directory as the database file. If the database file is already attached to the SQL Server instance, the connection is opened to the existing database.

This is a great option because if you are an administrator, you can attach and connect to a database file by specifying the file name in the connection string in your application. Many developers run as administrators on their systems so AttachDBFilename works well for them. The problem is that Microsoft strongly recommends NOT running as an administrator because this reduces the amount of damage that can be inflicted by a virus. What we need in this case is a way to use AttachDBFilename without being a member of the Windows Administrators group. The solution is the User Instance feature.

User Instances

I have mentioned SQL Server instances several times without defining what they are. A SQL Server instance is a SQL Server executable program running on a server. Each instance has a name, a sqlservr.exe process in memory, buffer memory, its own copies of the system databases, and its own set of user databases. By default, SQL Server Express installs as an instance named "SQLEXPRESS," for example. You connect to a named instance by specifying the instance name with the server name in the connection string. That is why you normally specify ".\SQLEXPRESS" as the server name when connecting to a local SQL Server Express database. The dot means the local server and \SQLEXPRESS specifies the SQLEXPRESS named instance. The SQL Server service (sqlservr.exe) runs as a Microsoft Windows service and executes in the user context of the user specified as the service account in the Windows service manager. For SQL Server Express, this account defaults to "NT AUTHORITY\NETWORK SERVICE" although a different account can be specified during setup.

SQL Server Express expands the concept of SQL Server instances by supporting user instances. A user instance is similar to a normal instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. In other words, if Sally opens a connection to a database file specifying the user instance option in the connection string, the user instance will have Sally as the service account.

User instances are created when the User Instance option is set in the SQL Client connection string. Following is a section of a Visual Basic configuration file that shows the User Instance option.

<connectionStrings>
     <add name="TestVB1.Settings.Database1ConnectionString" 
          connectionString="Data Source=.\SQLEXPRESS;
          AttachDbFilename=|DataDirectory|\Database1.mdf;
          Integrated Security=True;
          User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>

The following are a few things to notice in the connection string.

The data source is .\SQLEXPRESS. User instances are created by the parent SQL Server Express instance, so the initial connection must specify the parent instance.
AttachDBFilename is used to specify the database to attach to the user instance.
Integrated Security is True. User instances only work with Integrated Security—the SQL Server users with user name and password don't work.
The provider name is System.Data.SqlClient. The User Instance option is only valid on SqlClient connection strings.

When a connection with this connection string opens successfully, the user application is connected to a user instance of SQL Server Express running as the user who opened the connection. The user is connected to the database in the "database1.mdf" file. If Sally opens this connection, the user instance runs with Sally as the service account. Because Sally is the service account for the instance, Sally has full administrator rights to all databases attached to the user instance even if she isn't a Windows administrator. That is why the AttachDBFilename option works even though Sally is a normal user.

Opening a User-Instance Connection

What happens when you open a connection with the User Instance option set to True? The following steps describe what happens if this is the first time the user has opened a user instance connection.

The SQLClient logic opens a connection to the parent SQL Server Express instance (.\SQLEXPRESS by default).
SQL Server Express detects that the User Instance option is set and that there is no user instance for this user.
The master and msdb system database files are copied to the user's directory. In Sally's case, the directory will be:
```
C:\Documents and Settings\Sally\Local Settings\Application 
Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
```
These files are copied from a template directory that is created when the parent instance is installed. When the user instance starts, the tempdb, log, and trace files are written to this same user directory.
The parent instance impersonates the Windows user who is opening the connection and starts a copy of sqlservr.exe running as that user. The location of the system databases is passed as a parameter. The name of the instance is generated. For example: 69651E0A-5550-46.
A Named Pipe database connection is established for the new instance. The name is based on the instance name. For example:
```
\\.\pipe\69651E0A-5550-46\tsql\query.
```
The database file specified in the AttachDBFilename parameter is attached to the new instance and named with the full path to the file:
```
[C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
```
The name of the named pipe is passed back to the SqlClient that is opening the connection.
When SqlClient receives the name of the connection, it closes the connection to the parent instance. It opens a new connection to the user instance using the returned named pipe name.

Once the user instance has been created for a particular user, the system databases and the named pipe are kept around. Therefore, after the first connection, subsequent connections just do the last two steps.

The sqlservr.exe process that is started is kept running for a while after the last connection to the instance is closed. Therefore, it doesn't need to be restarted if another connection is opened. The length of time it stays around is set by the sp_configure option "user instance timeout". By default, this is set to 60 minutes but you can use the sp_configure command to change this.

SSEUtil

One indispensable tool for working with user instances is SSEUtil. It opens a user instance and allows you to execute SQL commands against the user instance. It can also detach a user instance so that you can work with the files. There are many other features available and SSEUtil gets better and more capable with every release. You can download it from the SQL Server Express Utility Web site.

Some of the things you can do with SSEUtil are as follows:

Attach and detach databases.
Run SQL Statements and execute SQL batch files.
List and connect to child instances.
Execute the Checkpoint command and shrink a database.
Sign a database.

Off By Default Configuration

For security reasons, SQL Server 2005 has several features that are turned off by default to reduce the surface area of code available for a hacker to attack. A tool that allows you to configure these options, called the Surface Area Configuration (SAC) tool, is installed with SQL Server Express. Changing the configuration by using the SAC tool changes the settings of the parent instance but does not affect any user instances. This allows each instance to turn on only the options required for the applications in that instance.

Since the SAC tool does not configure user instances, we must fall back on our friend SSEUtil to do this configuration. The only option that is generally required in user instances is the "clr enabled" option, which is needed if your application includes CLR stored procedures, triggers, user defined types, etc. The following figure shows how to turn on the "clr enabled" option.

C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'clr enabled','1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

For the sake of completeness, there are two other options that turn on OLE Automation and xp_cmdshell. I don't recommend using either of these options. A CLR stored procedure generally does what these procedures do except in a safer, more reliable manner. If you really need to use these options, here is how to use SSEUtil to turn them on.

C:\SSEUtil>sseutil -c
Console mode. Type 'help' for more information.
1> sp_configure 'show advanced option', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

1> sp_configure 'xp_cmdshell', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

1> sp_configure 'Ole Automation Procedures', '1'
2> go

Command completed successfully.

1> reconfigure
2> go

Command completed successfully.

Connecting to User Instances from Other Clients

Creating a user instance and starting the user instance process can only be done from an SqlClient connection in a Microsoft .NET Framework application. Once the user instance is running, however, any client that can connect to a named pipe can connect to the user instance by opening a connection to the named pipe that is created for the user instance. The name of the named pipe is available from the sys.dm_os_child_instances view in the parent instance. Use the following statement.

SELECT owning_principal_name, instance_pipe_name 
FROM sys.dm_os_child_instances

Figure 1 shows the contents of a typical sys.dm_os_child_instances view.

Bb264564.sqlexpuserinst_01(en-US,SQL.90).gif

Figure 1

Once you know the name of the named pipe, you can put np: in front of it and use it in your connection string. For example, here is an extract from an SQLCMD.exe session.

C:\>sqlcmd -S np:\\.\pipe\69651E0A-5550-46\tsql\query
1> use [C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF]
2> go
Changed database context to 'C:\MYDBPROJECTS\TESTVB1\TESTVB1\DATABASE1.MDF'.
1> select * from test1
2> go
Col1   Col2       Col3
------ ---------- -----------------------
     1 Frank      2005-02-11 00:00:00.000
    12 Sam        2001-03-21 00:00:00.000

This named pipe is a local-only named pipe so there is no way to attach to a user instance from a remote client. Also, keep in mind that attaching directly to the named pipe only works if another application that used an SqlClient connection has started the user instance within the last hour.

User Instance Limitations

The unique User Instance architecture introduces some functional limitations as follows:

Only local connections are allowed.
Replication does not work with user instances.
Distributed queries do not work to remote databases.
User instances only work in the Express Edition of SQL Server 2005.

Common Issues

The User Instance architecture sometimes leads to confusion when databases don't behave the way we are accustomed to. Most of these issues are related to the database files that get attached to the user instance and how they are handled. Following are the more common issues.

The user instance cannot attach the database because the user does not have the required permissions. The user instance executes in the context of the user who opened the connection—not the normal SQL Server service account. The user who opened the user instance connection must have write permissions on the .mdf and .ldf files that are specified in the AttachDbFilename option of the connection string. One common issue occurs when working with the Visual Web Designer. The application connects to a user instance database from the Visual Studio integrated development environment (IDE) and then fails to connect when the database is opened by the Web page. When the ASP page opens the database it is generally running as ASPNET. If ASPNET does not have write permissions on the database files, the connection fails.

Another common issue is when you open a database file successfully when the database is attached to the SQL Server Express instance, but fails when you try to open it from the Visual Studio IDE. This might occur because the SQL Server Express instance is running as "NT AUTHORITY\NETWORK SERVICE," while the IDE is running as you. Therefore, the permissions may not work.
A variation of this issue is when the user that opens the user instance connection has read permissions on the database files but does not have write permissions. In this case, SQL Server attaches the database as a READ_ONLY database. If you get a message saying that the database is opened as read only, you need to change the permissions on the database file.
The other main issue with user instances occurs because SQL Server opens database files with exclusive access. This is necessary because SQL Server manages the locking of the database data in its memory. Thus, if more than one SQL Server instance has the same file open, there is the potential for data corruption. If two different user instances use the same database file, one instance must close the file before the other instance can open it. There are two common ways to close database files, as follows.
- User instance databases have the Auto Close option set so that if there are no connections to a database for 8-10 minutes, the database shuts down and the file is closed. This happens automatically, but it can take a while, especially if connection pooling is enabled for your connections.
- Detaching the database from the instance by calling sp_detach_db will close the file. This is the method Visual Studio uses to ensure that the database file is closed when the IDE switches between user instances. For example, you are using the IDE to design a data-enabled Web page. You press F5 to run the application. The IDE detaches the database so that ASP.NET can open the database files. If you leave the database attached to the IDE and try to run the ASP page from your browser, ASP.NET cannot open the database because the file is still in use by the IDE.

Distribution and Deployment

One of the attractive features of user instances is that you can distribute your database application by including the database files on the media with your application code. If the database files are copied to the same directory as the application and |DataDirectory| is used in the AttachDbFilename option of the connection string, the database will work no matter where the application is copied to as long as there is a SQL Server Express installation available. If the user doesn't already have SQL Server Express, you can either include SQL Server Express with your application or your users can download it from the Web. No additional configuration is required to support user instances.

RANU

Every software feature needs a cool acronym. The logical acronym for user instances, UI, is already taken, so the user instance is known as RANU for Run As Normal User. The official name is the User Instance feature, but you will often hear the feature referred to as RANU.

Converting a User Instance Database

You might want to change your user instance database into a normal SQL Server database. The primary reasons to do this are if you want multiple users to connect to the database or if you need to upgrade to another version of SQL Server—perhaps because you are running into performance or database size limitations. While there are several ways to do this, the easiest way is to attach the database files to a normal SQL Server instance by using the CREATE DATABASE ... FOR ATTACH command. Then remove the AttachDbFilename and User Instance options from the connection string. Remember that while you are connected to your own instance of SQL Server, you have full administrator permissions on the databases that are attached to that instance. When you change to a normal SQL Server instance, each user must have a login and database user. The database user must be granted the permissions required to run the application.

Conclusion

SQL Server 2005 Express Edition includes a new User Instance feature, which allows an application to open a database connection if the name of the file where the database data is stored is specified. This works even if the user who is opening the connection is not an administrator. This feature simplifies creating and distributing .NET-connected database applications.

For more information

Microsoft SQL Server TechCenter

Dela via