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 (http://msdn.microsoft.com/en-us/library/ms177610(SQL.105).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 (http://msdn.microsoft.com/en-us/library/cc627408(SQL.105).aspx). As the documentation online suggests, this builtin is pretty much a T-SQL wrapper around the Crypto API (CAPI) function CryptGenRandom (http://msdn.microsoft.com/en-us/library/aa379942(VS.85).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))

Read more: SQL Server Security

Posted via email from .NET Info