Random Tokens in SQL Server: Generation, Storage and Expiry Checks

Random tokens in SQL Server need more than a generated value. Their stored state must define expiry, revocation and whether consumption already occurred.

Generate bytes for a disposable token

First a small setup. A temporary table holds the stored token state. A second one stands in for the token I would hand to the user.

DROP TABLE IF EXISTS #Tokens, #Issued;
CREATE TABLE #Tokens
 (Id int NOT NULL PRIMARY KEY,TokenHash binary(32) NOT NULL UNIQUE,
  ExpiresAtUtc datetime2(7) NOT NULL,UsedAtUtc datetime2(7) NULL,Revoked bit NOT NULL);
CREATE TABLE #Issued(Raw varbinary(32) NOT NULL);

CRYPT_GEN_RANDOM is a cryptographic random-byte generator. This example requests 32 bytes without supplying a seed. It checks the returned length before using the value. A small test does not certify entropy or prove uniqueness across all future requests.

DECLARE @Raw varbinary(8000)=CRYPT_GEN_RANDOM(32);
IF @Raw IS NULL OR DATALENGTH(@Raw)<>32
 THROW 51726,'Token generation failed.',1;
DECLARE @Digest binary(32)=HASHBYTES('SHA2_256',@Raw);
INSERT #Issued VALUES(@Raw);
INSERT #Tokens VALUES(1,@Digest,DATEADD(minute,15,'2025-01-01T12:00:00'),NULL,0);

CRYPT_GEN_RANDOM(32) returns 32 bytes. HASHBYTES with SHA2_256 returns a 32-byte digest. The demo table stores the digest instead of the raw token. This example never prints a token or digest in its results.

Keep a token separate from a password

A fresh random token and a human-selected password have different properties. A SHA2_256 digest here is not a password-storage recommendation. A digest also does not encrypt the original value for later recovery. Delivering a token securely remains the surrounding application’s responsibility.

Real token tables need an appropriate subject and purpose association. Access control, delivery, request limits and audit policy need separate design. This article tests a small state predicate. It does not claim to implement a complete account-recovery endpoint.

Validate length before narrowing the input

The incoming decoded bytes remain varbinary(max) until their length is checked. Reject NULL and anything other than 32 bytes. Narrowing an oversized value first can discard trailing bytes. That could turn a malformed value into a matching token.

DECLARE @Input varbinary(max)=(SELECT Raw FROM #Issued);
IF @Input IS NULL OR DATALENGTH(@Input)<>32
 THROW 51730,'Invalid token length.',1;

The oversized input is the valid token followed by one extra byte. It must not consume the stored token. Check transport decoding before this byte-level test in an application. Hexadecimal characters, binary bytes and encoded text lengths are different contracts.

Consume only an eligible stored row

This example treats the stored times as UTC by declared convention. datetime2 itself carries no time-zone label. Production code must obtain and use the intended current time consistently. The example instead uses a fixed clock so expiry checks are reproducible.

DECLARE @Input varbinary(max)=(SELECT Raw FROM #Issued);
DECLARE @NowUtc datetime2(7)='2025-01-01T12:00:00';
UPDATE #Tokens SET UsedAtUtc=@NowUtc
WHERE TokenHash=HASHBYTES('SHA2_256',@Input)
 AND UsedAtUtc IS NULL AND Revoked=0
 AND ExpiresAtUtc>@NowUtc;
SELECT @@ROWCOUNT AS ConsumedRows;

A row exactly at its expiry time is no longer eligible. A revoked or previously consumed row is also excluded. Inspect the affected row count immediately after the UPDATE. A preliminary SELECT followed by an unconditional write would omit the state predicate at consumption.

I would keep the business action and consumption in an appropriate transaction when both must succeed together. This example does not perform that business action. It also does not test competing sessions or isolation behavior. Reusing a token sequentially is narrower evidence than a concurrent-consumption test.

Test states without exposing the token

The block below puts the first token back to unused. Then it adds three more rows with known values: expired, expiring exactly now and revoked. It tries nine inputs one after another and prints the results. Raw tokens never appear in the output.

UPDATE #Tokens SET UsedAtUtc=NULL WHERE Id=1;
DECLARE @NowUtc datetime2(7)='2025-01-01T12:00:00';
DECLARE @Raw varbinary(32)=(SELECT Raw FROM #Issued);
INSERT #Tokens VALUES
 (2,HASHBYTES('SHA2_256',CONVERT(varbinary(32),REPLICATE('E',32))),DATEADD(second,-1,@NowUtc),NULL,0),
 (3,HASHBYTES('SHA2_256',CONVERT(varbinary(32),REPLICATE('B',32))),@NowUtc,NULL,0),
 (4,HASHBYTES('SHA2_256',CONVERT(varbinary(32),REPLICATE('R',32))),DATEADD(minute,15,@NowUtc),NULL,1);
DECLARE @Cases TABLE(Id int NOT NULL PRIMARY KEY,Label varchar(20) NOT NULL,Input varbinary(max) NULL);
INSERT @Cases VALUES(1,'OVERSIZED',@Raw+0x01),(2,'VALID',@Raw),(3,'REUSE',@Raw),
 (4,'EXPIRED',CONVERT(varbinary(32),REPLICATE('E',32))),
 (5,'AT_EXPIRY',CONVERT(varbinary(32),REPLICATE('B',32))),
 (6,'REVOKED',CONVERT(varbinary(32),REPLICATE('R',32))),
 (7,'UNMATCHED',CONVERT(varbinary(32),REPLICATE('U',32))),
 (8,'SHORT',0x0102),(9,'NULL',NULL);
DECLARE @Results TABLE(Id int NOT NULL PRIMARY KEY,Label varchar(20),InputBytes int NULL,ChangedRows int);
DECLARE @Case int=1,@Input varbinary(max),@Label varchar(20),@Changed int;
WHILE @Case<=9
BEGIN
 SELECT @Input=Input,@Label=Label FROM @Cases WHERE Id=@Case;
 SET @Changed=0;
 IF @Input IS NOT NULL AND DATALENGTH(@Input)=32
 BEGIN
  UPDATE #Tokens SET UsedAtUtc=@NowUtc
  WHERE TokenHash=HASHBYTES('SHA2_256',@Input)
   AND UsedAtUtc IS NULL AND Revoked=0 AND ExpiresAtUtc>@NowUtc;
  SET @Changed=@@ROWCOUNT;
 END;
 INSERT @Results VALUES(@Case,@Label,DATALENGTH(@Input),@Changed);
 SET @Case+=1;
END;
SELECT Id,Label,InputBytes,ChangedRows FROM @Results ORDER BY Id;
SELECT Id,ExpiresAtUtc,UsedAtUtc,Revoked FROM #Tokens ORDER BY Id;
SELECT DATALENGTH(@Raw) AS GeneratedBytes,DATALENGTH(HASHBYTES('SHA2_256',@Raw)) AS DigestBytes;
SELECT @@TRANCOUNT AS OpenTransactions;
DROP TABLE #Tokens, #Issued;

The cases cover valid consumption, reuse, expired, expiry-equal, revoked and unmatched values. Short, oversized and NULL inputs are also checked. Results show labels, byte lengths and affected counts.

The unique digest constraint rejects a duplicate stored digest. It does not establish a mathematically impossible collision. A production issuance process needs its own handling for a rejected insertion. Keep that concern separate from the tested expiry and consumption states.

On SQL Server 2025, only valid consumption changed one row across the nine cases. The oversized valid-prefix input ran first and left the eligible token untouched. The four stored rows show only the first token as used, with 32 generated bytes and 32 digest bytes.

Keep the token secret, and keep its stored state honest.

A random token is not complete authorization, it is a value whose purpose and stored state need checking.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.