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.