Generate a Random Password in SQL Server Using T-SQL

To generate a random password in T-SQL, draw cryptographic bytes, pick characters from a safe pool and shuffle. The old trick of sorting by NEWID() or looping over RAND() works for test data. It isn’t good enough for a value someone will sign in with.

What a Good Generator Needs

Four rules shape the procedure below. First, the random source is CRYPT_GEN_RANDOM, which returns bytes from the Windows cryptographic API. RAND is a pseudo-random generator, and a fixed seed repeats its sequence. Second, every result holds an upper case letter, a lower case letter, a digit and a symbol. Password policies commonly demand all four.

Third, the pool leaves out look-alike characters: I, O, l, 0 and 1. People read these passwords aloud and type them by hand, and those five look alike in many fonts. Fourth, the characters are shuffled at the end, so the guaranteed ones don’t always sit in the first four positions.

A Procedure to Generate a Random Password

It needs SQL Server 2017 or later, because STRING_AGG arrived in that version. The first block creates a demo database and the procedure. Run it twice if you like, since it checks before it creates.

IF DB_ID(N'RandomPasswordDemo') IS NULL CREATE DATABASE RandomPasswordDemo;
GO
USE RandomPasswordDemo;
GO
CREATE OR ALTER PROCEDURE dbo.GeneratePassword
    @Length     int = 16,
    @UseSymbols bit = 1,
    @Result     varchar(128) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF @Length NOT BETWEEN 8 AND 128
        THROW 50001, N'Length must be between 8 and 128.', 1;
    DECLARE @upper   varchar(30) = 'ABCDEFGHJKLMNPQRSTUVWXYZ';
    DECLARE @lower   varchar(30) = 'abcdefghijkmnopqrstuvwxyz';
    DECLARE @digits  varchar(10) = '23456789';
    DECLARE @symbols varchar(20) = CASE WHEN @UseSymbols = 1 THEN '!#$%&*+-=?@^_' ELSE '' END;
    DECLARE @pool    varchar(100) = @upper + @lower + @digits + @symbols;
    WITH n AS (
        SELECT TOP (@Length) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS pos
        FROM sys.all_columns
    ), picked AS (
        SELECT n.pos,
               CASE WHEN n.pos = 1 THEN SUBSTRING(@upper, 1 + r1.v % LEN(@upper), 1)
                    WHEN n.pos = 2 THEN SUBSTRING(@lower, 1 + r1.v % LEN(@lower), 1)
                    WHEN n.pos = 3 THEN SUBSTRING(@digits, 1 + r1.v % LEN(@digits), 1)
                    WHEN n.pos = 4 AND @UseSymbols = 1 THEN SUBSTRING(@symbols, 1 + r1.v % LEN(@symbols), 1)
                    ELSE SUBSTRING(@pool, 1 + r1.v % LEN(@pool), 1) END AS ch,
               r2.v AS shuffle
        FROM n
        CROSS APPLY (SELECT CONVERT(bigint, CRYPT_GEN_RANDOM(4)) AS v) AS r1
        CROSS APPLY (SELECT CONVERT(bigint, CRYPT_GEN_RANDOM(4)) AS v) AS r2
    )
    SELECT @Result = STRING_AGG(ch, '') WITHIN GROUP (ORDER BY shuffle)
    FROM picked;
END;

The n part makes one row per position. For each row, two separate CRYPT_GEN_RANDOM calls give a pick value and a shuffle value. Positions 1 to 4 take one character from each type, and the rest come from the full pool. STRING_AGG ... WITHIN GROUP (ORDER BY shuffle) joins the characters in random order.

The bytes are converted to bigint on purpose. Four bytes make a number between 0 and 4,294,967,295, so the % step never sees a negative value. The uneven spread from the modulo is far below one part in a million for this pool. That is small enough to ignore.

You can edit the character lists to fit your own policy. Keep every list non-empty, and keep the whole pool under 100 characters, because @pool is declared as varchar(100). The symbol list is short on purpose. Quotes, semicolons, slashes and spaces cause trouble in connection strings and shell commands, so they stay out. Characters such as =, &, % and # can cause trouble in connection strings and web addresses too. Check the list against the place where the value will be pasted.

Copy the code block, not the rendered page, so that characters such as < and > arrive unchanged.

Now call it to generate a random password. The first call asks for 16 characters. The second asks for 12 characters and no symbols, for systems that reject them.

DECLARE @p varchar(128);
EXEC dbo.GeneratePassword @Length = 16, @Result = @p OUTPUT;
SELECT @p AS NewValue, LEN(@p) AS Length;
EXEC dbo.GeneratePassword @Length = 12, @UseSymbols = 0, @Result = @p OUTPUT;
SELECT @p AS NewValue, LEN(@p) AS Length;

The values change on every run, so yours will differ. Only the lengths stay fixed.

Proving It on 1,000 Passwords

A few samples prove nothing about a random generator. This script calls the procedure 1,000 times, stores the results in a table variable and counts what it finds. The class checks use a binary collation, so [A-Z] means upper case only.

SET NOCOUNT ON;
DECLARE @t TABLE (pw varchar(128));
DECLARE @i int = 1, @p varchar(128);
WHILE @i <= 1000
BEGIN
    EXEC dbo.GeneratePassword @Length = 16, @Result = @p OUTPUT;
    INSERT @t (pw) VALUES (@p);
    SET @i += 1;
END;
SELECT COUNT(*) AS Generated, COUNT(DISTINCT pw) AS DistinctValues,
       SUM(CASE WHEN LEN(pw) = 16 THEN 1 ELSE 0 END) AS Len16,
       SUM(CASE WHEN pw COLLATE Latin1_General_100_BIN2 LIKE '%[A-Z]%' THEN 1 ELSE 0 END) AS HasUpper,
       SUM(CASE WHEN pw COLLATE Latin1_General_100_BIN2 LIKE '%[a-z]%' THEN 1 ELSE 0 END) AS HasLower,
       SUM(CASE WHEN pw LIKE '%[0-9]%' THEN 1 ELSE 0 END) AS HasDigit,
       SUM(CASE WHEN pw LIKE '%[!#$%&*+=?@^_-]%' THEN 1 ELSE 0 END) AS HasSymbol,
       SUM(CASE WHEN pw COLLATE Latin1_General_100_BIN2 LIKE '%[IOl01]%' THEN 1 ELSE 0 END) AS Ambiguous
FROM @t;

All 1,000 values are different, every one has all four types, and none contains a look-alike character. These numbers repeat on every run, even though the passwords don’t.

Why Not ABS(CHECKSUM(NEWID()))

A common pattern turns CHECKSUM(NEWID()) into a positive number with ABS. It works until the checksum lands on the smallest integer, -2,147,483,648. That value has no positive twin in the int range, so ABS raises an overflow.

SELECT ABS(CONVERT(int, -2147483648));

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

That is one value out of the 4.3 billion the type can hold. A single call almost never hits it, but a job that builds millions of values can. The bigint conversion in the procedure removes the problem.

The procedure also rejects a length below 8, and the error tells the caller why.

Msg 50001, Level 16, State 1, Procedure dbo.GeneratePassword, Line 9
Length must be between 8 and 128.

Does It Meet the Windows Policy

A SQL login created with CHECK_POLICY = ON follows the Windows password policy of the machine. A typical policy asks for a minimum length and characters from at least three of four types. A 16 character value with all four types satisfies that. A domain policy can ask for more, so read yours before you pick the length.

The length also matters more than the symbols. Every extra character multiplies the number of possible values by the size of the pool, which here is 70. A 20 character value is a safer default than a 12 character value with more symbols.

Using the Value Safely

You could argue that a password manager does this job better. For a person, it does. This procedure is for scripts that must generate a random password as an initial value. A new SQL login or a service account is a typical case.

The generated text travels in the query, the result set and any trace that captures them. So treat it as a one-time value. Create the login with MUST_CHANGE so the person sets a new password at the first sign in. Don’t email the value and don’t store it in a table.

What to Remember

Use CRYPT_GEN_RANDOM, guarantee each character type, and drop the characters people confuse. To generate a random password you can trust, test the generator on a large batch, not on three samples. When you finish, run the cleanup script.

USE master;
GO
IF DB_ID(N'RandomPasswordDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RandomPasswordDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RandomPasswordDemo;
END;

A random password is not a long string, it is a value nobody could have guessed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.