SQL Server Security Best Practices Print

SQL Server Security Best Practices

Protect your database and sensitive data with these security configurations.

1. Connection String Security

Never Store Credentials in Code

// BAD - Hardcoded credentials
var connectionString = "Server=myserver;Database=mydb;User Id=admin;Password=secret123;";

// GOOD - Use configuration
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

Use Environment Variables

// appsettings.json (template only)
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=myserver;Database=mydb;User Id=${DB_USER};Password=${DB_PASSWORD};"
  }
}

2. Prevent SQL Injection

Always Use Parameterized Queries

// BAD - SQL Injection vulnerable
string query = $"SELECT * FROM Users WHERE Username = '{username}'";

// GOOD - Parameterized query
string query = "SELECT * FROM Users WHERE Username = @username";
using var command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@username", username);

Entity Framework Core (Safe by Default)

// EF Core uses parameters automatically
var user = await context.Users
    .Where(u => u.Username == username)
    .FirstOrDefaultAsync();

// Avoid raw SQL unless necessary
// If you must use raw SQL, still parameterize:
var users = await context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Username = {0}", username)
    .ToListAsync();

3. Database User Permissions

Principle of Least Privilege

• Create dedicated database users per application
• Grant only required permissions (SELECT, INSERT, UPDATE, DELETE)
• Never use 'sa' or admin accounts in applications
• Use separate accounts for read-only operations

Permission Levels

4. Encrypt Sensitive Data

Encrypt Connection

Server=myserver;Database=mydb;User Id=user;Password=pass;Encrypt=True;TrustServerCertificate=False;

Encrypt Sensitive Columns

For highly sensitive data, consider:

• Application-level encryption before storing
• SQL Server Always Encrypted (if available)
• Hashing for passwords (never store plain text)

Password Hashing Example

// ASP.NET Core Identity handles this automatically
// For custom implementation:
using Microsoft.AspNetCore.Identity;

var hasher = new PasswordHasher<User>();
string hashedPassword = hasher.HashPassword(user, plainPassword);

// Verify password
var result = hasher.VerifyHashedPassword(user, hashedPassword, inputPassword);

5. Audit and Monitoring

Log Database Access

// Enable EF Core logging
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information));

Track Changes

• Implement audit tables for sensitive data changes
• Record who changed what and when
• Review logs regularly for suspicious activity

Security Checklist

1. ☐ Credentials stored in configuration, not code
2. ☐ All queries use parameters (no string concatenation)
3. ☐ Database user has minimal required permissions
4. ☐ Connection uses encryption
5. ☐ Passwords are hashed, never stored plain text
6. ☐ Sensitive data is encrypted at rest
7. ☐ Regular backups are in place
8. ☐ Access logs are reviewed