How to create RNGCryptoServiceProvider number in TSQL
SELECT CHECKSUM(NEWID())
gives you a 32 signed integer based on GUIDs. You can use this as a base: this is pretty much the best SQL way
You can use it for the RAND seed (because RAND is the same for all rows in one selection.
SELECT RAND(CHECKSUM(NEWID()))
Signed 64-bit integer:
SELECT CAST(CHECKSUM(NEWID()) as bigint) * CAST(CHECKSUM(NEWID()) as bigint)
With some games around you could dump and / or concatenate to get binary ("multiples of 4") etc. to create a byte array ... I didn't use RAND, although to emulate directly
source to share
If you need to generate cryptographically secure random numbers (like hashing salts), you must use CLR functions. Checksum, hints, etc. Are not secure for cryptographic transactions.
The simplest solution is to use a CLR scalar function that calls RNGCryptoServiceProvider.
Note that many of the SQL Server crpto functions, such as EncryptByKey , already push input using cryptographically secure generated salts (with the exception of the EncryptByKey on the RC4 algorithm, which is split into SQL).
source to share