SQL SERVER – Generating Fixed Width OTP Values with Cryptographic Randomness

A Random Function for a six-digit code needs suitable randomness and deliberate formatting. A reader wanted codes for new app registrations.

-- Fixed-width non-security demonstration; RAND is not an OTP security source.
SELECT RIGHT('000000' + CONVERT(varchar(6),
       CONVERT(int, FLOOR(RAND() * 1000000))), 6) AS DemoCode;

-- Cryptographic random bytes with rejection sampling for a uniform six-digit code.
DECLARE @Random bigint = CONVERT(bigint, CRYPT_GEN_RANDOM(4));
WHILE @Random >= 4294000000
    SET @Random = CONVERT(bigint, CRYPT_GEN_RANDOM(4));
SELECT RIGHT('000000' + CONVERT(varchar(6), @Random % 1000000), 6) AS RandomCode;

The first expression demonstrates six-character padding, including leading zeros. It is a formatting exercise, not authentication. My earlier LEFT(RAND() * 1000000000, 6) expression biased the displayed digits. Adding 999999 still didn’t reliably meet the requirement.

The second expression uses CRYPT_GEN_RANDOM for cryptographically secure bytes. It rejects the small remainder at the four-byte range boundary before reducing modulo one million. The output is a six-character string. Keep it as a string when leading zeros matter.

Bind a verification code to its user and purpose. Apply short expiry and attempt limits, invalidate successful codes, and protect stored verification data. Send it through the intended channel. Use an established authentication service or a reviewed implementation for the complete flow.

Reference: Cryptographic random bytes.

Related reading

• SQL SERVER – How to Generate Random Password? – Enhanced Version – Part 2
• SQL SERVER – How to Generate Random Password? – Enhanced Version
• How to Generate Random Password in SQL Server? – Interview Question of the Week #181
• SQL SERVER – Random Number Generator Script – SQL Query
• SQL SERVER – Using RAND() in User Defined Functions (UDF)
• SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function

A random string is not a complete authentication system, it is one input to a protected verification flow.

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