Home / server / Questions / 319912
Accepted
William Hilsum
Asked: 2011-10-10 06:04:26 +0800 CST

I am very confused with SQL Server Express Instances / what am I actually connecting to?

  • 772

I am running SQL Express 2008 on my development machine with one instance.

For some development, I used the AttachDB Connection string. I really can't remember the exact reason, but these are not mounted in the normal way. I think I wanted to live edit some data and after ages, I found out how to connect from management studio... I used \\.\pipe\A2108B22-19AA-41\tsql\query

However, I am trying to learn MS SQL management a bit deeper and was wondering, how is this technically working and how is it separated from the main instance (connectable via .\SQLExpress)?

Also, I remember reading somewhere, but I may be wrong, that this is this a feature of SQL Express and not the main SQL Server product. Is this correct or do I have it wrong?

The reason for the last part of the question is that I was wondering if this is sort of a "back door" in SQL hosting, or if it is possible to disable it. I am hosting a few websites for people and I issued a user/pass and a DB with a size limit to people. Before I spend hours testing/mucking around, I just wondered if anyone had any insight in to this?

sql-server hosting

1 Answers

  1. Best Answer

    If you're using a connection string that looks like:

    AttachDBFilename=|DataDirectory|MyDB.mdf;User Instance=true`

    then you're using a feature of SQL Express known as "User Instancing".

    This feature was introduced in SQL Server Express 2005 and provided the ability for developers to run an instance of SQL Server under their own (non-administrator) login.

    There is still a .\SQLExpress named instance running i.e. the one created at install time. However when you connect to a database using the AttachDBFilename user instance feature, a new SQL Express instance is spun up to handle access to the AttachDBFilename database, this "user instance" runs under your windows logon account.

    Communication with this instance happens over a named pipe (as you found out).

    There's an informative article about User Instancing by Microsoft here:

    User Instances for Non-Administrators (MSDN)

    Whilst this feature is still available on SQL Server 2008 R2 Express, it looks like it's being deprecated or possibly removed in the near future and you are advised not to use it:

    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.

    User instancing is also incompatible with sites running under the ApplicationPoolIdentity account on IIS7 and IIS7.5. For more information see this MS Knowledgebase article:

    Problems with SQL Server Express user instancing and ASP.net Web Application Projects

    Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity. These products were developed and tested against application pools running with the older NETWORK SERVICE account.

    If you want to just host your database in the normal .\SQLExpress instance and have access to it via TCP/IP like a regular SQL Server then you need to do a few things:

    1. Enable TCP/IP for SQL Express. The default install only enables named pipes. You can do this using the SQL Server Configuration Manager:

      enter image description here

    2. The default install of SQL Express uses the NETWORK SERVICE account as the service account. You may need to grant modify permissions for this account on the folder containing your .mdf file.

    3. Attach the .mdf file in your App_Data folder to the .\SQLExpress instance.

    4. Change your connection string to a regular connection string e.g. 

      <add name="SupportSystem" 
     connectionString="Database=MyDatabaseName;Data Source=172.16.3.44\SQLEXPRESS;User ID=dbuser;Password=dbpassword;Initial Catalog=MyDatabaseName;" 
     providerName="System.Data.SqlClient"/>
    • 2