How to fix "SqlException : A network-related or instance-specific error occurred while establishing a connection to SQL Server. (error: 26) "

Question

My scenario

I have a new ASP.NET Core web application and I've connected it to my locally running SQL Server database. I can add migrations and update-database from package console manager without any issues. I can inspect the tables in Visual Studio 2022 and in SQL Server Management Studio and I also can query them in SSMS without any issues.

But when I try to query some data from the DB in my running app I keep getting this SqlException:

SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
(provider: TCP Provider, error: 26 - Error Locating Server/Instance Specified)

Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)
Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(bool errorsExpected, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(bool errorsExpected, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, bool errorsExpected)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable<TResult>+ConfiguredValueTaskAwaiter.GetResult()

I get the same errors in swagger in another project.

What I've tried already

• I have added firewall rules on inbound connections (tcp1433 and udp1434)
• I have checked services.msc and checked if SQL Server (sqlexpress) is running, it is
• I have checked services.msc and checked if SQL Server Browser (sqlexpress) is running, it is
• I have tried many connection strings.
• I have tried reinstalling SQL Server
• I have enabled TCP/IP
• I have changed IP1 tcp port to 1433
• I have changed IPAll tcp port to 1433
• I have restarted the server service and even restarted my computer several times.

The connectionstrings I've tried that worked in Package Manager Console but not in the web app running:

• options.UseSqlServer("Server=LAPTOP-LIDY\\SQLEXPRESS;Database=recruitmentdb;Trusted_Connection=True;Trust Server Certificate=True;");
• options.UseSqlServer("Server=localhost\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

The connectionstrings I've tried that did give the error in the web app and also errored in Package Manager Console:

• options.UseSqlServer("Server=host.docker.internal\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

• options.UseSqlServer("Server=172.18.0.1\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

• options.UseSqlServer("Server=172.17.0.1\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

My controller:

public class StudentsController : Controller
{
    private readonly Schoolcontext _context;

    public StudentsController(Schoolcontext context)
    {
        _context = context;
    }

    // GET: Students
    public async Task<IActionResult> Index()
    {
        //var checkvar = await _context.Students.ToListAsync();
        //return View(await _context.Students.ToListAsync());
        var test = "tested with breakpoint";
        var firstItem = await _context.Students.FirstOrDefaultAsync();
        return View(firstItem);
    }
}

My program.cs:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<Schoolcontext>(options =>
    {    
        options.UseSqlServer("Server=localhost\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");
    });

I have tried many connection strings, and tested them by running Update-Database.

I'm probably missing something really stupid. Any help would be highly appreciated!

FIX

I had to create a new firewall rule to allow a program called %ProgramFiles%\Microsoft SQL Server\MSSQL16.SQLEXPRESS01\MSSQL\Binn\sqlservr.exe In the following link I've found the info for the fix: Link to : Open access to SQL Server when using dynamic ports and I had to remove the docker container. Thereafter it worked.

Answer 1

First, You can describe the connection string in your appsettings.json:

  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=recruitmentDbNew;Integrated Security=True;encrypt=false;"
  },...

Then just use this in your Program.cs:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<Schoolcontext>(options =>
    options.UseSqlServer(connectionString));

It is not very good idea to use your DbContext in your Controller. I recommend You to make a Service to deal with it.