(SQL Server) Install PFX Certificates and Private Keys to Windows

Demonstrates how to install the certificates and private keys contained in a PFX to the Windows certificate stores.

Note: This example requires Chilkat v9.5.0.83 or greater.

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)
    -- Note: This example requires Chilkat v9.5.0.83 or greater.

    DECLARE @pfx int
    -- Use "Chilkat_9_5_0.Pfx" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    -- See the online reference documentation for more information about the ImportToWindows method.

    -- Mark imported privae keys as exportable.
    DECLARE @exportable int
    SELECT @exportable = 1

    -- If userProtected is 1, the user is to be notified through a dialog box or other method when certain attempts to use this key are made. 
    DECLARE @userProtected int
    SELECT @userProtected = 0

    -- If machineKeyset is 1, the private keys are stored under the local computer and not under the current user. 
    DECLARE @machineKeyset int
    SELECT @machineKeyset = 0

    -- If allowOverwriteKey is 1, allow overwrite of the existing key. 
    DECLARE @allowOverwriteKey int
    SELECT @allowOverwriteKey = 0

    -- If allowExport is 1, then the imported key can later be exported back to a PFX.
    -- (It also allows for applications to directly access the private key material.)
    DECLARE @allowExport int
    SELECT @allowExport = 1

    -- Indicate where to import types of certs.
    DECLARE @leafStore nvarchar(4000)
    SELECT @leafStore = 'My'
    DECLARE @intermediateStore nvarchar(4000)
    SELECT @intermediateStore = 'CertificationAuthority'
    DECLARE @rootStore nvarchar(4000)
    SELECT @rootStore = 'AuthRoot'

    EXEC sp_OAMethod @pfx, 'ImportToWindows', @success OUT, @exportable, @userProtected, @machineKeyset, @allowOverwriteKey, @allowExport, @leafStore, @intermediateStore, @rootStore, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Show information about the certificates imported
    DECLARE @json int
    EXEC sp_OAMethod @pfx, 'LastJsonData', @json OUT
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    EXEC @hr = sp_OADestroy @json

    -- Sample LastJsonData:

    -- {
    --   "exportable": true,
    --   "userProtected": false,
    --   "machineKeyset": false,
    --   "allowOverwriteKey": false,
    --   "allowExport": true,
    --   "key": [
    --     {
    --       "name": "{F09B755A-1E90-444D-9851-02B86CA14961}",
    --       "storageProvider": "Microsoft Software Key Storage Provider"
    --     }
    --   ],
    --   "cert": [
    --     {
    --       "DN": "...",
    --       "type": "intermediate",
    --       "storeName": "CertificationAuthority",
    --       "imported": true
    --     },
    --     {
    --       "DN": "...",
    --       "type": "root",
    --       "storeName": "AuthRoot",
    --       "imported": true
    --     },
    --     {
    --       "DN": "....",
    --       "type": "leaf",
    --       "storeName": "My",
    --       "keyContainer": "{345D4EAD-B8DD-4150-9A1F-4BC6437E77A2}",
    --       "machineKeyset": true,
    --       "imported": true
    --     }
    --   ]
    -- }


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @pfx


END
GO