How to generate a RNGCryptoServiceProvider-like number in TSQL

Question

Does anyone know of a way to generate a RNGCryptoServiceProvider-like random number in T-SQL without having to register any .NET Assemblies?

Answer 1

SELECT CHECKSUM(NEWID()) gives you a 32 signed integer based on GUIDs. You can use this as base: it's pretty much the best SQL way

You can use it to seed RND (because RND is the same for all rows in a single select.

SELECT RND(CHECKSUM(NEWID()))

A signed 64 bit integer:

SELECT CAST(CHECKSUM(NEWID()) as bigint) * CAST(CHECKSUM(NEWID()) as bigint)

With some playing around you could cast and/or concatenate to get binary("multiples of 4") etc to build up a byte array... I've not used the RNG thingy though to emulate directly

Answer 2

If you have to generate cryptographycally secure random numbers (like salts for hashing) then you should use the CLR functions. Checksum, guids and the like are not secure for crypto operations.

The easiest solution is to use a CLR scalar function that invokes RNGCryptoServiceProvider.

Note that many of the SQL Server crpto functions like EncryptByKey are already salting the input using cryptographically secure generated salts (except for EncryptByKey on RC4 algorithm, that is broken in SQL).