Cannot open database \"|DataDirectory|\\Australia Properties\\Australia Properties.mdf\" requested by the login. The login failed.

Question

I have an existing SQL LocalDB v11.0 database created with database first and Entity Framework ADO.NET. The application, for personal and business finance, allows users to create different 'Activities' to record financial information about each. All data resides in the same database and reporting separately on each Activity is achieved programatically. This means that users cannot export or transfer their database to others without disclosing all data from all of that user's Activities. I wish to enable users, when creating a new Activity, to create a separate database, based on the same metadata scheme, which can be exported alone.

My initial App.config connectionString is:

<connectionStrings>
<add name="PMMEntities" connectionString="metadata=res://*/PMMData.csdl|res://*/PMMData.ssdl|res://*/PMMData.msl;provider=System.Data.SqlClient;provider connection string=&quot;             data source=(localdb)\v11.0; AttachDBFilename=|DataDirectory|\PMM.mdf; Integrated security=True;MultipleActiveResultSets=True; 
</connectionStrings>

To create the new database I created a new folder in the relevant DataDirectory and then used:

string database = "|DataDirectory|\\Australia Properties\\Australia Properties.mdf";
string connectionString1 = $"Data Source=(LocalDB)\\v11.0;Integrated Security=True;Database=" + database + ";Connect Timeout = 30;App=EntityFramework" ;
using (SqlConnection connection1 = new SqlConnection(connectionString1))
[
	connection1.Open();
	//Do stuff here
	connection1.Close();
}

The connection fails at the using statement and the database is not created in the new sub-directory (SQL will automatically create a new database with a .mdf filename if one does not already exist), leading me to think that the 'Australia Properties' database is not being created as it is not able to adopt the existing metadata scheme (PMMEntities/PMMData).

I have previously been able to open a connection to the existing database with:

var connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;

but this does not create a different database but simply opens the existing database.

How do I get around this?

Answer 1

When you use Database keyword in the connection string, you are supposed to specify an existing database. The name of your database looks odd. It's looks more like a path to a database. Database names rarely end in .mdf.

If you want to create an empty database with a specific schema, there are some ways to do:

1. The application runs CREATE DATABASE and then all CREATE TABLE statements, and all other DDL.
2. You have an empty template database, which you backup and the restore under the new name.
3. You deploy your schema in the model database. Then all new database will get this schema. And I mean all.
4. You can clone an existing database with DBCC CLONEDATABASE. This does not have to be an empty database, but the new database will not have any existing data.

My preferences would be any of the first two options. The last options kind of looks slick, but it is not supported for production usage. It's more a tool intended for troubleshooting.

Answer 2

Somehow you mix up database file names in the two different connection strings, isn't it?