Random Password Procedure in SQL Server with CRYPT_GEN_RANDOM

A random password procedure in SQL Server needs a cryptographic source and a guarantee of variety. It should also skip characters people misread. All of that fits in about thirty lines, and every promise can be tested.

Why NEWID Is the Wrong Source

Many password scripts pick characters with NEWID. The pattern is ABS(CHECKSUM(NEWID())) % n, which turns a unique identifier into a position in a list of characters. The output looks random. NEWID exists to make unique identifiers, though, and no documentation promises that its values are unpredictable.

CRYPT_GEN_RANDOM is built for secrets. It returns random bytes from the Windows cryptographic API, and its only argument is the number of bytes. This random password procedure reads four bytes for each character it picks. It casts them to bigint, so the number is never negative. A modulo then turns the number into a position in the character list.

A modulo adds a tiny bias, because four bytes don’t divide evenly by the list length. With the full list of 75 characters, lookalikes included, the bias is about one part in 57 million. For a password that doesn’t matter.

What the Procedure Does

The procedure takes a length from 8 to 100 and four switches: uppercase letters, lowercase letters, digits and symbols. A fifth switch, SkipLookalikes, is on by default. It removes the capital I and O, the lowercase l, and the digits 0 and 1. People misread those characters when they type a password from a printed page. The password comes back in an output parameter.

Variety comes first. Slot 1 takes a character from the first chosen set, slot 2 from the second, and so on. Every remaining slot takes a character from the whole pool. Then each character gets a second random number, and the password is assembled in the order of those numbers. Without that shuffle, the guaranteed characters would always sit at the front.

IF DB_ID(N'PasswordDemo') IS NULL CREATE DATABASE PasswordDemo;
GO
USE PasswordDemo;

CREATE OR ALTER PROCEDURE dbo.NewPassword
    @Length         int           = 16,
    @Upper          bit           = 1,
    @Lower          bit           = 1,
    @Digits         bit           = 1,
    @Symbols        bit           = 1,
    @SkipLookalikes bit           = 1,
    @Result         nvarchar(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF @Length NOT BETWEEN 8 AND 100
        THROW 50001, N'Length must be between 8 and 100.', 1;
    DECLARE @Sets TABLE (SetNo int IDENTITY(1,1) PRIMARY KEY, Chars nvarchar(40) NOT NULL);
    INSERT INTO @Sets (Chars)
    SELECT Chars
    FROM (VALUES
        (@Upper,   IIF(@SkipLookalikes = 1, N'ABCDEFGHJKLMNPQRSTUVWXYZ', N'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),
        (@Lower,   IIF(@SkipLookalikes = 1, N'abcdefghijkmnopqrstuvwxyz', N'abcdefghijklmnopqrstuvwxyz')),
        (@Digits,  IIF(@SkipLookalikes = 1, N'23456789', N'0123456789')),
        (@Symbols, N'!#$%&*+-=?@^_')
    ) AS s (Wanted, Chars)
    WHERE Wanted = 1;
    IF NOT EXISTS (SELECT 1 FROM @Sets)
        THROW 50002, N'Choose at least one kind of character.', 1;
    DECLARE @Pool nvarchar(200) = (SELECT STRING_AGG(Chars, N'') FROM @Sets);
    SELECT @Result = STRING_AGG(Pick.Ch, N'') WITHIN GROUP (ORDER BY rnd.Shuffle)
    FROM (SELECT TOP (@Length) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Slot
          FROM sys.all_objects) AS Slots
    LEFT JOIN @Sets AS st ON st.SetNo = Slots.Slot
    CROSS APPLY (SELECT COALESCE(st.Chars, @Pool) AS Source) AS src
    CROSS APPLY (SELECT CAST(CRYPT_GEN_RANDOM(4) AS bigint) AS Pos,
                        CAST(CRYPT_GEN_RANDOM(4) AS bigint) AS Shuffle) AS rnd
    CROSS APPLY (SELECT SUBSTRING(src.Source, 1 + (rnd.Pos % LEN(src.Source)), 1) AS Ch) AS Pick;
END;

The password comes back in an output parameter, not a result set. A caller can pass it straight into another statement, or hash it, without a temporary table. The table variable @Sets holds one row per chosen set, which is how the first slots find their set. Slots beyond the number of sets find no row and fall back to the whole pool.

The symbol list is a policy choice. Some systems reject certain punctuation, so edit the list in the one place where it appears. Length matters more than variety. Each extra character multiplies the number of possible passwords by the size of the pool.

Run It

Call the procedure with no options for a 16 character password with every kind of character. The second call asks for 24 characters and no symbols, which suits systems that reject punctuation.

DECLARE @p nvarchar(100);
EXEC dbo.NewPassword @Result = @p OUTPUT;
SELECT @p AS GeneratedPassword, LEN(@p) AS Characters;
EXEC dbo.NewPassword @Length = 24, @Symbols = 0, @Result = @p OUTPUT;
SELECT @p AS GeneratedPassword, LEN(@p) AS Characters;

The values change on every run, so yours will differ. The lengths won’t.

Check the Output, Not Only the Code

A password procedure fails quietly. A bug in the shuffle still returns text that looks random. So test the properties instead. The next script makes 1,000 passwords of 12 characters. It counts how many contain each kind of character, and how many contain a lookalike.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #Batch;
CREATE TABLE #Batch (N int IDENTITY(1,1) PRIMARY KEY, Pwd nvarchar(100) NOT NULL);
DECLARE @i int = 0, @p nvarchar(100);
WHILE @i < 1000
BEGIN
    EXEC dbo.NewPassword @Length = 12, @Result = @p OUTPUT;
    INSERT INTO #Batch (Pwd) VALUES (@p);
    SET @i += 1;
END;
SELECT COUNT(*) AS Passwords,
       COUNT(DISTINCT Pwd) AS DistinctOnes,
       MIN(LEN(Pwd)) AS ShortestLen,
       MAX(LEN(Pwd)) AS LongestLen,
       SUM(IIF(Pwd COLLATE Latin1_General_BIN LIKE N'%[A-Z]%', 1, 0)) AS WithUpper,
       SUM(IIF(Pwd COLLATE Latin1_General_BIN LIKE N'%[a-z]%', 1, 0)) AS WithLower,
       SUM(IIF(Pwd LIKE N'%[0-9]%', 1, 0)) AS WithDigit,
       SUM(IIF(Pwd COLLATE Latin1_General_BIN LIKE N'%[^A-Za-z0-9]%', 1, 0)) AS WithSymbol,
       SUM(IIF(Pwd COLLATE Latin1_General_BIN LIKE N'%[IOl01]%', 1, 0)) AS WithLookalike
FROM #Batch;

Every password holds every kind of character, none holds a lookalike, and all 1,000 are different. That is what the slots and the skip switch promise. The test proves those properties and nothing more. The randomness itself comes from CRYPT_GEN_RANDOM.

Bad Input

The procedure rejects a length outside 8 to 100. It also rejects a call that switches off every kind of character. Both stop with THROW, so the caller can’t ignore the failure.

DECLARE @p nvarchar(100);
EXEC dbo.NewPassword @Length = 4, @Result = @p OUTPUT;

DECLARE @p nvarchar(100);
EXEC dbo.NewPassword @Upper = 0, @Lower = 0, @Digits = 0, @Symbols = 0, @Result = @p OUTPUT;

Msg 50001, Level 16, State 1, Procedure dbo.NewPassword, Line 13
Length must be between 8 and 100.
Msg 50002, Level 16, State 1, Procedure dbo.NewPassword, Line 25
Choose at least one kind of character.

Using the Password

Treat the result as a secret. Show it once, and store only a hash, never the text. For a SQL Server login, pass it to CREATE LOGIN with the MUST_CHANGE option. The person then picks a new password at the first sign-in. MUST_CHANGE needs CHECK_EXPIRATION and CHECK_POLICY to be on. CREATE LOGIN needs the password as literal text, so build that statement with dynamic SQL.

You could argue that a database procedure is the wrong place to make passwords. The application has to carry the password to the person, and that trip is the exposed part. That’s fair. Generate the password in the application when you can. Use this random password procedure for scripts that create logins, where the password never leaves the session.

What to Remember

A random password procedure should use CRYPT_GEN_RANDOM for anything that protects access. Skip lookalike characters, because a password nobody can type becomes a support call. Guarantee the kinds of character inside the procedure, then shuffle, so a policy check never rejects a fresh password.

Test the output across many runs. When you finish, run the cleanup script to remove the demo database.

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

A random password is not a clever string, it is a draw from a source you can trust.

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