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.
What we're defending against
SQL Injection
Still in the OWASP top 10. Mitigated by parameterization — but most legacy code still concatenates.
Database Account Compromise
Leaked connection string → instant db_owner access. Least-privilege fixes the blast radius.
Privileged Insider
DBAs / admins seeing data they shouldn't. Always Encrypted + masking solve this.
Ransomware
Encrypted backup + immutable ledger = recovery without paying.
Cross-Tenant Leakage
Multi-tenant SaaS pulling other tenants' data via WHERE clause bugs. RLS makes it impossible.
Audit Trail Tampering
"Did the rogue insider delete their tracks?" Ledger tables make tampering cryptographically detectable.
Quick reference: the 10 strategies
| # | Strategy | SQL Server feature | Effort |
|---|---|---|---|
| 1 | Parameterized queries — always | Built-in / EF Core | Low |
| 2 | Microsoft Entra ID auth (kill SQL auth) | Entra-only mode | Medium |
| 3 | Least-privilege schema design | Database roles + schemas | Medium |
| 4 | Transparent Data Encryption (TDE) | Encrypt-at-rest, automatic | Low |
| 5 | Always Encrypted (column-level) | App-level encryption | Medium |
| 6 | Row-Level Security (RLS) | Security policies + predicates | Medium |
| 7 | Dynamic Data Masking | Per-column mask functions | Low |
| 8 | Ledger tables (tamper-evident) | SQL Server 2022 ledger | Medium |
| 9 | SQL Audit + extended events | CREATE SERVER AUDIT | Medium |
| 10 | Encrypted, off-site, tested backups | BACKUP DATABASE WITH ENCRYPTION | Low |
1. 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.
2. 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 2025 and 2022 support 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.
3. 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.
4. 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.
5. 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.
6. 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
7. 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.
8. 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).
9. 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
10. 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
✅ Authentication & Access
- App connects as least-privileged role (no
db_owner) - Migrations run under separate identity
- Connection strings in secret manager, never source control
- Entra ID auth where supported
✅ 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:
| Layer | What's Included |
|---|---|
| SQL Server version | SQL Server 2025 & 2022 — ledger tables, Always Encrypted, row-level security and Dynamic Data Masking available |
| Network | TLS 1.3 termination, FREE SSL on every site, post-quantum-ready on .NET 10 LTS hosting |
| Backups | Automated database backups; ship encrypted copies to your own off-site storage |
| Isolation | Dedicated IIS Application Pools per site — one compromised app can't query another's database |
| Patching | SQL Server cumulative updates applied on managed cadence |
| Infrastructure | AWS US-East data center, 99.99% uptime SLA, 30-day money-back guarantee for new accounts |
Choose a plan
SQL Server 2025 and 2022 + 1 GB dedicated app pool. Right for dev / staging databases and small production apps.
SQL Server 2025 and 2022 + 2 GB dedicated app pool + WAF. The right tier for production apps with sensitive data.
SQL Server 2025 and 2022 + 4 GB dedicated app pool + 10 sites + 200 GB storage. For agencies / SaaS with multiple databases.
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 2025 and 2022 are included on every tier — ledger tables, Always Encrypted, row-level security, and Dynamic Data Masking are all available. SQL Server 2025 is now available on every plan, and you choose the version per database in Plesk. 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. New accounts get a 30-day money-back guarantee on their hosting plan.
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.
