SQL Server LocalDB simply failing to create DB

Question

Here's my complete example:

var builder = new SqlConnectionStringBuilder();
builder.DataSource = "(localdb)\\v11.0";
builder.IntegratedSecurity = true;
builder.AttachDBFilename = "test.mdf";
builder.InitialCatalog = "test";
builder.ConnectTimeout = 60;

var connStr = builder.ToString();

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
}

The exception:

System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file 'test.mdf' as database 'test'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
ClientConnectionId:6ea99bb4-5703-4354-84f3-cfc5bcd8c045
Error Number:1832,State:1,Class:14

If I comment out the AttachDBFilename part, I get:

Cannot open database "test" requested by the login. The login failed.

If I comment out the InitialCatalog part, I get:

An attempt to attach an auto-named database for file test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm running as a regular user, and get the precise same errors if I specify the data path using Path.GetTempPath(), i.e. I have access rights to the data location.

There is no existing test.mdf file, and no test database. I can change the file name and database name all I like, and get precisely the same messages.

If I use the sqllocaldb tool to stop and delete the v11.0 instance, the first time I then run the above code, I get:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3274; handshake=1;

(This occurs almost immediately, i.e. it does not use up the 60 second connection timeout I set in the connection string)

I see the same if I restart the computer then run the code.

After that, I get the above errors, the same every time.

I'm not sure what else to try!

http://stackoverflow.com/questions/13275054/ef5-cannot-attach-the-file-0-as-database-1 — CodeCaster, Dec 11 '15 at 10:16
StringBuilder doesn't create a database, it just creates a connection string, then `Open` tries to connect to it. You need to have an existing test.mdf for this to work. — Nick.Mc, Dec 11 '15 at 10:41
@Nick.McDermaid - I am aware of that :) But I think you're mistaken, localdb can be used to create databases — Kieren Johnstone, Dec 11 '15 at 11:09
@CodeCaster - you must have missed the part where I said I tried stopping and deleting the instance. Can you add anything new? — Kieren Johnstone, Dec 11 '15 at 11:10
I'm definitely not an expert on LocalDB and I'm happy to be proven incorrect. Is it possible your login has its default database set to test and therefore your login will never work? Perhaps you could first troubleshoot as per this: http://www.leniel.net/2014/02/localdb-sqlserver-2012-cannot-open-database-requested-by-login-the-login-failed-error-4060.html#sthash.LxsJz9ee.dpbs — Nick.Mc, Dec 11 '15 at 14:25
@Nick.McDermaid - thanks, but localdb always uses "integrated security" and is an ad-hoc instance. I'm creating the 'test' DB on demand, and so can't configure security for it in advance.. — Kieren Johnstone, Dec 12 '15 at 14:25
In normal SQL Server install (and I assume LocalDB), logins are attached to the _server_ not the database. You first connect to the server under a login then it tries to use that logins default database. I'm curious as to what you see you if your follow the process in the above link - i.e. connect to the instance specifying a specific connection to master. We might both learn something new. — Nick.Mc, Dec 13 '15 at 01:49
@Nick.McDermaid - I can connect fine using SSMS, or using SqlConnection if I don't give a database name, and also if I specify 'master'. I believe the 'login failed' error message is what you get if a database simply doesn't exist, i.e. to avoid brute-force attacks to determine the name of a database - though that's a guess — Kieren Johnstone, Dec 13 '15 at 19:09
Is your app running in IIS or is it a console app? It might be running under a different windows user which adds to confusion when trying to understand the issue. When you connect in SSMS, can you see the Security / Logins tree? Can you identify your windows user in that list? If you double click it, does it have a default database set? Does it help your original issue if you set that to Master? — Nick.Mc, Dec 14 '15 at 02:33

score 4 · Accepted Answer · answered Dec 16 '15 at 20:46

Ok, here's the deal:

LocalDB does not in fact auto-create databases. It's just that most of the examples out there are Entity Framework ones, which in a lot of configurations will create missing databases.

EF is in fact pretty clever, and will detect AttachDBFilename values in the connection string to build a custom CREATE DATABASE command, along with correct data/log file paths in line with the specified value. It will try to use the connection string specified, and if that fails, it will adjust the connection string to connect to master DB and issue a CREATE DATABASE. In the end, that's what I did, too.

As an aside, I was in fact using EF in my main example. The code in my question is a good test case, but I was stuck for ages getting precisely the same exceptions I outlined here, even when using EF in an "auto-create-if-missing" configuration mode. It was because I had break-on-exception turned on, along Just My Code turned off, i.e. I was seeing EF internal exceptions.

SQL Server LocalDB simply failing to create DB

1 Answers1