# How can I generate a cryptographically secure number in SQL Server?

I am currently using guid `NEWID()`

but I know it is not cryptographically secure.

Is there any better way of generating a cryptographically secure number in SQL Server?

### 2 Solutions collect form web for “How can I generate a cryptographically secure number in SQL Server?”

Interesting question ðŸ™‚

I think this will work: `CRYPT_GEN_RANDOM`

`CRYPT_GEN_RANDOM`

is documented to return a “cryptographic random number”.

It takes a length parameter between `1`

and `8000`

which is the length of the number to return in bytes.

For lengths <= 8 bytes. This can be cast to one of the SQL Server integer types straightforwardly.

```
+-----------+------------------+---------+
| Data type | Range | Storage |
+-----------+------------------+---------+
| bigint | -2^63 to 2^63-1 | 8 Bytes |
| int | -2^31 to 2^31-1 | 4 Bytes |
| smallint | -2^15 to 2^15-1 | 2 Bytes |
| tinyint | 0 to 255 | 1 Byte |
+-----------+------------------+---------+
```

Three of them are signed integers and one unsigned. The following will each use the full range of their respective datatypes.

```
SELECT
CAST(CRYPT_GEN_RANDOM(1) AS TINYINT),
CAST(CRYPT_GEN_RANDOM(2) AS SMALLINT),
CAST(CRYPT_GEN_RANDOM(4) AS INT),
CAST(CRYPT_GEN_RANDOM(8) AS BIGINT)
```

It is also possible to supply a shorter value than the datatype storage.

```
SELECT CAST(CRYPT_GEN_RANDOM(3) AS INT)
```

In this case only positive numbers can be returned. The sign bit will always be 0 as the last byte is treated as `0x00`

. The range of possible numbers that can be returned by the above is between `0`

and `POWER(2, 24) - 1`

inclusive.

Suppose the requirement is to generate some random number between `1 and 250`

.

One possible way of doing it would be

```
SELECT ( 1 + CAST(CRYPT_GEN_RANDOM(1) AS TINYINT) % 250) AS X
INTO #T
FROM master..spt_values V1, master..spt_values
```

However this method has a problem.

```
SELECT COUNT(*),X
FROM #T
GROUP BY X
ORDER BY X
```

The first ten rows of results are

```
+-------+----+
| Count | X |
+-------+----+
| 49437 | 1 |
| 49488 | 2 |
| 49659 | 3 |
| 49381 | 4 |
| 49430 | 5 |
| 49356 | 6 |
| 24914 | 7 |
| 24765 | 8 |
| 24513 | 9 |
| 24732 | 10 |
+-------+----+
```

Lower numbers (in this case `1 -6`

) are generated twice as regularly as the others because there are two possible inputs to the modulus function that can generate each of those results.

One possible solution would be to discard all numbers >= 250

```
UPDATE #T
SET X = CASE
WHEN Random >= 250 THEN NULL
ELSE ( 1 + Random % 250 )
END
FROM #T
CROSS APPLY (SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT)) CA (Random)
```

This appears to work on my machine but it is probably not guaranteed that SQL Server will only evaluate the function once across both references to `Random`

in the `CASE`

expression. Additionally it still leaves the problem of needing second and subsequent passes to fix up the `NULL`

rows where the random value was discarded.

Declaring a scalar UDF can solve both those issues.

```
/*Work around as can't call CRYPT_GEN_RANDOM from a UDF directly*/
CREATE VIEW dbo.CRYPT_GEN_RANDOM1
AS
SELECT CAST(CRYPT_GEN_RANDOM(1) AS TINYINT) AS Random
go
CREATE FUNCTION GET_CRYPT_GEN_RANDOM1()
RETURNS TINYINT
AS
BEGIN
DECLARE @Result TINYINT
WHILE (@Result IS NULL OR @Result >= 250)
/*Not initialised or result to be discarded*/
SELECT @Result = Random FROM dbo.CRYPT_GEN_RANDOM1
RETURN @Result
END
```

And then

```
UPDATE #T
SET X = dbo.GET_CRYPT_GEN_RANDOM1()
```

Alternatively and more straight forwardly one could simply use

```
CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) % 250
```

On the grounds that the range of `bigint`

is so huge that any bias will likely be insignificant. There are 73,786,976,294,838,208 ways that `1`

can be generated and 73,786,976,294,838,206 that `249`

can be from the query above.

If even that small possible bias is not permitted you could discard any values `NOT BETWEEN -9223372036854775750 AND 9223372036854775749`

as shown earlier.