SQL Server Requires Chilkat v11.7.0+
SQL Server
Encrypt and Sign a PDF, and Sign an Encrypted PDF
See more PDF Protection Examples
Demonstrates signing together with encryption. A pending encrypt is applied by SignPdf before it signs, so one call produces a PDF that is both encrypted and signed. A PDF that is already encrypted can be signed too, and stays encrypted. When it was opened with only the user password its permissions decide: adding a signature field needs both the modify and the annotate permission. Loading the PDF with its owner password lifts that.Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- The PDF used in this example is available at two_page_memo.pdf
-- The signing certificate, with its private key.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/myPdfSigningCert.pfx', 'secret'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @pdf int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/two_page_memo.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
RETURN
END
-- Encrypt with AES-256 and do not permit modifying.
EXEC sp_OASetProperty @pdf, 'UserPassword', 'user-secret'
EXEC sp_OASetProperty @pdf, 'OwnerPassword', 'owner-secret'
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'permissions.modify', 0
DECLARE @jsonOut int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT
EXEC sp_OAMethod @pdf, 'Protection', @success OUT, 'encrypt', @json, @jsonOut
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
RETURN
END
-- The encryption is still pending. SignPdf applies it first and signs the encrypted result.
EXEC sp_OAMethod @pdf, 'SetSigningCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
RETURN
END
DECLARE @sigOpts int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @sigOpts OUT
EXEC sp_OAMethod @sigOpts, 'UpdateBool', @success OUT, 'invisibleSignature', 1
EXEC sp_OAMethod @sigOpts, 'UpdateInt', @success OUT, 'signingCertificateV2', 1
EXEC sp_OAMethod @sigOpts, 'UpdateInt', @success OUT, 'signingTime', 1
EXEC sp_OAMethod @pdf, 'SignPdf', @success OUT, @sigOpts, 'qa_output/memo_encrypted_signed.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
RETURN
END
-- The result is encrypted and signed.
DECLARE @check int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @check OUT
EXEC sp_OASetProperty @check, 'UserPassword', 'user-secret'
EXEC sp_OAMethod @check, 'LoadFile', @success OUT, 'qa_output/memo_encrypted_signed.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @check, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
RETURN
END
DECLARE @empty int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @empty OUT
DECLARE @info int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @info OUT
EXEC sp_OAMethod @check, 'Protection', @success OUT, 'getInfo', @empty, @info
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @check, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
RETURN
END
EXEC sp_OAMethod @info, 'StringOf', @sTmp0 OUT, 'loaded.algorithm'
PRINT 'algorithm: ' + @sTmp0
EXEC sp_OAGetProperty @check, 'NumSignatures', @iTmp0 OUT
PRINT 'signatures: ' + @iTmp0
DECLARE @sigInfo int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @sigInfo OUT
DECLARE @verified int
EXEC sp_OAMethod @check, 'VerifySignature', @verified OUT, 0, @sigInfo
PRINT 'signature verifies: ' + @verified
-- Signing an encrypted PDF. This one was opened with only the user password, and its
-- permissions do not allow modifying, so another signature is refused.
EXEC sp_OAMethod @check, 'SetSigningCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @check, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
RETURN
END
EXEC sp_OAMethod @check, 'SignPdf', @success OUT, @sigOpts, 'qa_output/memo_signed_twice.pdf'
IF @success = 0
BEGIN
PRINT 'A second signature was refused: the permissions do not allow it.'
END
-- Loaded with the owner password, the same PDF can be signed. It stays encrypted, and the
-- first signature stays valid because the new one is added as an incremental update.
DECLARE @owner int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @owner OUT
EXEC sp_OASetProperty @owner, 'UserPassword', 'user-secret'
EXEC sp_OASetProperty @owner, 'OwnerPassword', 'owner-secret'
EXEC sp_OAMethod @owner, 'LoadFile', @success OUT, 'qa_output/memo_encrypted_signed.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @owner, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
EXEC @hr = sp_OADestroy @owner
RETURN
END
EXEC sp_OAMethod @owner, 'SetSigningCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @owner, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
EXEC @hr = sp_OADestroy @owner
RETURN
END
EXEC sp_OAMethod @owner, 'SignPdf', @success OUT, @sigOpts, 'qa_output/memo_signed_twice.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @owner, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
EXEC @hr = sp_OADestroy @owner
RETURN
END
DECLARE @twice int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @twice OUT
EXEC sp_OASetProperty @twice, 'UserPassword', 'user-secret'
EXEC sp_OAMethod @twice, 'LoadFile', @success OUT, 'qa_output/memo_signed_twice.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @twice, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
EXEC @hr = sp_OADestroy @owner
EXEC @hr = sp_OADestroy @twice
RETURN
END
EXEC sp_OAGetProperty @twice, 'NumSignatures', @iTmp0 OUT
PRINT 'signatures after signing with owner rights: ' + @iTmp0
EXEC sp_OAMethod @twice, 'VerifySignature', @verified OUT, 0, @sigInfo
PRINT 'first signature still verifies: ' + @verified
EXEC sp_OAMethod @twice, 'VerifySignature', @verified OUT, 1, @sigInfo
PRINT 'second signature verifies: ' + @verified
-- Sample output:
-- algorithm: aes-256
-- signatures: 1
-- signature verifies: True
-- A second signature was refused: the permissions do not allow it.
-- signatures after signing with owner rights: 2
-- first signature still verifies: True
-- second signature verifies: True
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @sigOpts
EXEC @hr = sp_OADestroy @check
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @sigInfo
EXEC @hr = sp_OADestroy @owner
EXEC @hr = sp_OADestroy @twice
END
GO