T-SQL: Generate cryptographic-secure random numbers within a naively-compiled procedure

by Giffyguy   Last Updated October 10, 2019 20:26 PM

I need to generate salt values for a hash operation.

Generating these salts within the database is perfect for my situation - it would be much more complicated (and bug-prone/security risk) to generate these salt values elsewhere (e.g. client-side)

To make things easier for myself, I have written some procedures to generate random numbers of various types. (BIGINT, INT, etc.)

Obviously, these procedures could easily be modified to produce any size chunk of random bytes, but BIGINT is my focus for now.

The problem I'm running into, is that CRYPT_GEN_RANDOM can only be used in non-native contexts.
I have both native and non-native operations that need to use random numbers, and they all need to be secure.

My workaround right now, is to use NEWID in the native version (see code below), but this is known to be non-secure, and I'd like to avoid it if possible.

--non-native, cryptographic
CREATE PROCEDURE [dbo].[RandomBigInt]
    @result BIGINT OUTPUT
AS BEGIN
    SET @result = CAST ( CRYPT_GEN_RANDOM ( 8 ) AS BIGINT ) ;
END
GO

--native, non-cryptographic
CREATE PROCEDURE [dbo].[NativeRandomBigInt]
    @result BIGINT OUTPUT
WITH NATIVE_COMPILATION ,
     SCHEMABINDING
AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT   ,
                       LANGUAGE                    = N'English' )
    SET @result = CAST ( CAST ( NEWID ( ) AS BINARY ( 8 ) ) AS BIGINT ) ;
END
GO


Related Questions


Updated June 21, 2019 12:26 PM

Updated April 01, 2019 18:26 PM

Updated October 11, 2017 13:26 PM

Updated July 30, 2018 06:26 AM