2

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?

William Hilsum
  • 3,536
  • 6
  • 29
  • 39

1 Answers1

2

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"/>
Kev
  • 7,877
  • 18
  • 81
  • 108
  • I had no idea that it was a complete different "virtual" (I know that isn't the technical term) instance I was connected to... I knew something funky was going on, but, thank you - I had no idea about it on a deeper level... I was wondering if you know of anyway to disable this feature? (As every DB on my server is under 2GB (I allow 5) I want to ideally switch from SQL Standard to SQL Express as the licensing fees are getting very expensive! IIS currently uses different accounts for every user, so, as this is different from `ApplicationPoolIdentity`, would my users be able to do attachdb? – William Hilsum Oct 10 '11 at 01:22
  • Ok... I think I just read up how to disable it, as I had no idea what this feature was called, I had no idea what to even look for... searching for "Disable SQL user instance" seems to have found a switch I need to do... very happy! Now I just have to find out/confirm if I can use Express in a commercial environment! – William Hilsum Oct 10 '11 at 01:26
  • @WilliamHilsum - out of the box SQL Express is locked down to only allow connections over named pipes. As I noted above you can enable TCP/IP and connect to the server using regular connection strings. You would however need to use a *normal* connection string and ensure that the named instance `SQLExpress` is specified in the data source, eg: `Data Source=[IP or FQ Hostname]\SQLEXPRESS`. You don't need to use that switch to prevent your application using User Instancing, just change the [connection string](http://www.connectionstrings.com/sql-server-2008#p1). – Kev Oct 10 '11 at 08:01
  • It is possible to use SQL Express in a commercial environment, we have some customers who use it on their dedicated servers for databases < 10GB and where they don't need > 1GB RAM or more than 1 CPU. You can compare features here: http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx – Kev Oct 10 '11 at 08:02
  • @WilliamHilsum - I've updated the last part of my answer. – Kev Oct 10 '11 at 08:08
  • Sorry, I don't think my comments were very clear... I understand how to use SQL in a normal way from web pages now, I just wanted to disable the ability for other people to change their connection strings... but, it is fine, I Googled it and have found out how to disable it. restrictions - Memory utilized is a bit of a bugger... my current SQL setup uses 2GB, so, going to profile differences as the cost savings will be worth it. Thanks very much. – William Hilsum Oct 10 '11 at 11:08