SQL Server

Import a PFX/P12 into the Windows Certificate Stores

See more Certificates Examples

Demonstrates how to import the certificates contained in a .pfx/.p12 to the Windows certificate stores. 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 @pfx int
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/pfx/example.p12', 'p12Password'
    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 @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @pfx, 'GetLastJsonData', NULL, @json

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample of the last JSON data:

    -- {
    --   "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
    EXEC @hr = sp_OADestroy @json


END
GO