Synchronous One-Time Password Token via T-SQL

While studying for the CISSP exam...which I passed last Saturday...I had this problem rankling around in my brain: how exactly time-based synchronization tokens, like the cryptocard devices we use at work for two factor authentication, might work. T-SQL isn't the best way to do this of course (although if you could carry around an entire instance of SQL Server inside a tiny crypto token that'd be pretty cool) but it's what I know. So after I kept failing to get something working while trying to use ENCRYPTBYPASSPHRASE and its decrypting cousin, I was out jogging in the rain and realized that a hash function is actually how you could do it. These type of functions depend on a combination of a secret key tied to that user, known only to the cryptocard itself and the authentication server, and a time based counter, which is how they avoid replay attacks (sniffing the wire and grabbing the credentials to reuse later). Instead of using the secret key to encrypt the timestamp or (somewhat oddly, but possible) the timestamp to encrypt the secret key...what you can do is just concatenate all that together, and hash it. The hash is a one way function so you can't reconstitute it back to its original text and find the secret key.

So imagine this code running on your token device when you push the button. The user and secret key are fixed in the device's firmware/ROM (before being distributed to the user). User probably not even necessary but it makes a nice addition. Then what happens is you take the user, key, and timestamp with seconds stripped off, and concatenate it into a long, rather unique string. Then hash it. Because nobody wants to enter a 160 bit hash in a window, we simplify it down by converting to int, making it positive, and taking the first 8 numbers off of that...and then output to the user on our little LCD panel.

/*This is the CryptoCard Side*/
DECLARE @UserID VARCHAR(10) = 'MyUserID';
DECLARE @SecretKey VARCHAR(100) = 'd4&g!29Betcetcetc';
DECLARE @DateHHMM VARCHAR(100) = DATEADD(MINUTE,DATEDIFF(MINUTE,0,GETDATE()),0);
DECLARE @TokenValue CHAR(8);

--Concatenate user ID, secret key, and date (floored to minute)
--Hash with SHA
--Convert to integer and ABS negative values
--Pull first 8 characters as token value
SELECT @TokenValue=RIGHT(ABS(CAST(HASHBYTES('SHA1',@UserID+@SecretKey+@DateHHMM)AS INT)),8);
SELECT @TokenValue;
GO
/*-----------------------------------*/

Then we, to authenticate, put in our username and password (basic single factor authentication), and are prompted to enter in this token (providing the multifactor, something-you-have + something-you-know authentication).

The server obviously validates the user password against the directory service or authentication server, and to authenticate the token it could do something like this. First grab the secret key for the specified user seeking authentication, make a small table variable for token values, and then insert the 'integerized' hash value for all possible values given the time (floored to the minute level), the user, and the secret key, within the last 5 minutes, and with some future minutes thrown in just in case clocks get off a bit. It's a fuzzy match, but so are biometrics, for that matter. If the provided token value is in that table variable, congrats, you've been authenticated. If not, sorry, no dice.

Now, nobody would use SQL Server to do this, nor should they, but to a DBA that has a hammer, everything looks like a nail, I suppose.
/*This is the Authentication Server Side*/
--User Provides User ID and token value
DECLARE @ProvidedUserID VARCHAR(10) = 'MyUserID';
DECLARE @ProvidedTokenValue CHAR(8) = '82650027';

--At this point, the system would retrieve the stored shared secret key into memory for that user from some sort of table or structure
--However to simplify this example, we're just going to hardcode it:
DECLARE @SecretKey VARCHAR(100) = 'd4&g!29Betcetcetc';

DECLARE @PossibleRecentValues TABLE (TokenValue CHAR(8));

--Compute possible values for last 5 minutes, plus two future to account for time sync issues
INSERT INTO @PossibleRecentValues (TokenValue) VALUES
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,2,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,1,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,GETDATE()),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-1,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-2,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-3,GETDATE())),0) AS VARCHAR(100)))AS INT)),8)),
(RIGHT(ABS(CAST(HASHBYTES('SHA1',@ProvidedUserID+@SecretKey+CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(minute,-4,GETDATE())),0) AS VARCHAR(100)))AS INT)),8));

IF EXISTS (SELECT 1 FROM @PossibleRecentValues WHERE TokenValue=@ProvidedTokenValue)
        PRINT 'User is authenticated';
ELSE
        PRINT 'Token expired or invalid';