dotnet / dotnet/efcore

SQL Server EnsureCreated/EnsureDeleted using SqlCredential to provide credentials

Open
#33,192 1 comment 1 reaction 0 assignees View on GitHub
area-dbcontext customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

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:

```c#
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 options = new DbContextOptionsBuilder().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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.