SQL SERVER – Generating Complex Passwords with T-SQL

During a recent Comprehensive Database Performance Health Check, a popular service I provide for my clients, I was tasked with an exciting challenge. The client needed a robust method for generating complex passwords directly within their SQL Server database. The requirement was to create a password comprising a mix of uppercase and lowercase English letters, digits, and special characters.

In addressing this task, I developed a T-SQL script that meets the client’s requirements and executes efficiently. This post will discuss the script and offer an explanation of how it works.

Generating Complex Passwords

Here’s the T-SQL script I developed:

DECLARE @upperLetters VARCHAR(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
        @lowerLetters VARCHAR(26) = 'abcdefghijklmnopqrstuvwxyz',
        @digits VARCHAR(10) = '0123456789',
        @specials VARCHAR(32) = '-!"#$%&()*+,./:;?@[]^_`{|}~+<=>',
        @password VARCHAR(MAX) = ''

;WITH Tally (n) AS (
    SELECT TOP (16) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM master..spt_values
)
SELECT @password = @password + CHAR FROM (
    SELECT TOP (12) CHAR = SUBSTRING(@upperLetters + @lowerLetters + @digits, CAST(RAND(CHECKSUM(NEWID()))*(26+26+10) AS INT)+1, 1)
    FROM Tally
    UNION ALL
    SELECT TOP (4) CHAR = 
SUBSTRING(@specials, CAST(RAND(CHECKSUM(NEWID()))*32 AS INT)+1, 1)
    FROM Tally
) t

SELECT @password AS GeneratedPassword

How It Works

The script begins by declaring four variables (@upperLetters, @lowerLetters, @digits, @specials), each representing a category of characters that will be used in the password.

A tally table is then constructed using a common table expression (CTE). This table essentially provides a row of numbers from 1 to 16, which will be used to loop through the character selection process 16 times (the required length of our password).

The script then picks 12 alphanumeric characters at random. This randomness is achieved using the RAND(CHECKSUM(NEWID())) function, which generates a new random float number between 0 and 1 for each row in the tally table. This random number is then scaled up to the total length of the alphanumeric characters string and rounded down to an integer. This integer is used to pick one character from the string.

The same process is then repeated to select 4 special characters.

The UNION ALL operator combines these 16 characters into one result set, which is then concatenated into the @password variable.

Finally, @password is returned as the output of the script.

Here are a few examples:

8kZv8obIAcUv&#<$
JkmSvHpNkMBw^/_
o1G6JxHTe1Hv/)+[

Conclusion

In a world where data security is paramount and complex passwords are a necessity, having the ability to generate such passwords directly within a SQL Server environment can be a game-changer. The T-SQL script discussed in this post achieves this with efficiency and speed.

However, it is crucial to remember that the security of your passwords is of utmost importance. This T-SQL script is a simple example and may not be suitable for generating passwords in a secure production environment. Always consider using a reliable password generation tool or library that has been thoroughly tested for security vulnerabilities.

You can always reach out to me on LinkedIn.

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