Securing SQL Server for ASP.NET Applications: Essential Tips

SQL Server has shipped enterprise-grade security primitives for over a decade, but most ASP.NET apps still ship with a SQL user that has db_owner rights and a connection string in plaintext. The gap between "what SQL Server can do" and "what the app actually configures" is where most data-breach blast radius hides. SQL Server 2022 brought five new defenses (ledger tables, Always Encrypted with secure enclaves, Microsoft Entra ID-only auth, and more) that most teams haven't adopted yet.

This is the SQL hardening playbook we apply to every database running on Adaptive's ASP.NET Core hosting — 10 strategies, threat-mapped, with the configuration and the SQL Server feature that backs each.

2022SQL Server version

5New 2022-only features

FREESQL Server on every plan

What we're defending against

SQL Injection

Still in the OWASP top 10. Mitigated by parameterization — but most legacy code still concatenates.

🔴 Critical

Database Account Compromise

Leaked connection string → instant db_owner access. Least-privilege fixes the blast radius.

🟠 High

Privileged Insider

DBAs / admins seeing data they shouldn't. Always Encrypted + masking solve this.

🟠 High

Ransomware

Encrypted backup + immutable ledger = recovery without paying.

🟠 High

Cross-Tenant Leakage

Multi-tenant SaaS pulling other tenants' data via WHERE clause bugs. RLS makes it impossible.

🟠 High

Audit Trail Tampering

"Did the rogue insider delete their tracks?" Ledger tables make tampering cryptographically detectable.

Quick reference: the 10 strategies

  • Parameterized Queries — Always

String concatenation in SQL is still in production code in 2026. "SELECT * FROM users WHERE id = " + userId is one user input away from DROP TABLE users; --.

Use parameters always. EF Core + LINQ does it for you; raw ADO.NET and Dapper require explicit parameters:

// ❌ NEVER — string concatenation

var sql = $"SELECT * FROM Orders WHERE CustomerId = {customerId}";

// ✅ ADO.NET with parameters

using var cmd = new SqlCommand("SELECT * FROM Orders WHERE CustomerId = @cid", conn);

cmd.Parameters.Add("@cid", SqlDbType.Int).Value = customerId;

// ✅ Dapper — parameters live in the object

var orders = await conn.QueryAsync<Order>(

"SELECT * FROM Orders WHERE CustomerId = @cid",

new { cid = customerId });

// ✅ EF Core — parameterized by default

var orders = await db.Orders.Where(o => o.CustomerId == customerId).ToListAsync();

Add a Roslyn analyzer (Microsoft.CodeAnalysis.NetAnalyzers CA2100) that fails the build on string concatenation in SQL. Cheap to add, catches the regression class permanently.

  • Microsoft Entra ID Auth — Kill SQL Auth Where Possible

SQL Server logins live in a plaintext connection string somewhere — in source control, in Azure App Service config, in a developer's Notes app. Leak the string, lose the database. Entra ID auth eliminates the credential.

SQL Server 2022 supports Entra-ID-only authentication mode. Application connects using a Managed Identity or workload identity:

// Connection string with no password — Entra ID via Managed Identity

var conn = "Server=tcp:myserver.database.windows.net,1433;" +

"Database=AppDb;" +

"Authentication=Active Directory Default;" +

"Encrypt=True;TrustServerCertificate=False;";

using var sqlConn = new SqlConnection(conn);

await sqlConn.OpenAsync();

Adaptive's managed SQL Server 2022 supports SQL authentication out of the box. For Entra ID integration on Adaptive-hosted databases, contact support — it requires per-instance configuration. For most B2B web apps on a single-instance database, strong SQL auth + connection string in encrypted appsettings or a secret manager is a workable baseline.

  • Least-Privilege Schema Design

App connects as db_owner. SQL injection or app compromise → attacker can DROP tables, modify schema, or read every table including those they shouldn't.

Three roles, scoped to schemas:

-- One read-only role for reporting / read-replicas

CREATE ROLE app_reader;

GRANT SELECT ON SCHEMA::dbo TO app_reader;

-- One read-write role for the main app — NO DDL, NO admin

CREATE ROLE app_writer;

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_writer;

-- DDL only via deployment account (separate principal, used by migrations)

CREATE ROLE app_migrator;

GRANT CREATE TABLE, ALTER ON SCHEMA::dbo TO app_migrator;

-- Sensitive PII in its own schema with stricter ACLs

CREATE SCHEMA sensitive;

CREATE ROLE app_pii_reader;

GRANT SELECT ON SCHEMA::sensitive TO app_pii_reader;

-- only specific service accounts ever get app_pii_reader

-- Grant the app's identity ONLY app_writer:

CREATE USER [appsvc] FROM LOGIN [appsvc];

ALTER ROLE app_writer ADD MEMBER [appsvc];

Migrations run under a separate identity from runtime. SQL injection through your runtime can't DROP tables because the runtime principal lacks DDL grants. The deployment pipeline is the only path that can change schema.

  • Transparent Data Encryption (TDE)

Encrypt-at-rest, automatic, app-transparent. A backup file copied off the server is unreadable without the DEK. On SQL Server 2022, TDE uses AES-256 and rotates the database encryption key on demand:

-- One-time setup per server

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong-password>';

CREATE CERTIFICATE TDECert WITH SUBJECT = 'App TDE Certificate';

-- Per database

USE AppDb;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE AppDb SET ENCRYPTION ON;

-- Verify

SELECT name, is_encrypted FROM sys.databases WHERE name = 'AppDb';

If you lose the certificate, you lose every TDE-encrypted backup. Store the certificate (with its password) in a secret manager or KMS — never on the same disk as the database. We've seen real incidents where the cert backup was on the same volume that got encrypted by ransomware.

  • Always Encrypted — Column-Level Encryption

TDE protects data at rest. Always Encrypted protects data even from DBAs, server admins, and the SQL Server process itself. Encryption/decryption happens in the client driver; the server never sees plaintext.

-- Create a column master key (in Windows Cert Store, Azure Key Vault, or HSM)

CREATE COLUMN MASTER KEY [CMK_Auto1] WITH (

KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',

KEY_PATH = N'https://myvault.vault.azure.net/keys/cmk1/abc123'

);

-- Encrypted column on the table

CREATE TABLE Patients (

Id INT PRIMARY KEY,

Name NVARCHAR(100) COLLATE Latin1_General_BIN2

ENCRYPTED WITH (

COLUMN_ENCRYPTION_KEY = CEK_Auto1,

ENCRYPTION_TYPE = DETERMINISTIC,

ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'

) NOT NULL,

SSN CHAR(11) COLLATE Latin1_General_BIN2

ENCRYPTED WITH (

COLUMN_ENCRYPTION_KEY = CEK_Auto1,

ENCRYPTION_TYPE = RANDOMIZED,

ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'

) NOT NULL

);

// Client connection — enable Always Encrypted, fetch keys from Key Vault

var connStr = "Server=...;Database=AppDb;Column Encryption Setting=Enabled;";

SqlColumnEncryptionAzureKeyVaultProvider akvProvider =

new(new DefaultAzureCredential());

SqlConnection.RegisterColumnEncryptionKeyStoreProviders(new()

{

[SqlColumnEncryptionAzureKeyVaultProvider.ProviderName] = akvProvider

});

// App writes plaintext — driver encrypts before transit

using var conn = new SqlConnection(connStr);

await conn.OpenAsync();

using var cmd = new SqlCommand(

"INSERT INTO Patients (Id, Name, SSN) VALUES (@id, @name, @ssn)", conn);

cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "Jane Doe";

cmd.Parameters.Add("@ssn", SqlDbType.Char, 11).Value = "123-45-6789";

DETERMINISTIC encrypts the same plaintext to the same ciphertext — allows WHERE Name = @name equality queries. RANDOMIZED is stronger but you can't filter/sort on the column. Use DETERMINISTIC for lookup keys, RANDOMIZED for high-sensitivity payloads.

  • Row-Level Security (RLS)

In multi-tenant SaaS, one missing WHERE TenantId = @currentTenant = cross-tenant data leak. The app code becomes the only line of defense, and humans miss it. RLS pushes the predicate to the database — impossible to forget.

-- The predicate function — runs on every query

CREATE FUNCTION dbo.fn_tenantFilter(@tenantId INT) RETURNS TABLE

WITH SCHEMABINDING

AS RETURN

SELECT 1 AS allowed

WHERE @tenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

-- The policy — bind the predicate to a table

CREATE SECURITY POLICY dbo.TenantFilterPolicy

ADD FILTER PREDICATE dbo.fn_tenantFilter(TenantId) ON dbo.Orders,

ADD BLOCK PREDICATE dbo.fn_tenantFilter(TenantId) ON dbo.Orders AFTER INSERT

WITH (STATE = ON);

// App sets session context once per connection

using var conn = new SqlConnection(connStr);

await conn.OpenAsync();

using (var sc = new SqlCommand("EXEC sp_set_session_context @key = N'TenantId', @value = @t", conn))

{

sc.Parameters.AddWithValue("@t", currentTenantId);

await sc.ExecuteNonQueryAsync();

}

// Now every query is automatically tenant-scoped

var orders = await conn.QueryAsync<Order>("SELECT * FROM Orders WHERE Id = @id", new { id = orderId });

// Returns nothing if Id belongs to a different tenant — RLS filters at the storage engine layer

  • Dynamic Data Masking

Hide sensitive data from users who don't need to see it (support staff, reporting users) without changing the underlying value:

CREATE TABLE Customers (

Id INT PRIMARY KEY,

Email VARCHAR(255) MASKED WITH (FUNCTION = 'email()'),

Phone VARCHAR(20) MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)'),

SSN CHAR(11) MASKED WITH (FUNCTION = 'default()')

);

-- Grant SELECT but no UNMASK permission

GRANT SELECT ON Customers TO support_role;

-- support_role sees: [email protected], XXX-XXX-1234, xxxx

-- DBAs / admins with UNMASK permission see the real values

DDM hides values in result sets, but the underlying data is still plaintext and a privileged user with UNMASK can see it. Use it as a defense-in-depth control alongside Always Encrypted — not a replacement.

  • Ledger Tables — Tamper-Evident History

Ledger tables produce a cryptographic hash chain over every change. An attacker (or rogue DBA) who modifies historical rows breaks the hash — and any auditor running sp_verify_database_ledger sees it immediately. For the broader .NET 10 LTS upgrade context, see the .NET 10 LTS performance comparison.

CREATE TABLE AuditLog (

Id BIGINT PRIMARY KEY,

UserId INT NOT NULL,

Action VARCHAR(64) NOT NULL,

Details NVARCHAR(MAX),

OccurredAt DATETIME2 NOT NULL

)

WITH (

SYSTEM_VERSIONING = ON,

LEDGER = ON

);

-- Verify integrity (run from CI / scheduled job)

EXEC sp_verify_database_ledger N'<digest-from-azure-confidential-ledger>';

Use ledger tables for any data with regulatory audit requirements: financial transactions, healthcare records, compliance evidence, employee actions in regulated industries. The pattern pairs well with the compliance-heavy Blazor use case described in our enterprise Blazor patterns article (#7 — keep code server-side).

  • SQL Audit + Extended Events

When (not if) you have an incident, the question is "who ran which query, when?". Without server audit, you have nothing. Set up at minimum:

-- Server-level audit destination — separate disk volume

CREATE SERVER AUDIT AppDb_Audit

TO FILE (FILEPATH = 'D:\SQLAudit\', MAXSIZE = 100MB, MAX_FILES = 50)

WITH (ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT AppDb_Audit WITH (STATE = ON);

-- Database-level — log DDL, permission changes, and failed logins

CREATE DATABASE AUDIT SPECIFICATION AppDb_AuditSpec

FOR SERVER AUDIT AppDb_Audit

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP)

WITH (STATE = ON);

-- Forward to SIEM (Splunk, Datadog, Elastic) via Azure Sentinel or fluent-bit reading the audit files

  • Encrypted, Off-Site, Tested Backups

Most ransomware encrypts backups too. The recovery path is: backups encrypted with a key the attacker doesn't have, stored off-site (different account, different region, different credential), and tested to restore at least quarterly.

-- Encrypted backup (SQL Server 2014+)

BACKUP DATABASE AppDb

TO DISK = 'D:\Backups\AppDb_2026-05-21.bak'

WITH ENCRYPTION (

ALGORITHM = AES_256,

SERVER CERTIFICATE = TDECert

),

CHECKSUM, INIT;

-- Push to S3 / Azure Blob in a different region, different account

-- Tools: msodbcsql + AWS CLI, or Ola Hallengren's maintenance solution

3 copies of your data, on 2 different storage tiers, with 1 immutable / air-gapped copy. Adaptive plans include automated database backups; for the immutable off-site copy, ship to S3 Object Lock or Azure Immutable Blob.

Production readiness checklist

✅ Encryption

TDE enabled on every production database

Always Encrypted on PII columns

TDE certificate backed up off-server

TLS 1.3 enforced for SQL connections

✅ Multi-Tenant Isolation

RLS policies on every tenant-scoped table

Session context set once per connection

Migration tested to confirm RLS doesn't break

Reporting connections use a tenant-scoped principal

✅ Recovery & Audit

Server audit forwarding to SIEM

Ledger tables on regulated data

Encrypted backups in different region

Quarterly restore drill — verified, not assumed

Hosting-Layer Security Adaptive Provides

Every Adaptive Web Hosting plan includes the SQL Server primitives most teams need:

LayerWhat's Included

SQL Server versionSQL Server 2022 — ledger tables, Always Encrypted with secure enclaves, RLS, TDE all available

NetworkTLS 1.3 termination, FREE SSL on every site, post-quantum-ready on .NET 10 LTS hosting

BackupsAutomated database backups; ship encrypted copies to your own off-site storage

IsolationDedicated IIS Application Pools per site — one compromised app can't query another's database

PatchingSQL Server cumulative updates applied on managed cadence

InfrastructureAWS US-East data center, 99.99% uptime SLA, 30-day money-back guarantee

Choose a plan

$9.49/mo

SQL Server 2022 + 1 GB dedicated app pool. Right for dev / staging databases and small production apps.

View Developer plan →

Popular

ASP.NET Business

$17.49/mo

SQL Server 2022 + 2 GB dedicated app pool + WAF. The right tier for production apps with sensitive data.

View Business plan →

ASP.NET Professional

$27.49/mo

SQL Server 2022 + 4 GB dedicated app pool + 10 sites + 200 GB storage. For agencies / SaaS with multiple databases.

View Professional plan →

Frequently Asked Questions

Do I really need both TDE and Always Encrypted?

Yes — they defend against different threat models. TDE protects against backup theft (file copy off the disk); Always Encrypted protects against privileged-insider attacks (DBA viewing PII, server admin tampering with the SQL process). Most regulated workloads need both. Always Encrypted is the bigger lift; TDE is a one-time setup with no app changes.

Does Always Encrypted work with EF Core?

Yes — the encryption happens in the SqlClient driver, transparent to EF Core. The catch: equality filters work on DETERMINISTIC columns, but range / LIKE queries don't (the encrypted ciphertext doesn't preserve ordering). Plan your schema with that constraint in mind, or use Always Encrypted with secure enclaves on SQL Server 2022 for richer query support.

What's the performance cost of RLS?

Single-digit milliseconds per query in our measurements — the security predicate compiles into the query plan alongside the user's own WHERE clauses. The performance ceiling is when your predicate function is itself expensive (lots of subqueries, joins to a permissions table). Keep the predicate simple — a single equality check against session context is fastest.

Should I use Dynamic Data Masking for compliance?

Not as your primary control. DDM hides values in result sets but doesn't encrypt the underlying data — any user with the UNMASK permission (or one that can be granted via SQL injection escalation) sees the real values. Use it as a defense-in-depth layer alongside Always Encrypted for the regulated columns, RLS for tenant scoping, and least-privilege schema for access boundaries.

How often should we test backup restores?

Quarterly minimum. Annual is too infrequent — backups silently rotate to a corrupt state and you only find out during the incident you're trying to recover from. We've reviewed two incidents in the last 18 months where the team had "backups" but no working restore path because the encryption key had been lost.

What does "secure enclave" mean for Always Encrypted?

SQL Server 2022 introduced secure enclaves — a hardware-isolated execution environment (Intel SGX or VBS enclaves) where the SQL engine can perform comparisons on encrypted data without seeing plaintext. This unlocks range queries and pattern matching on Always Encrypted columns. Requires SQL Server 2022 + a host that supports enclaves; ask your provider whether yours does.

Do ledger tables replace SQL Audit?

Different scopes. Ledger tables provide tamper-evidence for the data itself — was this row modified outside the documented transaction history? SQL Audit captures who ran which command, regardless of data state. Use both: ledger for the integrity question, audit for the access-and-execution question.

How does this article relate to the broader ASP.NET Core security playbook?

Database security sits beneath the application security layer. Read the ASP.NET Core security best practices for the framework-level controls, the API security strategies for endpoint-level controls, and this article for the database-level controls. All three layers reinforce each other — a breach at any one layer should not compromise the others.

Bottom line

Most SQL Server security incidents we review trace to one of three patterns: db_owner in production, plaintext PII columns, and no working backup restore path. Fixing those three things — least-privilege schemas, Always Encrypted on sensitive columns, and a tested 3-2-1 backup strategy — eliminates the bulk of the realistic blast radius. The other seven strategies above raise the ceiling further; the first three are the floor.

On Adaptive Web Hosting, real SQL Server 2022 is included on every tier — ledger tables, Always Encrypted, RLS, TDE, and Dynamic Data Masking are all available. ASP.NET Developer ($9.49/mo) for development databases, ASP.NET Business ($17.49/mo) for the typical production deployment, ASP.NET Professional ($27.49/mo) for agencies / SaaS with multiple databases. Every plan ships with a 30-day money-back guarantee.

If you're still on SQL Server's older defaults from .NET Framework days, the ASP.NET 4.8 → .NET 10 LTS migration guide covers the modernization path including database upgrades. For Blazor apps storing sensitive data, our Blazor security strategies covers the UI-layer controls that pair with the database controls above. View all plans, read the ASP.NET Core security best practices, or talk to a database engineer about a specific compliance scenario.

Back to Blog