Generate cryptographic-secure random numbers within a natively-compiled procedure

Question

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.

Is there a way to generate cryptographic random numbers in native procedures?

--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

Answer 1

Any pseudo-random number is not really random - as you obviously know...

One idea might be, to combine something almost random (the pseudo-random NEWID()) with something really random (the actual timestamp). The first does not meet your needs, the second is - at least in some figures - predictable. But together this may be a solution:

DECLARE @dt DATETIME2(7)=SYSUTCDATETIME(); --9 Bytes in memory, the first byte (the precision) will be cut off later
DECLARE @guid UNIQUEIDENTIFIER=NEWID();    --pseudo random

--We can cast the time value to a 9-byte-binary, take the right-most 8 byte and treat it as a BIGINT 
DECLARE @dtCasted BIGINT = CAST(RIGHT(CAST(@dt AS BINARY(9)),8) AS BINARY(8));
--And we take the first 8 byte of the GUID
DECLARE @guidCasted BIGINT = CAST(@guid AS BINARY(8));

--The combination is XORed and returned as a BIGINT
SELECT @dtCasted ^ @guidCasted

--This is the same as a one-liner:

SELECT CAST(CAST(RIGHT(CAST(SYSUTCDATETIME() AS BINARY(9)),8) AS BINARY(8)) AS BIGINT) ^ CAST(NEWID() AS BINARY(8));