SQL Server
SQL Server
TOTP Algorithm: Time-Based One-Time Password Algorithm
See more Encryption Examples
Demonstrates how to generate an time-based one-time password (TOTP) as specified in RFC 6238. This is the algorithm used by Google Authenticator.Note: This example requires Chilkat v9.5.0.77 or greater.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Duplicate the following results from RFC 6238
-- TOTP Algorithm: Test Values
--
-- +-------------+--------------+------------------+----------+--------+
-- | Time (sec) | UTC Time | Value of T (hex) | TOTP | Mode |
-- +-------------+--------------+------------------+----------+--------+
-- | 59 | 1970-01-01 | 0000000000000001 | 94287082 | SHA1 |
-- | | 00:00:59 | | | |
-- | 59 | 1970-01-01 | 0000000000000001 | 46119246 | SHA256 |
-- | | 00:00:59 | | | |
-- | 59 | 1970-01-01 | 0000000000000001 | 90693936 | SHA512 |
-- | | 00:00:59 | | | |
-- | 1111111109 | 2005-03-18 | 00000000023523EC | 07081804 | SHA1 |
-- | | 01:58:29 | | | |
-- | 1111111109 | 2005-03-18 | 00000000023523EC | 68084774 | SHA256 |
-- | | 01:58:29 | | | |
-- | 1111111109 | 2005-03-18 | 00000000023523EC | 25091201 | SHA512 |
-- | | 01:58:29 | | | |
-- | 1111111111 | 2005-03-18 | 00000000023523ED | 14050471 | SHA1 |
-- | | 01:58:31 | | | |
-- | 1111111111 | 2005-03-18 | 00000000023523ED | 67062674 | SHA256 |
-- | | 01:58:31 | | | |
-- | 1111111111 | 2005-03-18 | 00000000023523ED | 99943326 | SHA512 |
-- | | 01:58:31 | | | |
-- | 1234567890 | 2009-02-13 | 000000000273EF07 | 89005924 | SHA1 |
-- | | 23:31:30 | | | |
-- | 1234567890 | 2009-02-13 | 000000000273EF07 | 91819424 | SHA256 |
-- | | 23:31:30 | | | |
-- | 1234567890 | 2009-02-13 | 000000000273EF07 | 93441116 | SHA512 |
-- | | 23:31:30 | | | |
-- | 2000000000 | 2033-05-18 | 0000000003F940AA | 69279037 | SHA1 |
-- | | 03:33:20 | | | |
-- | 2000000000 | 2033-05-18 | 0000000003F940AA | 90698825 | SHA256 |
-- | | 03:33:20 | | | |
-- | 2000000000 | 2033-05-18 | 0000000003F940AA | 38618901 | SHA512 |
-- | | 03:33:20 | | | |
-- | 20000000000 | 2603-10-11 | 0000000027BC86AA | 65353130 | SHA1 |
-- | | 11:33:20 | | | |
-- | 20000000000 | 2603-10-11 | 0000000027BC86AA | 77737706 | SHA256 |
-- | | 11:33:20 | | | |
-- | 20000000000 | 2603-10-11 | 0000000027BC86AA | 47863826 | SHA512 |
-- | | 11:33:20 | | | |
-- +-------------+--------------+------------------+----------+--------+
-- Seed for HMAC-SHA1
DECLARE @seed nvarchar(4000)
SELECT @seed = '3132333435363738393031323334353637383930'
-- Seed for HMAC-SHA256 - 32 bytes
DECLARE @seed32 nvarchar(4000)
SELECT @seed32 = '3132333435363738393031323334353637383930313233343536373839303132'
-- Seed for HMAC-SHA512 - 64 bytes
DECLARE @seed64 nvarchar(4000)
SELECT @seed64 = '31323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334'
-- See the online reference documentation for an explanation of arguments.
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '59', 30, 8, -1, 'sha1'
PRINT 'TOTP(59,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '59', 30, 8, -1, 'sha256'
PRINT 'TOTP(59,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '59', 30, 8, -1, 'sha512'
PRINT 'TOTP(59,sha512) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '1111111109', 30, 8, -1, 'sha1'
PRINT 'TOTP(1111111109,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '1111111109', 30, 8, -1, 'sha256'
PRINT 'TOTP(1111111109,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '1111111109', 30, 8, -1, 'sha512'
PRINT 'TOTP(1111111109,sha512) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '1111111111', 30, 8, -1, 'sha1'
PRINT 'TOTP(1111111111,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '1111111111', 30, 8, -1, 'sha256'
PRINT 'TOTP(1111111111,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '1111111111', 30, 8, -1, 'sha512'
PRINT 'TOTP(1111111111,sha512) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '1234567890', 30, 8, -1, 'sha1'
PRINT 'TOTP(1234567890,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '1234567890', 30, 8, -1, 'sha256'
PRINT 'TOTP(1234567890,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '1234567890', 30, 8, -1, 'sha512'
PRINT 'TOTP(1234567890,sha512) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '2000000000', 30, 8, -1, 'sha1'
PRINT 'TOTP(2000000000,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '2000000000', 30, 8, -1, 'sha256'
PRINT 'TOTP(2000000000,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '2000000000', 30, 8, -1, 'sha512'
PRINT 'TOTP(2000000000,sha512) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '20000000000', 30, 8, -1, 'sha1'
PRINT 'TOTP(20000000000,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '20000000000', 30, 8, -1, 'sha256'
PRINT 'TOTP(20000000000,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '20000000000', 30, 8, -1, 'sha512'
PRINT 'TOTP(20000000000,sha512) ' + @sTmp0
-- Note: To compute the TOTP using the current system date/time, pass an empty string instead of an explicit time.
-- For example:
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed, 'hex', '0', '', 30, 8, -1, 'sha1'
PRINT 'TOTP(CurrentSystemTime,sha1) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed32, 'hex', '0', '', 30, 8, -1, 'sha256'
PRINT 'TOTP(CurrentSystemTime,sha256) ' + @sTmp0
EXEC sp_OAMethod @crypt, 'Totp', @sTmp0 OUT, @seed64, 'hex', '0', '', 30, 8, -1, 'sha512'
PRINT 'TOTP(CurrentSystemTime,sha512) ' + @sTmp0
EXEC @hr = sp_OADestroy @crypt
END
GO