(SQL Server) Easy Method to Import Certificate to Azure Key Vault

Demonstrates an easier method to import certificate with private key to an Azure key vault.

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

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- The certificate must originate from a source where the private key material is available to be included
    -- in the upload to Azure Key Vault.

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/myCert.pfx', 'pfx_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

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

    -- Indicate this request is to upload to Azure Key Vault.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'service', 'azure-keyvault'

    -- Provide your OAuth2 client credentials for your Azure App (service principal) that has
    -- the required Role-Based Access Control (RBAC) permissions.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'auth.client_id', 'APP_ID'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'auth.client_secret', 'APP_PASSWORD'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'auth.tenant_id', 'TENANT_ID'

    -- Indicate the key vault name
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'vault_name', 'VAULT_NAME'

    -- When you import a certificate into an Azure Key Vault, the certificate name and vault name are included as 
    -- parts of the URL to specify the target location where the certificate should be stored. 
    -- The URL follows a specific format to identify the target Key Vault and the certificate within it. 
    -- Here's how the certificate name and vault name are incorporated into the URL:
    -- https://VAULT_NAME.vault.azure.net//certificates/CERT_NAME/import?api-version=7.4

    -- Specify an arbitrary certificate name, but one that can be used in a URL as shown above.  (i.e. alphanumeric with no SPACE chars)
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'cert_name', 'CERT_NAME'

    -- Add optional tags if desired. Tags can be anything you want.   
    EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'tags.serial', @sTmp0
    EXEC sp_OAGetProperty @cert, 'IssuerCN', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'tags.issuer', @sTmp0
    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'tags.subject', @sTmp0

    -- OK.. everything is specified.  Simply call UploadToCloud.
    DECLARE @jsonOut int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT

    EXEC sp_OASetProperty @jsonOut, 'EmitCompact', 0
    EXEC sp_OAMethod @cert, 'UploadToCloud', @success OUT, @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @jsonOut, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END

    -- Success!  Go to the Azure portal and refresh to see the certificate has been uploaded.
    -- The jsonOut provides the JSON response from the Azure server.
    EXEC sp_OAMethod @jsonOut, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    PRINT 'Success'

    -- Here is sample jsonOut

    -- {
    --   "id": "https://kvchilkat.vault.azure.net/certificates/ChilkatTestCert123/b6e997db70144435a49d924be9f260ef",
    --   "kid": "https://kvchilkat.vault.azure.net/keys/ChilkatTestCert123/b6e997db70144435a49d924be9f260ef",
    --   "sid": "https://kvchilkat.vault.azure.net/secrets/ChilkatTestCert123/b6e997db70144435a49d924be9f260ef",
    --   "x5t": "I_e3776K5Q_6PN1HHvJoI2ZGQRQ",
    --   "cer": "MIIGXjCCBMagAw ... z50cjTsi7yIY=",
    --   "attributes": {
    --     "enabled": true,
    --     "nbf": 1633996800,
    --     "exp": 1728691199,
    --     "created": 1697754785,
    --     "updated": 1697754785,
    --     "recoveryLevel": "CustomizedRecoverable+Purgeable",
    --     "recoverableDays": 7
    --   },
    --   "tags": {
    --     "serial": "3FF5B69109BFD4046C92CC0D18EE23C2",
    --     "issuer": "Sectigo Public Code Signing CA R36",
    --     "subject": "Chilkat Software, Inc."
    --   },
    --   "policy": {
    --     "id": "https://kvchilkat.vault.azure.net/certificates/ChilkatTestCert123/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": 1697754785,
    --       "updated": 1697754785
    --     }
    --   }
    -- }

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonOut


END
GO