Generating a cryptographically secure random number in Microsoft SQL Server T-SQL

Among the applications for random number generators are gambling, statistical sampling, computer simulation and cryptography (the latter probably being the most significant application of all).

The diversity of applications has led to the development of several methods of generating random numbers.

A true random number generator measures some physical phenomena (such as thermal noise, or user interaction delays, etc.) and uses the measurement results to generate random numbers, while a pseudo-number generator (found in most programming environments) uses a deterministic algorithm to generate a sequence of numbers which appear random. This sequence is not truly random since it is determined by initializing what is known as a “seed.”  A seed is a random event, such as the current time of day in milliseconds, that is used to simulate a different sequence every time it is used.

A pseudo-random number generator cannot be regarded as a “true” or cryptographically secure random number generator since it’s output is predictable.

Microsoft SQL Server T-SQL language provides two methods for generating random numbers:

1. The RAND() function, which generates pseudo-random numbers
2. The CRYPT_GEN_RANDOM() function, which generates a cryptographically secure random number sequence of the specified length

To generate a sequence of 10 random bytes, CRYPT_GEN_RANDOM() can be used as follows:

declare @random_bytes varbinary(10)

set @random_bytes = CRYPT_GEN_RANDOM(10)

— @random_bytes now contains a sequence of 10 random bytes

However, the documentation does not explain how to generate a usable random number within the specified range. Let’s start with an arbitrary random number:

declare @random_int int
declare @random_bigint bigint

set @random_int = convert(int, CRYPT_GEN_RANDOM(4))
— @random_int now contains an arbitrary random integer

set @random_bigint = convert(bigint, CRYPT_GEN_RANDOM(8))
— @random_bigint now contains an arbitrary random bigint

To limit the range of the generated number, it is enough to calculate it’s modulus after dividing by the range limit + 1:

declare @random_int int
declare @random_bigint bigint

set @random_int = ABS(convert(int, CRYPT_GEN_RANDOM(4))) % 10
— @random_int now contains a random integer between 0 and 9

set @random_bigint = ABS(convert(bigint, CRYPT_GEN_RANDOM(8))) % 10
— @random_bigint now contains a random bigint between 0 and 9

Notice the use of ABS() function used to limit the result to positive numbers

Under the hood, CRYPT_GEN_RANDOM() uses the Windows API CryptGenRandom() function which is used by various security components in the system, which in turn allows for multiple processes to contribute to a system-wide seed, which is also combined with various system and user data such as the process ID and thread ID, the system clock, the system time, the system counter, memory status, free disk clusters and the hashed user environment block.