Cannot connect to LocalDB using standard connection string

[SetTrend]

Describe the bug

I'm not able to connect to LocalDB using Microsoft.Data.SqlClient.

The following is my simple test connection setup. It's a simple connection string that every Microsoft sample is quoting:

using SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Temp\Test.mdf;Integrated Security=True");

con.Open();

So, it should work off the cuff. But it doesn't.

Using SSMS, I can easily connect to the LocalDB instance and create/delete databases promptly, even with the provided path.

Exception

Microsoft.Data.SqlClient.SqlException:
"An attempt to attach an auto-named database for file C:\\Temp\\Test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

Stack Trace

   bei Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in /_/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlInternalConnection.cs: Zeile776
   bei Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs: Zeile1421
   bei Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs: Zeile2637
   bei Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs: Zeile1934
   bei Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs: Zeile1185
   bei Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs: Zeile1925
   bei Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs: Zeile1530
   bei Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs: Zeile1419
   bei Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs: Zeile516
   bei Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionFactory.cs: Zeile136
   bei Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Common/src/Microsoft/Data/ProviderBase/DbConnectionFactory.cs: Zeile123
   bei Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/ProviderBase/DbConnectionPool.cs: Zeile731
   bei Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/ProviderBase/DbConnectionPool.cs: Zeile1761
   bei Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/ProviderBase/DbConnectionPool.cs: Zeile1216
   bei Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/ProviderBase/DbConnectionPool.cs: Zeile1132
   bei Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/ProviderBase/DbConnectionFactory.cs: Zeile122
   bei Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Common/src/Microsoft/Data/ProviderBase/DbConnectionInternal.cs: Zeile341
   bei Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Common/src/Microsoft/Data/ProviderBase/DbConnectionClosed.cs: Zeile39
   bei Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs: Zeile1844
   bei Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs: Zeile1333
   bei Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected) in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerConnection.cs: Zeile45
   bei Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected) in /_/src/EFCore.Relational/Storage/RelationalConnection.cs: Zeile720
   bei Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected) in /_/src/EFCore.Relational/Storage/RelationalConnection.cs: Zeile634
   bei Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp) in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerDatabaseCreator.cs: Zeile191
   bei Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext _, TState s) in /_/src/EFCore/Storage/ExecutionStrategyExtensions.cs: Zeile392
   bei Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerExecutionStrategy.cs: Zeile57
   bei Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded) in /_/src/EFCore/Storage/ExecutionStrategyExtensions.cs: Zeile390
   bei Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists) in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerDatabaseCreator.cs: Zeile182
   bei Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists() in /_/src/EFCore.SqlServer/Storage/Internal/SqlServerDatabaseCreator.cs: Zeile179
   bei Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureDeleted() in /_/src/EFCore.Relational/Storage/RelationalDatabaseCreator.cs: Zeile190
   bei Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted() in /_/src/EFCore/Infrastructure/DatabaseFacade.cs: Zeile166
   bei EFC_LocalDB_MRE.Program.Main(String[] _) in D:\Documents\Repos\~Test\EFC-LocalDB-MRE\Program.cs: Zeile7

To reproduce

See this MRE: https://github.com/SetTrend/EFC-LocalDB-MRE

Expected behavior

The code should flawlessly connect to LocalDB.

Further technical details

Microsoft.Data.SqlClient version: 5.1.5
.NET target: .NET 8.0
SQL Server version: v16 (2022)
Operating system: Windows 10x64 22H2

[David-Engel]

The connection string in your repro is

"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Temp\Test.mdf;Integrated Security=True"

As the error says, you are missing the file C:\Temp\Test.mdf.

[SetTrend]

Thank you for enlightening me, @David-Engel! 👍

In the documentation on page https://learn.microsoft.com/sql/database-engine/configure-windows/sql-server-express-localdb, it reads in many places: "is automatically created and started", "LocalDB is an instance of SQL Server Express that can create and open SQL Server databases.", "They're created and managed automatically for the user and can be used by any application.". — This lead me to believe that the mdf/ldf files will automatically be created if not existing; at the default path if no path would be provided, or at a designated path if AttachDbFilename was provided.

So, this isn't the case then?

If I deployed my application and wanted to have it automatically create a blank database file if not already existing at a user's dedicated application data path (similar to how the SQLite provider behaves), how would I do that then? What is the recommended pattern for this?

Your answer is very much appreciated!

[David-Engel]

You might be right. If System.Data.SqlClient creates the file automatically, then this is something we will need to fix in Microsoft.Data.SqlClient.

[SetTrend]

This would be a greatly appreciated feature for automated testing in CI/CD environments.

With such "auto database creation" feature, tests could store their particular test case application database locally in dedicated test folders (e.g., TestResults\Deploy 20240928T054421_9452\Out), e.g., using Entity Framework Core's …

ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();

If a test failed, programmers could then download the particular .mdf/.ldf files to their development machine and try to reproduce the issue by attaching to the corresponding database files.

---

PS: If you agree, please feel free to change this issue's title and type anytime to anything appropriate for your internal workflow.

[edwardneal]

This specific behaviour is consistent between System.Data.SqlClient and Microsoft.Data.SqlClient (both for .NET Core and Framework.) None of these libraries will automatically create the database if it doesn't exist, although I can see why that behaviour would be useful for LocalDB.

AttachDBFilename is passed to the server as part of the login packet, and the error message we see also comes from the server, so this logic may need to be handled within LocalDB (to preserve the existing semantics where the database is unmounted when the user disconnects.)

In the mean time, the normal approach of connecting to the LocalDB instance and running the CREATE DATABASE DDL will let you create a database in a given location.

Incidentally, M.D.SqlClient on .NET Core behaves differently when connecting to custom LocalDB instances. If the custom LocalDB instance doesn't exist, M.D.SqlClient will create it on .NET Framework; on .NET Core, the connection will simply fail. For .NET Framework to create it, your application will need an app.config which is similar to this link. The system.data.localdb section will need to be of a different type though - something similar to this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section
    name="system.data.localdb"
    type="Microsoft.Data.LocalDBConfigurationSection,Microsoft.Data.SqlClient,Version=5.0.0.0,Culture=neutral"/>
  </configSections>

  <system.data.localdb>
    <localdbinstances>
      <add name="ExampleLocalDB" version="13.0" />
    </localdbinstances>
  </system.data.localdb>
</configuration>

[SetTrend]

Perhaps an additional connection string parameter may be feasible and backward compatible.

The suggested connection string parameter could auto-create the database with creation parameters if not exists, or just attach to the existing database file otherwise, e.g.,:

RunDbWith=PRIMARY <filespec> COLLATE collation_name WITH …

I.e., if <filespec> existed, the database file would just be attached, whereas otherwise the database file would be created with the provided parameters and then attached.

AttachDbFileName and the proposed RunDbWith would be mutually exclusive connection string parameters. So no changes to existing applications would be required.