rand vs. crypt_gen_random

  Many applications need to generate random data, and in order to help in this task they typically rely on pseudorandom number generators (PRNG). Typical PRNGs are deterministic in nature and therefore they are not cryptographically suitable, this is the case of the built-in RAND (https://msdn.microsoft.com/en-us/library/ms177610.aspx) in SQL Server.

 

   If your T-SQL application needs to use a cryptographically secure PRNG (CSPRNG), an alternative is to use CRYPT_GEN_RANDOM (https://msdn.microsoft.com/en-us/library/cc627408.aspx). As the documentation online suggests, this builtin is pretty much a T-SQL wrapper around the Crypto API (CAPI) function CryptGenRandom (https://msdn.microsoft.com/en-us/library/aa379942.aspx) using the Microsoft CSP.

 

  Since CRYPT_GEN_RANDOM return value is a varbinary it can easily be consumed as such (binary data) or converted to any T-SQL data type compatible with such conversion, such as int and bigint, for example:

SELECT crypt_gen_random(4)

SELECT convert( int, crypt_gen_random(4)) SELECT convert( bigint, crypt_gen_random(8))

 

  For more detailed information on how the CryptGenRandom works, please see the remarks section on the CryptGenRandom documentation online at: https://msdn.microsoft.com/en-us/library/aa379942(VS.85).aspx.

 

   NOTE: A quick word of warning when converting to some data types such as varchar or nvarchar, the output may contain invalid (or unprintable) characters.