SQL Server
SQL Server
Signing HTTP Messages
See more RSA Examples
Demonstrates how to sign HTTP messages per draft-cavage-http-signatures-10Chilkat 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)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @bCrlf int
SELECT @bCrlf = 1
DECLARE @sbPublicKeyPem int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPublicKeyPem OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, '-----BEGIN PUBLIC KEY-----', @bCrlf
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDCFENGw33yGihy92pDjZQhl0C3', @bCrlf
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, '6rPJj+CvfSC8+q28hxA161QFNUd13wuCTUcq0Qd2qsBe/2hFyc2DCJJg0h1L78+6', @bCrlf
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'Z4UMR7EOcpfdUE9Hf3m/hs+FUR45uBJeDK1HSFHD8bHKD6kv8FPGfJTotc+2xjJw', @bCrlf
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, 'oYi+1hqp1fIekaxsyQIDAQAB', @bCrlf
EXEC sp_OAMethod @sbPublicKeyPem, 'AppendLine', @success OUT, '-----END PUBLIC KEY-----', @bCrlf
DECLARE @pubKey int
EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT
EXEC sp_OAMethod @sbPublicKeyPem, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @sTmp0
DECLARE @sbPrivateKeyPem int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPrivateKeyPem OUT
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '-----BEGIN RSA PRIVATE KEY-----', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'MIICXgIBAAKBgQDCFENGw33yGihy92pDjZQhl0C36rPJj+CvfSC8+q28hxA161QF', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'NUd13wuCTUcq0Qd2qsBe/2hFyc2DCJJg0h1L78+6Z4UMR7EOcpfdUE9Hf3m/hs+F', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'UR45uBJeDK1HSFHD8bHKD6kv8FPGfJTotc+2xjJwoYi+1hqp1fIekaxsyQIDAQAB', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'AoGBAJR8ZkCUvx5kzv+utdl7T5MnordT1TvoXXJGXK7ZZ+UuvMNUCdN2QPc4sBiA', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'QWvLw1cSKt5DsKZ8UETpYPy8pPYnnDEz2dDYiaew9+xEpubyeW2oH4Zx71wqBtOK', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'kqwrXa/pzdpiucRRjk6vE6YY7EBBs/g7uanVpGibOVAEsqH1AkEA7DkjVH28WDUg', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'f1nqvfn2Kj6CT7nIcE3jGJsZZ7zlZmBmHFDONMLUrXR/Zm3pR5m0tCmBqa5RK95u', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '412jt1dPIwJBANJT3v8pnkth48bQo/fKel6uEYyboRtA5/uHuHkZ6FQF7OUkGogc', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'mSJluOdc5t6hI1VsLn0QZEjQZMEOWr+wKSMCQQCC4kXJEsHAve77oP6HtG/IiEn7', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'kpyUXRNvFsDE0czpJJBvL/aRFUJxuRK91jhjC68sA7NsKMGg5OXb5I5Jj36xAkEA', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'gIT7aFOYBFwGgQAQkWNKLvySgKbAZRTeLBacpHMuQdl1DfdntvAyqpAZ0lY0RKmW', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, 'G6aFKaqQfOXKCyWoUiVknQJAXrlgySFci/2ueKlIE1QqIiLSZ8V8OlpFLRnb1pzI', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '7U1yQXnTAEFYM560yJlzUpOb1V4cScGd365tiSMvxLOvTA==', @bCrlf
EXEC sp_OAMethod @sbPrivateKeyPem, 'AppendLine', @success OUT, '-----END RSA PRIVATE KEY-----', @bCrlf
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @sbPrivateKeyPem, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @privKey, 'LoadPem', @success OUT, @sTmp0
-- All examples use this request:
--
-- POST /foo?param=value&pet=dog HTTP/1.1
-- Host: example.com
-- Date: Sun, 05 Jan 2014 21:31:40 GMT
-- Content-Type: application/json
-- Digest: SHA-256=X48E9qOokqqrvdts8nOJRJN3OWDUoyWxBf7kbu9DBPE=
-- Content-Length: 18
--
-- {"hello": "world"}
-- C.1. Default Test
--
-- If a list of headers is not included, the date is the only header
-- that is signed by default. The string to sign would be:
--
-- date: Sun, 05 Jan 2014 21:31:40 GMT
--
-- The Authorization header would be:
--
-- Authorization: Signature keyId="Test",algorithm="rsa-sha256",
-- signature="SjWJWbWN7i0wzBvtPl8rbASWz5xQW6mcJmn+ibttBqtifLN7Sazz
-- 6m79cNfwwb8DMJ5cou1s7uEGKKCs+FLEEaDV5lp7q25WqS+lavg7T8hc0GppauB
-- 6hbgEKTwblDHYGEtbGmtdHgVCk9SuS13F0hZ8FD0k/5OxEPXe5WozsbM="
--
-- The Signature header would be:
--
-- Signature: keyId="Test",algorithm="rsa-sha256",
-- signature="SjWJWbWN7i0wzBvtPl8rbASWz5xQW6mcJmn+ibttBqtifLN7Sazz
-- 6m79cNfwwb8DMJ5cou1s7uEGKKCs+FLEEaDV5lp7q25WqS+lavg7T8hc0GppauB
-- 6hbgEKTwblDHYGEtbGmtdHgVCk9SuS13F0hZ8FD0k/5OxEPXe5WozsbM="
--
DECLARE @dtNow int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT
EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
DECLARE @dateStr nvarchar(4000)
EXEC sp_OAMethod @dtNow, 'GetAsRfc822', @dateStr OUT, 0
-- To duplicate the above result, we'll hard-code the date string.
SELECT @dateStr = 'Sun, 05 Jan 2014 21:31:40 GMT'
DECLARE @rsa int
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @success OUT, @privKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbPublicKeyPem
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @sbPrivateKeyPem
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @rsa
RETURN
END
DECLARE @sbStringToSign int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbStringToSign OUT
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @dateStr
EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
DECLARE @b64Signature nvarchar(4000)
EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'
PRINT @b64Signature
PRINT '---------------------------'
-- The result should be:
-- SjWJWbWN7i0wzBvtPl8rbASW ... FD0k/5OxEPXe5WozsbM=
-- ----------------------------------------------------------------------------------------------------
-- C.2. Basic Test
--
-- The minimum recommended data to sign is the (request-target), host,
-- and date. In this case, the string to sign would be:
--
-- (request-target): post /foo?param=value&pet=dog
-- host: example.com
-- date: Sun, 05 Jan 2014 21:31:40 GMT
--
-- The Authorization header would be:
--
-- Authorization: Signature keyId="Test",algorithm="rsa-sha256",
-- headers="(request-target) host date", signature="qdx+H7PHHDZgy4
-- y/Ahn9Tny9V3GP6YgBPyUXMmoxWtLbHpUnXS2mg2+SbrQDMCJypxBLSPQR2aAjn
-- 7ndmw2iicw3HMbe8VfEdKFYRqzic+efkb3nndiv/x1xSHDJWeSWkx3ButlYSuBs
-- kLu6kd9Fswtemr3lgdDEmn04swr2Os0="
EXEC sp_OAMethod @sbStringToSign, 'Clear', NULL
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, '(request-target): '
EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, 'post /foo?param=value&pet=dog', 0
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'host: '
EXEC sp_OAMethod @sbStringToSign, 'AppendLine', @success OUT, 'example.com', 0
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, 'date: '
EXEC sp_OAMethod @sbStringToSign, 'Append', @success OUT, @dateStr
PRINT 'StringToSign:'
EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAMethod @sbStringToSign, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rsa, 'SignStringENC', @b64Signature OUT, @sTmp0, 'SHA256'
PRINT @b64Signature
PRINT '---------------------------'
-- The result should be:
-- qdx+H7PHHDZgy4y/Ahn ... mn04swr2Os0=
EXEC @hr = sp_OADestroy @sbPublicKeyPem
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @sbPrivateKeyPem
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @dtNow
EXEC @hr = sp_OADestroy @rsa
EXEC @hr = sp_OADestroy @sbStringToSign
END
GO