SQL Server
SQL Server
Azure Key Vault Import Certificate
See more Azure Key Vault Examples
Imports a certificate into a specified Azure key vault.Imports an existing valid certificate, containing a private key, into Azure Key Vault. The certificate to be imported can be in either PFX or PEM format. If the certificate is in PEM format the PEM file must contain the key as well as x509 certificates. Key Vault will only accept a key in PKCS#8 format.
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
DECLARE @iTmp0 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'
-- Note: This example is using a relative file path. You can also specify a full file path, such as "C:/someDir/myCertAndKey.pfx"
-- or a file path the makes sense on non-Windows operating systems..
DECLARE @pfxFilePath nvarchar(4000)
SELECT @pfxFilePath = 'qa_data/pfx/myCertAndKey.pfx'
-- Load the PFX file to be imported to the Azure Key Vault.
DECLARE @bdPfx int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPfx OUT
EXEC sp_OAMethod @bdPfx, 'LoadFile', @success OUT, @pfxFilePath
IF @success = 0
BEGIN
PRINT 'Failed to load the PFX file.'
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @bdPfx
RETURN
END
-- We'll be sending a POST request like this:
-- POST https://myvault.vault.azure.net//certificates/importCert01/import?api-version=7.4
--
-- {
-- "value": "MIIJ...",
-- "pwd": "123",
-- "policy": {
-- "key_props": {
-- "exportable": true,
-- "kty": "RSA",
-- "key_size": 2048,
-- "reuse_key": false
-- },
-- "secret_props": {
-- "contentType": "application/x-pkcs12"
-- }
-- }
-- }
-- Also load the PFX into the Chilkat certificate object so we can get
-- information about the key type and size.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, @pfxFilePath, 'pfx_password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @bdPfx
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @privKey int
EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT
EXEC sp_OAMethod @cert, 'GetPrivateKey', @success OUT, @privKey
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @bdPfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
RETURN
END
-- Get the private key as a JWK so we can get information about it..
DECLARE @jwk int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jwk OUT
EXEC sp_OAMethod @privKey, 'GetJwk', @sTmp0 OUT
EXEC sp_OAMethod @jwk, 'Load', @success OUT, @sTmp0
-- Get the key type
DECLARE @sbKty int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbKty OUT
EXEC sp_OAMethod @jwk, 'StringOf', @sTmp0 OUT, 'kty'
EXEC sp_OAMethod @sbKty, 'Append', @success OUT, @sTmp0
-- If this is an EC key, get the curve name
DECLARE @sbCurve int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCurve OUT
EXEC sp_OAMethod @jwk, 'HasMember', @iTmp0 OUT, 'crv'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @jwk, 'StringOf', @sTmp0 OUT, 'crv'
EXEC sp_OAMethod @sbCurve, 'Append', @success OUT, @sTmp0
END
-- Build the JSON that will be the body of the HTTP POST.
DECLARE @jsonBody int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonBody OUT
EXEC sp_OAMethod @bdPfx, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'value', @sTmp0
EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'pwd', 'pfx_password'
EXEC sp_OAMethod @jsonBody, 'UpdateBool', @success OUT, 'policy.key_props.exportable', 1
EXEC sp_OAMethod @sbKty, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'policy.key_props.kty', @sTmp0
EXEC sp_OAMethod @sbKty, 'ContentsEqual', @iTmp0 OUT, 'RSA', 0
IF @iTmp0 = 1
BEGIN
EXEC sp_OAGetProperty @privKey, 'BitLength', @iTmp0 OUT
EXEC sp_OAMethod @jsonBody, 'UpdateInt', @success OUT, 'policy.key_props.key_size', @iTmp0
END
EXEC sp_OAMethod @sbKty, 'ContentsEqual', @iTmp0 OUT, 'EC', 0
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @sbCurve, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'policy.key_props.crv', @sTmp0
END
EXEC sp_OAMethod @jsonBody, 'UpdateBool', @success OUT, 'policy.key_props.reuse_key', 0
EXEC sp_OAMethod @jsonBody, 'UpdateString', @success OUT, 'policy.secret_props.contentType', 'application/x-pkcs12'
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
-- Choose anything to be the name of your imported certificate.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'certificateName', 'importCert01'
-- Note: Replace "VAULT_NAME" with the name of your Azure key vault.
DECLARE @url nvarchar(4000)
SELECT @url = 'https://VAULT_NAME.vault.azure.net/certificates/{$certificateName}/import?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 @bdPfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwk
EXEC @hr = sp_OADestroy @sbKty
EXEC @hr = sp_OADestroy @sbCurve
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 @bdPfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwk
EXEC @hr = sp_OADestroy @sbKty
EXEC @hr = sp_OADestroy @sbCurve
EXEC @hr = sp_OADestroy @jsonBody
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResp
RETURN
END
-- A successful JSON response looks like this:
-- {
-- "id": "https://kvchilkat.vault.azure.net/certificates/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "kid": "https://kvchilkat.vault.azure.net/keys/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "sid": "https://kvchilkat.vault.azure.net/secrets/importCert01/7140c8755ed14839b5d86a9f7e7f0497",
-- "x5t": "I_e3776K5Q_6PN1HHvJoI2ZGQRQ",
-- "cer": "MIIG ... jTsi7yIY=",
-- "attributes": {
-- "enabled": true,
-- "nbf": 1633996800,
-- "exp": 1728691199,
-- "created": 1697411128,
-- "updated": 1697411128,
-- "recoveryLevel": "CustomizedRecoverable+Purgeable",
-- "recoverableDays": 7
-- },
-- "policy": {
-- "id": "https://kvchilkat.vault.azure.net/certificates/importCert01/policy",
-- "key_props": {
-- "exportable": true,
-- "kty": "RSA",
-- "key_size": 4096,
-- "reuse_key": false
-- },
-- "secret_props": {
-- "contentType": "application/x-pkcs12"
-- },
-- "x509_props": {
-- "subject": "CN=\"Chilkat Software, Inc.\", O=\"Chilkat Software, Inc.\", S=Illinois, C=US",
-- "ekus": [
-- "1.3.6.1.5.5.7.3.3"
-- ],
-- "key_usage": [
-- "digitalSignature"
-- ],
-- "validity_months": 37,
-- "basic_constraints": {
-- "ca": false
-- }
-- },
-- "lifetime_actions": [
-- {
-- "trigger": {
-- "lifetime_percentage": 80
-- },
-- "action": {
-- "action_type": "EmailContacts"
-- }
-- }
-- ],
-- "issuer": {
-- "name": "Unknown"
-- },
-- "attributes": {
-- "enabled": true,
-- "created": 1697411128,
-- "updated": 1697411128
-- }
-- }
-- }
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
DECLARE @strVal nvarchar(4000)
DECLARE @Lifetime_percentage int
DECLARE @Action_type nvarchar(4000)
DECLARE @id nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @id OUT, 'id'
DECLARE @kid nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @kid OUT, 'kid'
DECLARE @sid nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @sid OUT, 'sid'
DECLARE @x5t nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @x5t OUT, 'x5t'
DECLARE @cer nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @cer OUT, 'cer'
DECLARE @Enabled int
EXEC sp_OAMethod @jsonResp, 'BoolOf', @Enabled OUT, 'attributes.enabled'
DECLARE @Nbf int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Nbf OUT, 'attributes.nbf'
DECLARE @Exp int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Exp OUT, 'attributes.exp'
DECLARE @Created int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Created OUT, 'attributes.created'
DECLARE @Updated int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Updated OUT, 'attributes.updated'
DECLARE @RecoveryLevel nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @RecoveryLevel OUT, 'attributes.recoveryLevel'
DECLARE @RecoverableDays int
EXEC sp_OAMethod @jsonResp, 'IntOf', @RecoverableDays OUT, 'attributes.recoverableDays'
DECLARE @Id nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @Id OUT, 'policy.id'
DECLARE @Exportable int
EXEC sp_OAMethod @jsonResp, 'BoolOf', @Exportable OUT, 'policy.key_props.exportable'
DECLARE @Kty nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @Kty OUT, 'policy.key_props.kty'
DECLARE @Key_size int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Key_size OUT, 'policy.key_props.key_size'
DECLARE @Reuse_key int
EXEC sp_OAMethod @jsonResp, 'BoolOf', @Reuse_key OUT, 'policy.key_props.reuse_key'
DECLARE @ContentType nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @ContentType OUT, 'policy.secret_props.contentType'
DECLARE @Subject nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @Subject OUT, 'policy.x509_props.subject'
DECLARE @Validity_months int
EXEC sp_OAMethod @jsonResp, 'IntOf', @Validity_months OUT, 'policy.x509_props.validity_months'
DECLARE @Ca int
EXEC sp_OAMethod @jsonResp, 'BoolOf', @Ca OUT, 'policy.x509_props.basic_constraints.ca'
DECLARE @Name nvarchar(4000)
EXEC sp_OAMethod @jsonResp, 'StringOf', @Name OUT, 'policy.issuer.name'
DECLARE @AttributesEnabled int
EXEC sp_OAMethod @jsonResp, 'BoolOf', @AttributesEnabled OUT, 'policy.attributes.enabled'
DECLARE @AttributesCreated int
EXEC sp_OAMethod @jsonResp, 'IntOf', @AttributesCreated OUT, 'policy.attributes.created'
DECLARE @AttributesUpdated int
EXEC sp_OAMethod @jsonResp, 'IntOf', @AttributesUpdated OUT, 'policy.attributes.updated'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jsonResp, 'SizeOfArray', @count_i OUT, 'policy.x509_props.ekus'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResp, 'I', @i
EXEC sp_OAMethod @jsonResp, 'StringOf', @strVal OUT, 'policy.x509_props.ekus[i]'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @jsonResp, 'SizeOfArray', @count_i OUT, 'policy.x509_props.key_usage'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResp, 'I', @i
EXEC sp_OAMethod @jsonResp, 'StringOf', @strVal OUT, 'policy.x509_props.key_usage[i]'
SELECT @i = @i + 1
END
SELECT @i = 0
EXEC sp_OAMethod @jsonResp, 'SizeOfArray', @count_i OUT, 'policy.lifetime_actions'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResp, 'I', @i
EXEC sp_OAMethod @jsonResp, 'IntOf', @Lifetime_percentage OUT, 'policy.lifetime_actions[i].trigger.lifetime_percentage'
EXEC sp_OAMethod @jsonResp, 'StringOf', @Action_type OUT, 'policy.lifetime_actions[i].action.action_type'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @bdPfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @privKey
EXEC @hr = sp_OADestroy @jwk
EXEC @hr = sp_OADestroy @sbKty
EXEC @hr = sp_OADestroy @sbCurve
EXEC @hr = sp_OADestroy @jsonBody
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @jsonResp
END
GO