SQL Server Requires Chilkat v11.0.0+

Set .pfx/.p12 Safe Bag Attributes

See more PFX/P12 Examples

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. 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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  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
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @sbPem int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPem OUT

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

    --  Information about the contents of the PFX was collected in the call to LoadPfxFile.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

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

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

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


END
GO