How to Encrypt Passwords in SQL Server 2012 A Step By Step Guide: Hashing, Salting, and Best Practices

Hash and salt passwords, don’t encrypt them. use HASHBYTES with a per-password salt and store the salt and hash. In SQL Server 2012 you can implement salted hashes using HASHBYTES and CRYPT_GEN_RANDOM, then verify by re-hashing user input with the same salt. This guide walks you through a practical, step-by-step approach, with ready-to-use SQL snippets, best practices, and concrete examples so you can implement secure password storage today.

• In this guide you’ll learn why hashing beats encryption for passwords
• How to generate per-user salts securely in SQL Server 2012
• How to implement a salt + hash password storage model with optional iterations for “key stretching”
• How to verify a password at login
• How to migrate existing passwords to a salted hash
• Common pitfalls and performance considerations
• Practical code samples you can copy-paste

Useful URLs and Resources un-clickable text:

• NIST Password Guidance – nist.gov
• OWASP Password Storage Cheat Sheet – owasp.org
• SQL Server 2012 Books Online – Microsoft Docs – docs.microsoft.com
• CRYPTOGRAPHY in SQL Server – TechNet articles
• Hashing vs Encryption: overview references – en.wikipedia.org/wiki/Cryptographic_hash_function

Introduction: what you’ll get and how to approach it
Hash and salt passwords, don’t encrypt them. use HASHBYTES with a per-password salt. In SQL Server 2012 you can implement salted hashes using HASHBYTES and CRYPT_GEN_RANDOM, then verify by re-hashing the input with the stored salt. This article provides a practical, step-by-step approach to securely storing passwords in SQL Server 2012, including:

• A real-world data model for storing password hash, salt, and iterations
• A reusable SQL function to derive a salted hash
• Stored procedures to create users and verify credentials
• A migration plan for existing passwords
• Tips on peppering, iteration counts, and performance
• A FAQ section with common questions and clear answers

Data-driven context you can rely on

• Security best practices increasingly emphasize salted hashes with a computational cost iterations to deter brute-force attacks. SHA-512 is widely used in enterprise settings as a strong hash function, but for password storage many teams also rely on specialized algorithms bcrypt, scrypt, Argon2 that are designed for password hashing. In SQL Server 2012, the most practical approach is salted hashing with a configurable number of iterations to achieve a reasonable cost on modern hardware.
• A per-user salt ensures that even identical passwords produce different hashes, so a breach of one hash doesn’t reveal others.
• Storing the salt alongside the hash and the iterations used is essential to verify passwords later during login.

Now, let’s get into the hands-on part. The steps below are designed to be copy-paste friendly and easy to adapt.

Body

Why hashing is the right choice for passwords not encryption

• Passwords should be stored in a way that even if the database is compromised, attackers can’t easily recover the original passwords.
• Encryption is reversible if the key is compromised. hashing is one-way, which is why it’s preferred for password storage.
• Salt ensures that the same password never hashes to the same value, thwarting rainbow table attacks.
• Iterations key stretching increase the time it takes to compute each guess, slowing attackers down.

Key components you’ll implement

• Salt: a unique per-password random value e.g., 16 bytes
• Hash: a cryptographic hash of password + salt
• Iterations: a count to re-hash the result multiple times e.g., 10,000
• Optional pepper: a server-wide secret value stored outside the database additional defense

Prerequisites and setup

• SQL Server 2012 or compatible
• A test database with permission to create tables, functions, and stored procedures
• Basic familiarity with T-SQL and stored procedures

Step 1: Create the user table to store hash, salt, and iterations

CREATE TABLE dbo.Users 
  UserID INT IDENTITY1,1 PRIMARY KEY,
  Username NVARCHAR128 NOT NULL UNIQUE,
  PasswordHash VARBINARY64 NOT NULL,
  Salt VARBINARY16 NOT NULL,
  Iterations INT NOT NULL,
  CreatedAt DATETIME2 DEFAULT GETDATE,
  LastLogin DATETIME2 NULL
.

- Why these types? SHA-512 produces 64 bytes, hence PasswordHash VARBINARY64. Salt is 16 bytes, a common size that provides strong randomness.

 Step 2: Create a helper function to derive a salted hash PBKDF2-style
Note: SQL Server 2012 doesn’t have a built-in PBKDF2 function, so we implement a simple, iteration-based hash. This is a practical approximation suitable for many apps. you can increase iterations to raise cost.

CREATE FUNCTION dbo.fn_PBKDF2_SHA512

  @Password NVARCHAR128,
  @Salt VARBINARY16,
  @Iterations INT

RETURNS VARBINARY64
AS
BEGIN
  DECLARE @PasswordBytes VARBINARY128 = CAST@Password AS VARBINARY128.
  DECLARE @Hash VARBINARY64.
  -- initial hash
  SET @Hash = HASHBYTES'SHA2_512', @PasswordBytes + @Salt.

  DECLARE @i INT = 1.
  WHILE @i < @Iterations
  BEGIN
     -- re-hash with the salt to introduce cost
     SET @Hash = HASHBYTES'SHA2_512', @Hash + @Salt.
     SET @i = @i + 1.
  END

  RETURN @Hash.
END
GO

- Why this function? It gives you a repeatable, server-side way to derive a password hash with a configurable cost.

 Step 3: Step-by-step usage example: create a user with a salted hash
-- Parameters example
DECLARE @Username NVARCHAR128 = N'alice'.
DECLARE @PlainPassword NVARCHAR128 = N'SecureP@ssw0rd!'.
DECLARE @Salt VARBINARY16 = CRYPT_GEN_RANDOM16.
DECLARE @Iterations INT = 10000.
DECLARE @PasswordHash VARBINARY64 = dbo.fn_PBKDF2_SHA512@PlainPassword, @Salt, @Iterations.

-- Insert the new user
INSERT INTO dbo.Users Username, PasswordHash, Salt, Iterations
VALUES @Username, @PasswordHash, @Salt, @Iterations.

- The CRYPT_GEN_RANDOM function provides a cryptographically secure random salt.
- The iterations value here 10,000 adds computational cost to password hashing, slowing down brute-force attempts.
- If you plan to support pepper, you can incorporate a server-wide secret by mixing it into the password before hashing, but keep in mind pepper storage and rotation considerations.

 Step 4: Verifying a password during login
-- Credentials supplied by the user at login
DECLARE @AttemptPassword NVARCHAR128 = N'SecureP@ssw0rd!'.

DECLARE @StoredHash VARBINARY64.
DECLARE @Salt VARBINARY16.
DECLARE @Iterations INT.

SELECT @StoredHash = PasswordHash, @Salt = Salt, @Iterations = Iterations
FROM dbo.Users
WHERE Username = @Username.

IF @StoredHash IS NULL
  PRINT 'User not found'.
ELSE
  DECLARE @AttemptHash VARBINARY64 = dbo.fn_PBKDF2_SHA512@AttemptPassword, @Salt, @Iterations.

  IF @AttemptHash = @StoredHash
     PRINT 'Authentication successful'.
  ELSE
     PRINT 'Authentication failed'.

- This approach ensures you never expose plaintext passwords and only compare derived hashes.

 Step 5: Migration plan for existing passwords if you already have passwords in plaintext or hashed form
- If you have plaintext passwords, you can migrate gradually by re-hashing them with a per-user salt as users log in.
- For existing hashed passwords without salt, you’ll need a one-time migration policy:
  - Phase 1: Add a new Salt and Iterations column to the Users table
  - Phase 2: For each user, when you re-hash on their next login, generate a new salt, recompute the hash using their plaintext password, and store the new hash and salt
  - Phase 3: Decommission old password handling methods once all accounts have migrated

- If you already store salted hashes but without iterations, consider re-hashing with a higher iteration count on next password change, and keep a migration window to rehash older accounts over time.

 Step 6: Best practices, pitfalls, and enhancements
- Always use a per-password salt and store it with the hash
- Use a reasonable number of iterations to balance security and performance
- Consider peppering the password with a server-wide secret kept outside the database. rotate the pepper on a scheduled basis
- Prefer a modern, dedicated password-h hashing approach bcrypt/scrypt/Argon2 if you can implement it outside SQL Server, or in application code that talks to SQL Server
- Keep the hashing logic in a centralized place a user-defined function and a stored procedure to avoid code duplication
- Use parameterized queries to prevent SQL injection when handling user input
- Regularly audit and rotate credentials for accounts with database access
- If you scale up to thousands or millions of users, test performance and consider moving heavy lifting to the application layer if needed

 Step 7: Performance considerations and real-world numbers
- Each iteration adds roughly the same order of cost to every password hash. With 10,000 iterations, you might see tens of milliseconds per hash on a typical SQL Server 2012 instance, depending on hardware and the workload.
- The benefit of iteration is that it makes brute-force attempts significantly more expensive for attackers. the trade-off is a small, predictable cost per login.
- If you anticipate heavy login traffic, consider keeping a lower iteration count on peak times and increasing during maintenance windows, or migrate the heavy lifting to the application tier where you can scale more flexibly.

 Step 8: Additional protection ideas you can add later
- Pepper: add a server-wide secret to the password before hashing. Do not store this secret in the database. load it into memory at startup and use it in the hashing routine.
- Key rotation: periodically rotate salts and, if feasible, the underlying hash scheme to keep security current.
- Access controls: limit who can query the Users table and who can execute hashing operations. Audit any changes to security-related code.

Table: quick reference checklist
- Salt per password: Yes CRYPT_GEN_RANDOM16
- Hash algorithm: SHA2-512 HASHBYTES
- Iterations: 10,000 adjust as needed
- Data types: PasswordHash VARBINARY64, Salt VARBINARY16, Iterations INT
- Verification: Recompute hash with same Salt and Iterations, compare to stored hash
- Encryption vs hashing: Hashing with salt is recommended for passwords

 Quick-start recipe condensed
- Create table
- Create function dbo.fn_PBKDF2_SHA512
- Add a user with a salt, iterations, and derived hash
- Verify a login by recomputing the hash and comparing
- Plan migration for existing accounts if needed

 What to tell your team
- The recommended practice for password storage today is salted hashing with a configurable iteration count. Encryption is not appropriate for passwords because it’s reversible with a key. SQL Server 2012 provides the right building blocks HASHBYTES, CRYPT_GEN_RANDOM to implement a solid salted hash strategy in-database, while you can still leverage application-layer logic for more advanced password-hashing schemes if needed.

 FAQ — Frequently Asked Questions
# What’s the difference between hashing and encryption for passwords?
Hashing is a one-way function that turns a password into a fixed-length value and cannot be reversed. Encryption is reversible with a key. For password storage, hashing with a salt and iterations is the recommended approach because it prevents recovery of the original password.

# Why do I need a salt?
A salt makes each password hash unique even if two users have the same password. It defends against precomputed rainbow table attacks and ensures identical passwords don’t reveal identical hashes.

# How do I generate a salt in SQL Server 2012?
Use CRYPT_GEN_RANDOM, for example: DECLARE @Salt VARBINARY16 = CRYPT_GEN_RANDOM16.

# How many iterations should I use?
Common starting points are 10,000 to 100,000 iterations. The exact number depends on your hardware and desired login latency. Start with 10,000 and test in your environment, then adjust as needed.

# Can I use SHA-256 instead of SHA-512?
SHA-512 is generally stronger and provides a larger digest. If you’re constrained to SHA-256 for compatibility, you can use HASHBYTES'SHA2_256', ... but SHA-512 is the preferred default for stronger security.

# Should I use encryption instead of hashing?
No. For passwords, hashing with salt and iterations is the correct approach. Encryption is reversible, which is not desirable for stored passwords.

# How do I verify a password during login?
Retrieve the stored Salt and Iterations for the user, compute the hash using the same function dbo.fn_PBKDF2_SHA512 with the entered password, and compare the result to the stored PasswordHash.

# How can I migrate existing passwords to salted hashes?
Add Salt and Iterations columns, then, on user login, re-hash their password with a new salt and store the results. For users who never log in again, you can prompt a password reset, which will re-hash on first successful login.

# What about peppering passwords?
Peppering adds an extra secret value to the password before hashing. It’s an optional defense-in-depth measure. If you implement a pepper, ensure it’s stored securely not in the database and rotated appropriately.

# How can I improve security beyond hashing in SQL Server 2012?
- Move heavy password-hashing logic to the application tier when feasible bcrypt/scrypt/Argon2 libraries
- Use per-user salts and a robust iteration count
- Consider peppering and periodic rotation
- Enforce strong password policies and user education
- Limit account lockout thresholds and monitor authentication attempts

# Is it okay to store hashes in a table alongside user data?
Yes, keeping PasswordHash, Salt, and Iterations in a dedicated Users table is standard practice. Ensure the table has strong access controls, and consider separating authentication-specific data from general user data where feasible.

# Can I reuse this approach for other security-sensitive tokens?
Yes, salt+hash with iterations is a solid pattern for storing derived keys or secrets that must be checked against user-provided input, not just passwords.

# How often should I review the hashing strategy?
Review your hashing approach whenever security guidance changes, when you onboard new security requirements, or when you expand to a larger user base. Reassess iteration counts and the possibility of moving to a more modern algorithm as hardware and standards evolve.



# Sources:



雙 esim 待機：你的手機如何同時處理兩個號碼？VPN 使用與 隱私保護 的完整指南


厦大vpn：学生党和教职工必备指南，高效访问教学资源、校园网加速、安全上网指南


猾猴vpn 全方位评测：功能对比、速度、隐私、价格、使用场景与常见问题


如何搭建自己的vpn：从家用到自托管的完整攻略，包含自建服务器、加密与隐私要点


Double vpn vs vpn: a comprehensive guide to multi-hop privacy, performance trade-offs, and practical use cases