How to Connect SQL Server in ASP.NET Core

Connecting to a database is one of the most essential steps in building any web application. Whether you are creating a login system, storing user data, managing products, or generating reports, your ASP.NET Core application must communicate with a database.

Microsoft SQL Server is one of the most widely used relational databases in enterprise applications. ASP.NET Core provides seamless integration with SQL Server using Entity Framework Core (EF Core), making database operations simple and efficient.

In this article, we will learn how to connect SQL Server to an ASP.NET Core application step by step.

Why Database Connection Is Important

Database connectivity is required for:

User authentication systems

CRUD (Create, Read, Update, Delete) operations

E-commerce applications

Admin dashboards

Reporting systems

Enterprise applications

Without a database connection, applications cannot store or retrieve persistent data.

Step 1: Install Required Packages

To connect SQL Server with ASP.NET Core, install the following NuGet packages:

Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore.Tools

These packages allow EF Core to communicate with SQL Server.

Step 2: Add Connection String in appsettings.json

The connection string contains the database server details, database name, and authentication information.

{

  "ConnectionStrings": {

    "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=YourDatabaseName;Trusted_Connection=True;TrustServerCertificate=True;"

  }

}

Explanation

Server → SQL Server instance name
Database → Name of your database
Trusted_Connection=True → Uses Windows Authentication
TrustServerCertificate=True → Avoids SSL certificate issues (for development)

In production, you should secure credentials properly.

Step 3: Create DbContext and Model (Main Database Configuration)

Now create a model class and a DbContext class.

Example Model and DbContext

using Microsoft.EntityFrameworkCore;



public class Student

{

    public int Id { get; set; }

    public string Name { get; set; }

    public int Age { get; set; }

}



public class ApplicationDbContext : DbContext

{

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

        : base(options)

    {

    }



    public DbSet<Student> Students { get; set; }

}

What Is Happening Here?

Student is a model class representing a database table.

DbSet<Student> represents the Students table in SQL Server.

ApplicationDbContext manages database operations.

Step 4: Register DbContext in Program.cs (Second Important Step)

Now configure the database connection inside Program.cs.

using Microsoft.EntityFrameworkCore;



var builder = WebApplication.CreateBuilder(args);



builder.Services.AddDbContext<ApplicationDbContext>(options =>

    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));



var app = builder.Build();



app.Run();

Explanation

AddDbContext registers the database context.
UseSqlServer() connects EF Core to SQL Server.
The connection string is read from appsettings.json.

At this point, your ASP.NET Core application is successfully connected to SQL Server.

Step 5: Create Database Using Migration (Optional but Recommended)

To create the database and tables automatically, run:

Add-Migration InitialCreate

Update-Database

Or using CLI:

dotnet ef migrations add InitialCreate

dotnet ef database update

EF Core will create the database and the Students table automatically.

How to Test the Connection

You can inject ApplicationDbContext into a controller and retrieve data:

Insert records
Fetch records
Update data
Delete data

If data operations work successfully, your connection is properly configured.

Important Security Best Practices

When connecting SQL Server in production:

Do not store passwords in plain text
Use environment variables
Use Azure Key Vault (for cloud apps)
Enable encrypted connections
Apply least privilege principle

Database security is critical in enterprise applications.

Common Interview Questions

What is DbContext?
What is DbSet?
What is a connection string?
What is Entity Framework Core?
What is migration in EF Core?
Difference between Code First and Database First?

This topic is extremely common in backend interviews.

Real-World Use Cases

SQL Server connection is used in:

Login and registration systems
E-commerce platforms
Banking applications
ERP systems
Hospital management systems
School management portals

Almost every enterprise ASP.NET Core application connects to SQL Server.

Conclusion

Connecting SQL Server in ASP.NET Core is straightforward using Entity Framework Core. The key steps include:

Adding the connection string
Creating a DbContext and model
Registering DbContext in Program.cs
Running migrations

Once configured, your application can perform full CRUD operations efficiently.

Mastering database connectivity is one of the most important skills for becoming a strong ASP.NET Core developer.