Description
I've been trying to use EnsureCreated and EnsureDeleted to completely rebuild a clean SQL Server database for testing. I want to authenticate using an SQL login by providing an SqlCredential object, like so:
public void CreateDb()
{
// Assume these variables are initialised
string serverName;
string databaseToCreate;
string databaseUsername;
SecureString databasePassword;
var connectionStringBuilder = new SqlConnectionStringBuilder
{
DataSource = serverName,
IntegratedSecurity = false,
PersistSecurityInfo = true,
MultipleActiveResultSets = true,
InitialCatalog = databaseToCreate
};
SqlCredential credential = new SqlCredential(databaseUsername, databasePassword);
SqlConnection conn = new SqlConnection(connectionStringBuilder.ToString(), credential);
DbContextOptions<MyDbContext> options = new DbContextOptionsBuilder<MyDbContext>().UseLazyLoadingProxies().UseSqlServer(conn).Options;
using (var context = new MyDbContext(options))
{
context.Database.EnsureCreated(); // Exception thrown here - same error occurs on EnsureDeleted() if the database exists.
}
}The exception that is thrown is:
Microsoft.Data.SqlClient.SqlException: Login failed for user ''.
The stack trace, from EnsureCreated() down:
SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
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, SqlAuthenticationProviderManager sqlAuthProviderManager)
SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
SqlConnection.TryOpen(TaskCompletionSource`1 retry)
SqlConnection.Open()
RelationalConnection.OpenDbConnection(Boolean errorsExpected)
RelationalConnection.Open(Boolean errorsExpected)
MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
SqlServerDatabaseCreator.Create()
RelationalDatabaseCreator.EnsureCreated()
DatabaseFacade.EnsureCreated()
I understand that the exception thrown is from Microsoft.Data.SqlClient, however I believe that the error is related to EF Core. Having trawled through through the source of the stack trace, it appears as though the connection to the server's master database that calls the CREATE DATABASE and DROP DATABASE queries doesn't use the SqlCredential object, and only takes details from the connection string, so authentication details are not passed through. Is this correct and intentional?
I am not interested in providing the login details in the connection string, though I know that that works without issue. I also cannot just lock down authentication to Windows login only.
EF Core version: 8.0.2 & 3.1.32 (have tried in both)
Database provider: Microsoft.EntityFrameworkCore.SqlServer (with Microsoft.Data.SqlClient)
Target framework: .NET 8.0 & .NET Standard 2.0 (have tried in both)
Operating system: Windows 10, Windows Server running SQL Server 2019