EF Core EnsureDeleted throws "failed to login with user 'sa'"

Question

I am trying to implement unit / integration testing with EF Core by using this code from the Microsoft documentation.

But calling EnsureDeleted() doesn't work, it throws an error

SqlException: Login failed for user 'sa'

This is the example from the MS docs:

public class SharedDatabaseFixture : IDisposable
{
    private static readonly object LockObject = new object(); 
    private static bool isDatabaseInitialized; 

    public SharedDatabaseFixture()
    {
        Connection = new SqlConnection("Server =; Database=; User Id=; Password=");

        Seed();
    
        Connection.Open();
    }

    public DbConnection Connection { get; }

    public OnlineMenuContext CreateContext(DbTransaction transaction = null)
    {
        var context = new OnlineMenuContext(
            new DbContextOptionsBuilder().UseSqlServer(Connection)
                .Options
            );

        if (transaction != null)
        {
            context.Database.UseTransaction(transaction);
        }
    
        return context;
    }

    private void Seed()
    {
        lock (LockObject)
        {
            if (isDatabaseInitialized) return;

            using var context = CreateContext();

            context.Database.EnsureDeleted(); // Crashes HERE                
            context.Database.EnsureCreated();
                    
            context.Products.Add(DummyData.Product);
            context.SaveChanges();

            isDatabaseInitialized = true;
        }
    }

    public void Dispose() => Connection.Dispose();
}

I used the same connection string as in the application, it works 100%

EnsureCreated() works perfectly, if I remove EnsureDeleted() the tests are running as expected, except I should delete the database manually.

I tried to isolate the problem and created a console application:

class Program
{
    static void Main(string[] args)
    {
        var connection = new SqlConnection("Perfectly working connection string");
        
        var context = new Context(
            new DbContextOptionsBuilder().UseSqlServer(connection).Options);
        
        // context.Database.EnsureCreated();

        context.Database.EnsureDeleted(); // Crashes HERE
    }
}

class Context : DbContext
{
    public Context(DbContextOptions options): base(options) { }
    private DbSet Products;
}

class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I am using MacOs to run these examples

Maybe there is something I don't know about how EnsureDeleted works.

My question is: why does it throw this exception, and how to fix it?

UPDATE:

I verified user SA's permissions using the code here, SELECT * FROM fn_my_permissions('dbo', 'SCHEMA')

Here is the result:

user_7429499 · Answer

I found the solution

I should've added Persist Security Info=true to my connection string

It was not so perfectly working after all :D

user_15709917 · Answer

If anyone else is still having trouble, ensure you are using

using Microsoft.Data.SqlClient; and not using System.Data.SqlClient;

user_11038008 · Answer

sorry for the delay, Looks like when you sending new SqlConnection("Perfectly working connection string") causing this issue. try directly sending connection string as stated

 var context = new Context(
            new DbContextOptionsBuilder()
            .UseSqlServer(@"Server=xxx; Database=myDb; Encrypt=False;User Id=sa; Password=xxxxx").Options);
        
        var tr = context.Database.EnsureCreated();
        context.Database.Migrate();
        context.Database.EnsureDeleted(); // No Loner an issue it works
        Console.WriteLine("Hello World!");

EF Core EnsureDeleted throws "failed to login with user 'sa'"

3 Answers