(SQL Server) TOTP Algorithm: Time-Based One-Time Password Algorithm

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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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