SQL SERVER – Generating Secure Passwords

With data breaches on the rise, it’s crucial to implement strong password practices in SQL Server. Using weak default passwords leaves databases vulnerable to attacks. In this post, we’ll explore industry guidelines for complex passwords and implement them in an SQL Server stored procedure to generate secure passwords.

Secure Password Complexity Best Practices

Professional organizations like NIST provide research-backed password complexity recommendations. Here are some key tips:

• Length – Longer passwords resist brute force attacks. At least 12 characters are ideal.
• Character Variety – Use upper/lowercase letters, numbers, and symbols. Different characters exponentially expand the search space.
• Unpredictability – Randomness prevents password guessing. Avoid patterns, keyboard walks, and repeating characters.
• No Personal Info – Don’t use dictionary words, names, and dates, which are easy to guess.

Following these principles results in passwords that can withstand cracking attempts. Now let’s look at implementing these practices in SQL Server.

Stored Procedure for Password Generation

Here is a stored procedure that generates random 12-character passwords adhering to the above guidelines:

CREATE PROCEDURE dbo.GenerateComplexPassword
AS
BEGIN
-- Declare
DECLARE @password VARCHAR(12);
DECLARE @lowercase CHAR(26);
DECLARE @uppercase CHAR(26);
DECLARE @numbers CHAR(10);
DECLARE @special CHAR(6);
-- Set
SET @lowercase = 'abcdefghijklmnopqrstuvwxyz';
SET @uppercase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @numbers = '0123456789';
SET @special = '!@#$%^';
-- Final Set
SET @password = LOWER(SUBSTRING(@lowercase, CAST(RAND()*26 + 1 AS INT), 1)) +
UPPER(SUBSTRING(@uppercase, CAST(RAND()*26 + 1 AS INT), 1)) +
SUBSTRING(@numbers, CAST(RAND()*10 + 1 AS INT), 1) +
SUBSTRING(@special, CAST(RAND()*6 + 1 AS INT), 1) +
LOWER(SUBSTRING(@lowercase, CAST(RAND()*26 + 1 AS INT), 1)) +
UPPER(SUBSTRING(@uppercase, CAST(RAND()*26 + 1 AS INT), 1)) +
SUBSTRING(@numbers, CAST(RAND()*10 + 1 AS INT), 1) +
SUBSTRING(@special, CAST(RAND()*6 + 1 AS INT), 1) +
LOWER(SUBSTRING(@lowercase, CAST(RAND()*26 + 1 AS INT), 1)) +
UPPER(SUBSTRING(@uppercase, CAST(RAND()*26 + 1 AS INT), 1)) +
SUBSTRING(@numbers, CAST(RAND()*10 + 1 AS INT), 1);
-- Final Select
SELECT @password;
END
GO

It uses RAND() to randomly select one character from each defined set in a loop, generating a 12-character password.

Using a stored procedure allows RAND() for randomness. The password length, complexity, and character sets can be customized.

Once created, the procedure can be called anywhere a random password is needed:

EXEC dbo.GenerateComplexPassword

Securely Storing Passwords

Plaintext passwords should never be stored. After generation, passwords should be hashed using SHA2 or bcrypt before inserting them into login tables.

Hashing one-way encrypts passwords, preventing theft even with DB access. Validate logins by comparing the input password’s hash versus the stored hash.

Conclusion – Secure Passwords

Building password generation into SQL Server using stored procedures and hashing provides a secure solution that scales across the enterprise.

The techniques covered here allow you to programmatically implement complex random password best practices based on your specific needs.

What methods do you use for password management in SQL Server? Share your thoughts and experiences!

You may follow me here: X (twitter)

Reference: Pinal Dave (https://blog.sqlauthority.com)