Mysterious authentication error connecting from Dapper but works from EF 6 - same ConnectionString

Question

We have services and repositories loaded by IoC.

/* registration in the service class for repository */
container.Register<IContainerRepository, ContainerRepository>();

/* registration in GUI app from service class and Form class for constructor injection usage */
container.Register<IContainerService, ContainerService>();
container.Register<rfrmContainerList, rfrmContainerList>();

So we have the ContainerRepository for data access and ContainerService for business logic.

ContainerRepository uses EF6 and Dapper (basically EF6 for simple queries and insert/update/delete methods while we use Dapper for running optimized queries against complex joined data). Every repository has the EF6 context passed by IoC in the class constructor. Dapper always get the ConnectionString from the context:

var testItem = Context.Container.FirstOrDefault();

using (SqlConnection connection = new SqlConnection(Context.Database.Connection.ConnectionString))
{
    var builder = new SqlBuilder();
...
    var items = connection.Query<ContainerDto>(sql.RawSql, sql.Parameters);
}

The ContainerService injected by constructor injection:

private readonly IContainerService _containerService;

public rfrmContainerList(IContainerService containerService)
{
    _containerService = containerService ?? throw new ArgumentNullException(nameof(containerService));
}

Now everything is set up, so I expect that if Dapper using the same ConnectionString as EF6 it will use the same authentication, right?

Nope. While var testItem = Context.Container.FirstOrDefault(); successfully returns the expected result Dapper fails at connection.Query<> throwing an exception says Login failed for user 'username' (username replaced to actual login name for my test user).

So after many tests and try I finally localized the main problem, it's something in the IoC.

/* This query will return successfully */

var localContainerService = IoC.Resolve<IContainerService>();
var container = localContainerService.ListContainers(new Model.QueryParams.DesktopApp.Operation.ContainerListQueryParams() { StatusDateFrom = new DateTime(2019, 8, 1), StatusDateTo = DateTime.Now.Date });

/* This query will fail with authentication */

var container2 = _containerService.ListContainers(new Model.QueryParams.DesktopApp.Operation.ContainerListQueryParams() { StatusDateFrom = new DateTime(2019, 8, 1), StatusDateTo = DateTime.Now.Date });

So the first query with locally resolved service object runs without any problem, takes the ConnectionString from the Context and connection.Query<ContainerDto>(sql.RawSql, sql.Parameters) returns an expected result.

The second query using the constructor injected service fails at the same line says "Login failed for user 'username'".

Strange but EF6 query var testItem = Context.Container.FirstOrDefault(); works for both cases.

Can anyone experience a similar problem? I've checked all my IoC registrations and can't figure out why this strange behavior happens.

Answer 1

There are multiple scenarios where that won't work. The good news is that you shouldn't be using two seperate SqlConnections in the first place. Just use the DbContext's SqlConnection for Dapper/ADO.NET. The DbContext will clean up the SqlConnection and you don't even need an additional using block.

So just something like:

class MyDb : DbContext
{

    public SqlConnection GetConnection()
    {
        var con = (SqlConnection)Database.Connection;
        if (con.State != System.Data.ConnectionState.Open)
        {
            con.Open();
        }
        return con;
    }

    // . . .
}