How to Connect to SQL Server from C# (.NET)

SQL Server is a Microsoft product and C# is a Microsoft language, so the connection story here is the most first-party of any database. There is one driver worth using, it is maintained by the same team that builds the server, and it is the foundation Dapper and EF Core sit on. This guide covers the driver, the connection string, the one default that breaks almost every fresh local setup, parameters, pooling, the higher-level options, and the errors you will meet.

The one driver you need

Microsoft.Data.SqlClient (7.0.2 as of June 2026) is the current ADO.NET provider for SQL Server and Azure SQL. It replaced the older System.Data.SqlClient, which still exists in the box but no longer receives feature work. Any tutorial that has you using System.Data.SqlClient; is out of date; the API is nearly identical, so the only change for most code is the namespace and the NuGet reference.

dotnet add package Microsoft.Data.SqlClient

using Microsoft.Data.SqlClient;

There is no second serious contender. The community has consolidated on this one package because it ships the newest TLS, authentication, and Azure features first.

A basic connection

using Microsoft.Data.SqlClient;
 
var connString =
    "Server=localhost;Database=shop;" +
    "User ID=appuser;Password=secret;" +
    "Encrypt=True;TrustServerCertificate=True;";
 
await using var conn = new SqlConnection(connString);
await conn.OpenAsync();

await using returns the connection to the pool when the scope exits. Open one per unit of work rather than holding a single connection for the life of the app; pooling makes that cheap (more below). The Encrypt and TrustServerCertificate settings are doing real work here, and they are the single biggest source of "it worked on the old driver, now it fails" reports.

The Encrypt default that breaks local setups

Starting with Microsoft.Data.SqlClient 4.0, the default for Encrypt flipped from False to True. The driver now tries to establish an encrypted TLS connection by default and validates the server's certificate. A default local SQL Server install presents a self-signed certificate that your machine does not trust, so the connection fails before you ever run a query:

A connection was successfully established with the server, but then an error
occurred during the login process. (provider: SSL Provider, error: 0 -
The certificate chain was issued by an authority that is not trusted.)

You have three ways to handle this, in order of preference:

1. Production / Azure: install a certificate the client trusts (Azure SQL already presents a valid one) and leave Encrypt=True with TrustServerCertificate=False. This is the only combination that actually protects you from a man-in-the-middle.
2. Local development: set TrustServerCertificate=True. This keeps the connection encrypted but skips certificate validation. It is fine on your own machine and dangerous over a real network, because anyone who can intercept the connection can present any certificate.
3. Reflex you should resist: Encrypt=False. This turns encryption off entirely. It "works," but it sends credentials and data in clear text. Do not reach for it just to silence the error; use TrustServerCertificate=True for local work instead.

The short rule: Encrypt=True;TrustServerCertificate=True for local, Encrypt=True;TrustServerCertificate=False (the default) for anything across a network.

Running a query

await using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT id, name FROM products WHERE price > @minPrice";
cmd.Parameters.AddWithValue("@minPrice", 100);
 
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var id = reader.GetInt32(0);
    var name = reader.GetString(1);
    Console.WriteLine($"{id}: {name}");
}

Always parameterize. String-concatenating user input into SQL is the classic injection hole, and the driver's parameters also handle quoting and type conversion. SQL Server uses @name for parameter markers, both in the SQL text and in the Parameters collection.

AddWithValue is convenient but infers the type from the .NET value, which can produce surprising plans (for example, a .NET string maps to nvarchar, and comparing it to a varchar column can defeat an index). For hot paths, declare the type explicitly:

cmd.Parameters.Add("@code", SqlDbType.VarChar, 20).Value = code;

Getting a generated key

SQL Server's portable way to read back an identity value is the OUTPUT clause, which is safe under concurrency in a way that SELECT SCOPE_IDENTITY() as a second statement is not:

await using var cmd = conn.CreateCommand();
cmd.CommandText = @"
    INSERT INTO products (name, price)
    OUTPUT INSERTED.id
    VALUES (@name, @price)";
cmd.Parameters.AddWithValue("@name", "Widget");
cmd.Parameters.AddWithValue("@price", 9.99m);
 
var newId = (int)(await cmd.ExecuteScalarAsync())!;

OUTPUT INSERTED.id returns the generated key as part of the same statement, so there is no race window and no need for a separate SCOPE_IDENTITY() round trip.

Transactions

await using var tx = await conn.BeginTransactionAsync();
try
{
    await using var cmd = conn.CreateCommand();
    cmd.Transaction = (SqlTransaction)tx;
    cmd.CommandText = "UPDATE accounts SET balance = balance - @amt WHERE id = @from";
    cmd.Parameters.AddWithValue("@amt", 50m);
    cmd.Parameters.AddWithValue("@from", 1);
    await cmd.ExecuteNonQueryAsync();
 
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Every command that should run inside the transaction must have its Transaction property set; the driver does not infer it from the connection. Forgetting this on one command is a common cause of "the rollback didn't undo everything."

Connection pooling

Microsoft.Data.SqlClient pools connections per unique connection string. The default Max Pool Size is 100. When the pool is exhausted, OpenAsync blocks until a connection frees up or times out:

Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached.

That message almost always means leaked connections, not a too-small pool. Look for a code path that opens a connection (or a reader) and never disposes it; await using on both the connection and the reader is the fix. Raising Max Pool Size only delays the same failure.

If you run multiple app instances, size the pool against the server's connection limit: instances times Max Pool Size should stay comfortably under what SQL Server can handle.

Named instances and ports

If SQL Server runs as a named instance (SQLEXPRESS is the common one), connect via the instance name and let the SQL Server Browser service resolve the dynamic port:

var connString = @"Server=localhost\SQLEXPRESS;Database=shop;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;";

The Browser service listens on UDP 1434 and tells the client which TCP port the instance uses. If it is disabled (common in locked-down environments), name resolution fails and you must specify the port directly: Server=localhost,1433. Note the comma before the port, not a colon.

Windows / integrated authentication

On Windows, you can skip username and password and authenticate as the current Windows user:

var connString = "Server=localhost;Database=shop;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;";

For Azure SQL with Microsoft Entra ID, the driver supports several Authentication modes (for example Active Directory Default, which walks the standard credential chain including managed identity). This is the recommended path for Azure-hosted apps because it avoids storing passwords:

var connString = "Server=tcp:myserver.database.windows.net;Database=shop;Authentication=Active Directory Default;Encrypt=True;";

Dapper

Dapper (2.1.79 as of June 2026) layers convenient mapping over the same SqlConnection. It does not replace the driver; it calls it:

using Dapper;
using Microsoft.Data.SqlClient;
 
await using var conn = new SqlConnection(connString);
var products = await conn.QueryAsync<Product>(
    "SELECT id, name, price FROM products WHERE price > @minPrice",
    new { minPrice = 100 });

Dapper passes parameters as @name bound from the anonymous object's property names, so the SQL Server marker style works unchanged. It is a good fit when you want hand-written SQL with object mapping and no change tracking.

EF Core

For a full ORM with migrations, change tracking, and LINQ, use the Microsoft.EntityFrameworkCore.SqlServer provider (10.0.9 as of June 2026). Match the provider's major version to your EF Core version:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer

builder.Services.AddDbContext<AppDb>(options =>
    options.UseSqlServer(connString));

The provider depends on Microsoft.Data.SqlClient, so the same Encrypt and TrustServerCertificate rules apply to the connection string you hand it. EF Core is the productive default for new applications; reach for Dapper or raw ADO.NET when a specific query needs hand-tuning.

Errors you actually hit

For exploring SQL Server data and drafting queries before they go into C#, Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.