SQL Server Requires Chilkat v11.0.0+
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.Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
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