An attempt to attach an auto-named database for file *.mdf failed

And another catch when using SQL Server Express 2005. There are several posts some are helpful some are not. The scenario I am focussing on here is embedding SQL Server Express 2005 into your .NET application. In that case you'd ship the database files (MDF and LDF) with your application and include them in the setup script as well. The usual question when working with ADO.NET is: How does the connection string look like? In my case I tried a few, and then try to use the following very simple C# code:

1: using(SqlConnection conn = new SqlConnection(connectionString)) {
2:   try {
3:      conn.Open();
4:   }
5:   catch(Exception ex) {
6:   }
7:}

I got an exception in line 3 along the lines

"An attempt to attach an auto-named database for file ###.mdf failed ..."

(note that ### is a place holder for the actual filename). I solved it by using information from MSDN, and the working connection string looked as follows:

Data Source = .\SQLEXPRESS; Integrated Security=true; User Instance=true; AttachDBFilename=DataDirectory\###.mdf; Initial Catalog=###;

Again, ### is a replacement for the actual file name. Using this connection string I can now access the database file in a user instance of SQL Server 2005 Express. Some additional comments:

• The user instance is separate from any SQL Server 2005 Express instance that may be running on the system as well. However, unless you haven't changed any permissions or default settings, even a user without administrative rights can run a program and access a user instance.
• Do not attach the database file at the same time with SQL Server Management Studio Express as you can't attach the same database file to twice to two different SQL Server (Express) instances.
• Always make sure you close the database connection. In the above code this is achived with the using statement as regardless how that block of code is left (exception or normal program flow) the Dispose() method will be called. This will always close the connection, and you have avoided a connection leak.
• If you use a connection string in you application, use the same one for all connections to the same database. If you use different connection strings, e.g. change the order of the parameters in them, these will be considered to be different and a connection pool for each will be created. The connection strings must be the same, every single character.

For more info please also see the link attached to this post. If you can't find it, try the heading!