SQL Server Data Encryption Using Symmetric Keys (AES-256)

In this guide, we will learn how to encrypt data in SQL Server using symmetric keys (AES-256) with a complete step-by-step example. We will also explore how EncryptByKey and DecryptByKey work in SQL Server and understand the full SQL Server encryption hierarchy.

Along with implementation, we will break down a critical mistake most developers make—using encryption for passwords—and why hashing is the correct approach for password security.

SQL Server Encryption Hierarchy Explained (Master Key, Certificate, Symmetric Key)

SQL Server uses a layered encryption model where each component protects the next:

Master Key → Certificate → Symmetric Key → Encrypted Data

Master Key: Root key of the database encryption system
Certificate: Protects symmetric keys
Symmetric Key (AES-256): Encrypts and decrypts actual data
Encrypted Data: Stored securely as VARBINARY

How to Encrypt Data in SQL Server Using Symmetric Key (Step-by-Step)

Step 1: Create Master Key in SQL Server

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'Strong@123Password';

This is the root encryption key required before creating certificates and symmetric keys.

Step 2: Create Certificate for Encryption

CREATE CERTIFICATE TestCert
WITH SUBJECT = 'Test Data Encryption Certificate';

Certificates are used to protect symmetric keys in SQL Server.

Step 3: Create Symmetric Key (AES-256 Encryption)

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

This symmetric key uses AES-256 encryption to secure your data.

Step 4: Create Table for Encrypted Data

CREATE TABLE TestUsers
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    UserName NVARCHAR(100),
    EncryptedPassword VARBINARY(MAX)
);

The encrypted value is stored as VARBINARY, not plain text.

Step 5: Encrypt Data Using EncryptByKey

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE TestCert;

INSERT INTO TestUsers (UserName, EncryptedPassword)
VALUES
(
    'Shahnawaz',
    EncryptByKey(Key_GUID('TestSymKey'), 'MySecret@123')
);

CLOSE SYMMETRIC KEY TestSymKey;

Open the symmetric key before encryption
EncryptByKey converts plain text into encrypted binary
Data is stored securely in the database

Step 6: Decrypt Data Using DecryptByKey

OPEN SYMMETRIC KEY TestSymKey
DECRYPTION BY CERTIFICATE TestCert;

SELECT 
    UserName,
    CONVERT(VARCHAR(100), DecryptByKey(EncryptedPassword)) 
    AS DecryptedPassword
FROM TestUsers;

DecryptByKey retrieves original data
Symmetric key must be opened before decryption

SQL Server EncryptByKey and DecryptByKey Example Explained

SQL Server provides built-in functions like EncryptByKey and DecryptByKey to handle encryption and decryption using symmetric keys.

These functions are widely used in column-level encryption in SQL Server where sensitive fields such as API keys, personal data, or financial information must be protected.

Encryption vs Hashing in SQL Server (Why Password Encryption is Wrong)

Encryption	Hashing
Reversible	Irreversible
Used for sensitive data	Used for passwords
Can decrypt data	Cannot retrieve original value

Using SQL Server encryption for passwords is a critical security mistake because encrypted passwords can be decrypted if keys are compromised.

How to Store Password Securely in SQL Server

Instead of encryption, use hashing algorithms for password storage:

Password → Hash → Store Hash
Login → Hash Input → Compare Hash

bcrypt
PBKDF2
Argon2

In .NET applications, always use password hashing libraries instead of SQL encryption.

Common Errors in SQL Server Encryption and How to Fix Them

Symmetric key not opening in SQL Server
DecryptByKey returning NULL
Certificate not found error
Key_GUID not working properly

These are common issues developers face while working with SQL Server encryption.

When to Use SQL Server Encryption (Real Use Cases)

Aadhaar or PAN data protection
Bank account details encryption
API keys and secrets storage
Personally identifiable information (PII)

Use encryption only when data needs to be decrypted later.

Final Thoughts on SQL Server Encryption

SQL Server symmetric key encryption using AES-256 is powerful, but it must be used correctly. It is ideal for protecting sensitive data but should never be used for authentication systems.

A strong backend developer understands not just how to implement encryption, but when not to use it.

Summary

SQL Server encryption hierarchy: Master Key → Certificate → Symmetric Key
AES-256 is used for secure data encryption
EncryptByKey and DecryptByKey handle encryption operations
Use hashing instead of encryption for passwords

IndianTechnoEra