SQL Server
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.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)
DECLARE @success int
SELECT @success = 0
-- 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.
-- Provide information needed for Chilkat to automatically get an OAuth2 access token as needed.
DECLARE @json int
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
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
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
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 @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', @url, @jsonBody, 'application/json', @resp
IF @success = 0
BEGIN
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
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @statusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT
DECLARE @jsonResp int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResp OUT
EXEC sp_OAMethod @resp, 'GetBodyJson', @success OUT, @jsonResp
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 @resp
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
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 @resp
EXEC @hr = sp_OADestroy @jsonResp
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @rsa int
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 @resp
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 @resp
EXEC @hr = sp_OADestroy @jsonResp
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @rsa
END
GO