Using SQL Server Management Studio (and Profiler) Tools with SQL Express 'User Instances'

  • Article

In the SQL Server 2005 Express Edition, available for free here, there is new connection string option: 'user instance=true'.  When this connection string option is specified, the SQL Express instance you are connecting to (we call this the parent instance), spins of another instance of the sqlservr.exe process, as a client process (not a server service), running in the security context of the currently logged on user.  The connection object passed back to the client is to this new user instance (we also call this the 'child instance'), running as a client process in the user's security context. 

Internally (in the SQL Engine Group), we call this feature RANU, 'Run As Normal User', it enables each user to have their own sqlservr.exe process, sandboxed to their own security context.

For troubleshootig purposes, it maybe useful to use your SQL Server Management Studio and/or SQL Server Profiler tools (obtained when purchasing a Workgroup/Standard/Enterprise edition) to connect to the 'user instance'.

To do this, in the directory:

C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\<instance name>

There will be a file called 'error.log'.  Note: This file is called 'ERRORLOG' in all other editions of SQL Server.

Load up the 'error.log' file in notepad, and look for the line:

2005-09-04 20:55:00.42 Server Server local connection provider is ready to accept connection on [ \\.\pipe\37B74C8E-45AE-4B\tsql\query ].

You can use the string: \\.\pipe\37B74C8E-45AE-4B\tsql\query, (the hex numbers after the \pipe\ will be different on your machines), in the 'Connect' dialog boxes for SQL Server Management Studio and SQL Profiler. 

I can't say we have fully tested it, but I have used these tools extensively with SQL Express 'User Instances' without problem.  I have found it useful for troubleshooting issues specific to 'user instances'.

Comments

  • Anonymous
    October 31, 2005
    To get the same info, you can also use from the master database the following query:

    select * from sys.dm_os_child_instances
  • Anonymous
    June 18, 2009
    PingBack from http://wheelbarrowstyle.info/story.php?id=939