SQL Server

Rewrite PFX using AES256-SHA256

See more PFX/P12 Examples

Demonstrates how to load a .pfx/.p12, examine the encryption algorithm used, and rewrite using aes256-sha256. 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

    -- Let's load a .pfx and examine the encryption algorithms used to protect the private key:
    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

    -- Examine the algorithms:

    -- "pbeWithSHAAnd3_KeyTripleDES_CBC" or "pbes2"?

    EXEC sp_OAGetProperty @pfx, 'AlgorithmId', @sTmp0 OUT
    PRINT 'Algorithm: ' + @sTmp0

    -- If the algorithm is "pbes2" then examine the actual encryption and HMAC algorithms used within pbes2.
    -- (If the algorithm is NOT "pbes2", then the following properties are meaningless and will not be modified from their previous values prior to loading the PFX.)

    EXEC sp_OAGetProperty @pfx, 'Pbes2CryptAlg', @sTmp0 OUT
    PRINT 'Pbes2CryptAlg: ' + @sTmp0

    EXEC sp_OAGetProperty @pfx, 'Pbes2HmacAlg', @sTmp0 OUT
    PRINT 'Pbes2HmacAlg: ' + @sTmp0

    -- Our output so far:

    -- Algorithm: pbeWithSHAAnd3_KeyTripleDES_CBC
    -- Pbes2CryptAlg: aes256-cbc
    -- Pbes2HmacAlg: hmacWithSha256

    -- This tells us that the PFX we loaded was protected using triple-DES with SHA1.
    -- (Most existing .pfx/.p12 files use 3DES w/ SHA1.)
    -- The Pbes2CryptAlg and Pbes2HmacAlg properties do not apply here because the AlgorithmId is not equal to "pbes2".  We can ignore those values.

    -- Examine the last JSON data collected in the call to LoadPfxFile.  This gives us information about what is contained in the PFX, including extended attributes.
    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 output

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    -- {
    --   "authenticatedSafe": {
    --     "contentInfo": [
    --       {
    --         "type": "Data",
    --         "safeBag": [
    --           {
    --             "type": "pkcs8ShroudedKeyBag",
    --             "attrs": {
    --               "localKeyId": "16444216",
    --               "keyContainerName": "{F09B755A-1E90-444D-9851-02B86CA14961}",
    --               "msStorageProvider": "Microsoft Enhanced Cryptographic Provider v1.0"
    --             }
    --           }
    --         ]
    --       },
    --       {
    --         "type": "EncryptedData",
    --         "safeBag": [
    --           {
    --             "type": "certBag",
    --             "attrs": {
    --               "localKeyId": "16444216"
    --             },
    --             "subject": "....",
    --             "serialNumber": "9999999999999999999999999999"
    --           },
    --           {
    --             "type": "certBag",
    --             "attrs": {
    --               "authRootSha256Hash": "0vkOXTXKxNQffUTOZq/4heGBX7M5GFhTqH5mwFyb7x4=",
    --               "friendlyName": "XYZ",
    --               "enhKeyUsage": [
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.2",
    --                   "usage": "clientAuth"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.4",
    --                   "usage": "emailProtection"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.3",
    --                   "usage": "codeSigning"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.8",
    --                   "usage": "timeStamping"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.4.1.311.10.3.4",
    --                   "usage": "encryptedFileSystem"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.8.2.2",
    --                   "usage": "iKEIntermediate"
    --                 },
    -- 
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.6",
    --                   "usage": "ipsecTunnel"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.7",
    --                   "usage": "ipsecUser"
    --                 },
    --                 {
    --                   "oid": "1.3.6.1.5.5.7.3.5",
    --                   "usage": "ipsecEndSystem"
    --                 }
    --               ]
    --             },
    --             "subject": "...",
    --             "serialNumber": "8888888888888888888888888888"
    --           },
    --           {
    --             "type": "certBag",
    --             "subject": "...",
    --             "serialNumber": "777777777777777777777777777"
    --           }
    --         ]
    --       }
    --     ]
    --   }
    -- }

    -- ------------------------------------------------------------------------------------------
    -- OK... now let's change the AlgorithmId to "pbes2" 

    EXEC sp_OASetProperty @pfx, 'AlgorithmId', 'pbes2'

    -- We already know from above that the PBES2 crypt and HMAC algorithms are "aes256-cbc" and "hmacWithSha256".
    -- Let's set them anyway just for the example...
    EXEC sp_OASetProperty @pfx, 'Pbes2CryptAlg', 'aes256-cbc'
    EXEC sp_OASetProperty @pfx, 'Pbes2HmacAlg', 'hmacWithSha256'

    -- Rewrite the PFX using pbes2/aes256 + sha256
    EXEC sp_OAMethod @pfx, 'ToFile', @success OUT, 'secret', 'qa_output/test_secret_aes256.pfx'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @json
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @json


END
GO