SQL Server

Easy Method to Import Certificate to Azure Key Vault

See more Azure Key Vault Examples

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.

SQL Server

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

    DECLARE @cert int
    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.

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