(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.

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.

    -- 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

    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPfx OUT

    DECLARE @success int
    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
    -- 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, '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 sp_OAMethod @cert, 'ExportPrivateKey', @privKey OUT
    EXEC sp_OAGetProperty @cert, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'The certificate does not have a private key.'
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @bdPfx
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    -- Get the private key as a JWK so we can get information about it..
    DECLARE @jwk int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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'

    EXEC @hr = sp_OADestroy @privKey

    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

    -- 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 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 @bdPfx
        EXEC @hr = sp_OADestroy @cert
        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
        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 @bdPfx
        EXEC @hr = sp_OADestroy @cert
        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 @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 @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 @jsonResp


END
GO