Recently I had worked on an issue with Run As User Instances a.k.a. RANU. The user instance, also known as a child or client instance, is an instance of SQL Server that is generated by the parent instance (the primary instance running as a service, such as SQL Express edition) on behalf of a user. The user instance runs as a user process under the security context of that user. The user instance is isolated from the parent instance and any other user instances running on the machine. The user instance feature is also referred to as “Run As Normal User” (RANU). This is not a feature of SQL Server that is common knowledge to most people in the SQL realm. Note the following mention in BOL for the topic “User Instances for Non-Administrators”:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Why did I decide to talk about a SQL Server feature in a series that I am running on my blog related to tools. Well, this post is about connecting to a RANU instance and the tools available to do this. But this is a lesser known beast, I had to set some context first.

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:

The heart_beat column value will tell you is the instance is online or not. For an online instance, the heart_beat will have a value set to alive. RANU instances allow only local Named Pipe connections. So there are three ways how you can connect to a RANU instance:

1. Using Management Studio

a. Launch a Management Studio on the machine where the SQL Express instance is running.
b. In the Connect to Server dialog box, you will have to specify the instance_pipe_name value obtained from the above output as the Server Name.

2. Using SQLCMD

Using the sqlcmd utility, you can also connect using the pipe name in the following manner:

sqlcmd -Snp:\\.\pipe\BA78E627-AD14-4F\tsql\query -E

3. Using SQL Server Express Utility

This is a utility available on the Microsoft Downloads site but is not supported by CSS. One of the tasks that can be performed by this utility is to connect to User Instances. The SSEUtil.exe –childlist command will list all the RANU instances on the machine. The output is the same as the one shown above which was retrieved using sys.dm_os_child_instances. Refer screenshot below.

The ReadMe.htm available with the installation of the utility has information on what can be done with this utility other than connecting to a user instance.

You can then connect to the instance using the following command:

SSEUtil.exe -c –child <domain>\<user name>

How do I shutdown a RANU/User Instance?

A RANU instance has a defined timeout value of 60 minutes; after 60 minutes without activity, RANU shuts itself down. The RANU timeout is configurable at the parent instances through the ‘User Instance Timeout’ setting. If you want to shutdown a RANU instance before the timeout is reached, then you can use one of the methods listed above to connect to the User Instance and issue the following command:

SHUTDOWN WITH NOWAIT

References:
Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances
Connecting to SQL Express User Instances in Management Studio

Now it’s time to shutdown this post… Adieu till the next post.