(SQL Server) Set .pfx/.p12 Safe Bag Attributes

Demonstrates how to set safebag attributes in a .pfx/.p12. This example creates a .pfx from a .pem containing a private key and certificates, but also sets PFX safebag attributes before writing the .pfx.

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)
    -- We have a PEM containing one private key, and two certificates:
    -- The private key is an ECDSA private key.
    -- The private key is associated with the 1st certificate.
    -- The 2nd certificate is the issuer of the 1st certificate.

    -- -----BEGIN PRIVATE KEY-----
    -- ME0CAQAwEwYHKoZIzj0CAQYIKoZIzj0DAQcEMzAxAgEBBCDgAn4Dal+0iEhIsYBk
    -- 6SdSR344vyj0suhOIxsjmM19s6AKBggqhkjOPQMBBw==
    -- -----END PRIVATE KEY-----
    -- -----BEGIN CERTIFICATE-----
    -- MIIBXzCCAQSgAwIBAgIUGp2obfF61BG7QTsqpyT+VvxxJC0wCgYIKoZIzj0EAwIw
    -- DTELMAkGA1UEAwwCQ0EwHhcNMjAwMzI5MTU1MTEwWhcNMzAwMzI3MTU1MTEwWjAN
    -- MQswCQYDVQQDDAJFRTBZMBMGByqGSM49AgEGCCqGSM49AwEHA0IABEil+DhBUss8
    -- kMCjEWvZHA+jdy1mQ76a2HFd+5p+AcFGQxNeG8/HXZax7FFzcrczWrli25R8P8j1
    -- cqhwPY4HtwujQjBAMB0GA1UdDgQWBBTenwm6x4A4W5BzZ2OckKA2IFtPSTAfBgNV
    -- HSMEGDAWgBTx1U/gWiRhAASl6FV04DxP3XmcazAKBggqhkjOPQQDAgNJADBGAiEA
    -- rkqbz5t1M/CjqXSKE5ebBLQ3npF+q7GRC8C2ovDi/xoCIQDGve7OP/ppIDcCNonr
    -- +WSRf5M/6Wvw1lnEsAXf3nLTeQ==
    -- -----END CERTIFICATE-----
    -- -----BEGIN CERTIFICATE-----
    -- MIIBcDCCARWgAwIBAgIUAnQiKKy/PdLnH0A6vYKBq21w1JAwCgYIKoZIzj0EAwIw
    -- DTELMAkGA1UEAwwCQ0EwHhcNMjAwMzI5MTU1MTEwWhcNMzAwMzI3MTU1MTEwWjAN
    -- MQswCQYDVQQDDAJDQTBZMBMGByqGSM49AgEGCCqGSM49AwEHA0IABPB6yVvqt8cL
    -- EneRtnjoi87H0ATi+JP1w2qkz4GLOaPtFxAnV0LdQCuN91SGbAlKrSkhFyWWimjh
    -- Rqe9+b/1WCijUzBRMB0GA1UdDgQWBBTx1U/gWiRhAASl6FV04DxP3XmcazAfBgNV
    -- HSMEGDAWgBTx1U/gWiRhAASl6FV04DxP3XmcazAPBgNVHRMBAf8EBTADAQH/MAoG
    -- CCqGSM49BAMCA0kAMEYCIQCcIfssfrOruVYvqhxbLGeyc5ppEX53zUU35wIE2t7C
    -- fAIhAKhOTEvN+pdEn+cNwW3AEi7D08ZUQx3P80i4EnFPs0OQ
    -- -----END CERTIFICATE-----

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

    DECLARE @success int
    EXEC sp_OAMethod @sbPem, 'LoadFile', @success OUT, 'qa_data/pfx/test_ecdsa.pem', 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the PEM file.'
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END

    -- The PEM in this example is unencrypted.  There is no password.
    DECLARE @password nvarchar(4000)
    SELECT @password = ''
    EXEC sp_OAMethod @sbPem, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @pfx, 'LoadPem', @success OUT, @sTmp0, @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END

    -- Let's add some safebag attributes for the private key...
    DECLARE @forPrivateKey int
    SELECT @forPrivateKey = 1
    DECLARE @keyIdx int
    SELECT @keyIdx = 0
    EXEC sp_OAMethod @pfx, 'SetSafeBagAttr', @success OUT, @forPrivateKey, @keyIdx, 'localKeyId', '16777216', 'decimal'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END
    EXEC sp_OAMethod @pfx, 'SetSafeBagAttr', @success OUT, @forPrivateKey, @keyIdx, 'keyContainerName', '{B99EB9E7-6AF7-42AF-A43A-D4B2225B7605}', 'ascii'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END
    EXEC sp_OAMethod @pfx, 'SetSafeBagAttr', @success OUT, @forPrivateKey, @keyIdx, 'storageProvider', 'Microsoft Software Key Storage Provider', 'ascii'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END

    -- Add the localKeyId safebag attribute to the 1st certificate.
    SELECT @forPrivateKey = 0
    DECLARE @certIdx int
    SELECT @certIdx = 0
    EXEC sp_OAMethod @pfx, 'SetSafeBagAttr', @success OUT, @forPrivateKey, @certIdx, 'localKeyId', '16777216', 'decimal'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END

    -- Write the pfx.
    EXEC sp_OAMethod @pfx, 'ToFile', @success OUT, 'secret', 'qa_output/ee.pfx'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        RETURN
      END

    -- Let's load the .pfx we just wrote to see if the safebag attributes exist.
    DECLARE @pfx2 int
    -- Use "Chilkat_9_5_0.Pfx" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx2 OUT

    EXEC sp_OAMethod @pfx2, 'LoadPfxFile', @success OUT, 'qa_output/ee.pfx', 'secret'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @sbPem
        EXEC @hr = sp_OADestroy @pfx2
        RETURN
      END

    -- After calling LoadPfxFile, the LastJsonData shows what's in the loaded PFX.
    DECLARE @json int
    EXEC sp_OAMethod @pfx2, 'LastJsonData', @json OUT
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    EXEC @hr = sp_OADestroy @json

    -- The LastJsonData shows what's in the PFX just loaded:

    -- {
    --   "authenticatedSafe": {
    --     "contentInfo": [
    --       {
    --         "type": "Data",
    --         "safeBag": [
    --           {
    --             "type": "pkcs8ShroudedKeyBag",
    --             "attrs": {
    --               "keyContainerName": "{B99EB9E7-6AF7-42AF-A43A-D4B2225B7605}",
    --               "msStorageProvider": "Microsoft Software Key Storage Provider",
    --               "localKeyId": "16777216"
    --             }
    --           }
    --         ]
    --       },
    --       {
    --         "type": "EncryptedData",
    --         "safeBag": [
    --           {
    --             "type": "certBag",
    --             "attrs": {
    --               "localKeyId": "16777216"
    --             },
    --             "subject": "EE",
    --             "serialNumber": "1a9da86df17ad411bb413b2aa724fe56fc71242d"
    --           },
    --           {
    --             "type": "certBag",
    --             "subject": "CA",
    --             "serialNumber": "02742228acbf3dd2e71f403abd8281ab6d70d490"
    --           }
    --         ]
    --       }
    --     ]
    --   }
    -- }

    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @sbPem
    EXEC @hr = sp_OADestroy @pfx2


END
GO