CRYPT_GEN_RANDOM: Generating Cryptographically Secure Random Numbers in T-SQL

Introduction

A few days ago I wrote about Pseudo Random Number Generators (PRNG) and what it means for a PRNG to be cryptographically secure (CSPRNG). Today I would like to show you a way to generate cryptographically secure random numbers in T-SQL.

Cryptographically Secure Random Numbers in T-SQL

The built-in T-SQL function CRYPT_GEN_RANdOM() is a wrapper of the CryptGenRandom function in Window's Crypto-API. It takes two parameters. The first is the length in bytes of the value you want to generate. The second parameter is optional and can be used to provide additional entropy. Let us look at an example:

The output is an eight byte varbinary value:

The data type of the return value is VARBINARY(8000)[/t]] and the maximum number of bytes that can be returned is therefore 8000.

The second optional parameter is also a [tt]VARBINARY(8000) value. It will be used as a seed. However, this is not a seed in the usual sense of a PRNG. Instead, CRYPT_GEN_RANDOM() combines the values in that second parameter with other data before using it. That means that calling the function twice with the same seed will still return two different values.

The PRNG used for CRYPT_GEN_RANDOM() is actually a good source of random numbers for non-cryptographic use cases too. You just have to cast the returned value to the desired data type. CRYPT_GEN_RANDOM() has a particular advantage over the RAND() function. If called within a multi-row SELECT or UPDATE statement, RAND() returns the same value for each row. CRYPT_GEN_RANDOM() on the other hand gets re-invoked for every row. To see that in action let us look at this example code:

The example selects rows from sys.columns, but which table is used is not actually relevant. For each row both RAND() and CRYPT_GEN_RANDOM() are invoked. The output looks like this:

You can clearly see that the same value is returned by RAND() for each row, while CRYPT_GEN_RANDOM() produces different values.

Security of CRYPT_GEN_RANDOM()

CRYPT_GEN_RANDOM() directly calls CryptGenRandom of the Windows Crypt-API. This API is widely used in many applications and in general considered well implemented. However, the details of the implementation of this CSPRNG are not published. That poses a significant risk. Most cryptographic algorithms are considered secure exactly because they have been reviewed and under attack of many security specialists. Every time an algorithm is kept a secret, that review cannot happen. Often that leads to vulnerabilities and CryptGenRandom is no exception. In 2007, Leo Dorrendorf reverse engineered the CryptGenRandom function for his master thesis on Cryptanalysis of the Windows Random Number Generator. He discovered several vulnerabilities. Microsoft later that year acknowledged the problems and changed the algorithm in later windows versions. However, it is still a secret algorithm.

One workaround that Leo suggested was, to always generate more randomness than required and discard the rest. That is the reason why I used CRYPT_GEN_RANDOM(8000) in the previous example to generate a random integer where four bytes would have been enough. In high security situations however I can only recommend to use a different provider for your CSPRN generation, a provider that is using a well-known and tested algorithm.

Summary

The built-in T-SQL function CRYPT_GEN_RANDOM() provides an easy way to generate cryptographically secure random numbers. While there is a security risk involved as the underlying algorithm is not published, it is far better than any other random data source readily available from T-SQL. If for nothing else, it is a better PRNG than RAND() and you should consider using it in non-secure contexts as well.