(SQL Server) Azure Key Vault Sign with a Certificate's Private Key

See more Azure Key Vault Examples

Signs a hash using the private key of a certificate previously imported to an Azure Key Vault.

Note: This example requires Chilkat v9.5.0.96 or later.

For more information, see https://learn.microsoft.com/en-us/rest/api/keyvault/certificates/import-certificate/import-certificate?tabs=HTTP

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- See Azure Key Vault Get Certificates for a more detailed explanation
    -- for how Chilkat is automatically getting the OAuth2 access token for your application.

    DECLARE @success int

    -- Provide information needed for Chilkat to automatically get an OAuth2 access token as needed.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_id', 'APP_ID'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'client_secret', 'APP_PASSWORD'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'resource', 'https://vault.azure.net'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'token_endpoint', 'https://login.microsoftonline.com/TENANT_ID/oauth2/token'

    -- In this example, we'll sign the SHA256 hash of the string "This is a test"
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    DECLARE @signedString nvarchar(4000)
    SELECT @signedString = 'This is a test'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @signedString
    DECLARE @hash_base64url nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetHash', @hash_base64url OUT, 'sha256', 'base64url', 'utf-8'

    -- We're going to send a POST to the following URL:
    -- POST {vaultBaseUrl}/keys/{key-or-cert-name}/{key-or-cert-version}/sign?api-version=7.4

    -- For example:

    -- POST https://VAULT_NAME.vault.azure.net/keys/CERT_NAME/CERT_VERSION/sign?api-version=7.4
    -- 
    -- {
    --   "alg": "RS512",
    --   "value": "RUE3Nzg4NTQ4QjQ5RjFFN0U2NzAyQzhDNEMwMkJDOTA1MTYyOTUzNjI5NDhBNzZDQTlFOTM1NDA2M0ZGMjk2Mg"
    -- }

    -- The alg can be one of the following
    -- ES256  ECDSA using P-256 and SHA-256
    -- ES256K ECDSA using P-256K and SHA-256
    -- ES384  ECDSA using P-384 and SHA-384
    -- ES512  ECDSA using P-521 and SHA-512
    -- PS256  RSASSA-PSS using SHA-256 and MGF1 with SHA-256
    -- PS384  RSASSA-PSS using SHA-384 and MGF1 with SHA-384
    -- PS512  RSASSA-PSS using SHA-512 and MGF1 with SHA-512
    -- RS256  RSASSA-PKCS1-v1_5 using SHA-256
    -- RS384  RSASSA-PKCS1-v1_5 using SHA-384
    -- RS512  RSASSA-PKCS1-v1_5 using SHA-512

    -- The sample POST above uses SHA512.  We'll instead sign a SHA256 hash..

    DECLARE @jsonBody int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonBody OUT

    EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'alg', 'RS256'
    EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'value', @hash_base64url

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    -- Instead of providing an actual access token, we give Chilkat the information that allows it to 
    -- automatically fetch the access token using the OAuth2 client credentials flow.
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'certName', 'importCert01'
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'certVersion', '7140c8755ed14839b5d86a9f7e7f0497'
    -- Note: Replace "VAULT_NAME" with the name of your Azure key vault.
    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://VAULT_NAME.vault.azure.net/keys/{$certName}/{$certVersion}/sign?api-version=7.4'
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, @url, 'application/json', @jsonBody
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        -- This means something failed before we got a response.
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonBody
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT

    DECLARE @jsonResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT

    EXEC sp_OAMethod @resp, 'GetBodyJson', @success OUT, @jsonResp
    EXEC @hr = sp_OADestroy @resp

    EXEC sp_OASetProperty @jsonResp, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    IF @statusCode <> 200
      BEGIN

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonBody
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonResp
        RETURN
      END

    -- A successful response body contains JSON like this:
    -- Note: Azure's documentation is not very clear, but base64url is the encoding, not "base64".
    -- {
    --   "kid": "https://kvchilkat.vault.azure.net/keys/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
    --   "value": "JzWd2YF21gjtW ... Em37hKOQ"
    -- }

    -- Let's validate the signature using the cert's public key.
    -- This example will load the corresponding certificate from a local file and will verify the signature against the original data.
    -- 
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/chilkat_code_signing_2024.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonBody
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonResp
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    -- Tell the RSA object to use the cert's public key.
    EXEC sp_OAMethod @rsa, 'SetX509Cert', @success OUT, @cert, 0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @jsonBody
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonResp
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Verify the signature using the cert's public key against the original string.
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64url'
    DECLARE @valid int
    EXEC sp_OAMethod @jsonResp, 'StringOf', @sTmp0 OUT, 'value'
    EXEC sp_OAMethod @rsa, 'VerifyStringENC', @valid OUT, @signedString, 'sha-256', @sTmp0

    PRINT 'signature valid = ' + @valid

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @jsonBody
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonResp
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @rsa


END
GO