SQL Server Requires Chilkat v11.7.0+
SQL Server
Encrypt a PDF for a Certificate Instead of a Password
See more PDF Protection Examples
Demonstrates certificate security, where a PDF is encrypted for one or more recipients and each opens it with their own certificate and private key. No password is involved. Only the public certificate of a recipient is needed to encrypt. Each recipient has its own permissions, and the changeSecurity permission gives a recipient owner rights. PreLoadCheck tells an application that a file is certificate-encrypted and which certificates can open it. A PDF that cannot be opened, because no matching certificate and private key were provided, fails to load.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 recipient's certificate. Only the public certificate is needed to encrypt.
DECLARE @recipient int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @recipient OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @recipient, 'LoadFromFile', @success OUT, 'qa_data/certs/recipient.cer'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @recipient, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
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 @recipient
EXEC @hr = sp_OADestroy @pdf
RETURN
END
-- Call AddEncryptCert once for each recipient.
EXEC sp_OAMethod @pdf, 'AddEncryptCert', @success OUT, @recipient
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
RETURN
END
-- recipientPermissions[0] belongs to the first certificate added. changeSecurity gives that
-- recipient owner rights, so the file can be managed later.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'method', 'certificate'
EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'recipientPermissions[0].changeSecurity', 1
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 @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
RETURN
END
EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'pending.method'
PRINT 'method: ' + @sTmp0
EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'pending.algorithm'
PRINT 'algorithm: ' + @sTmp0
EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @iTmp0 OUT, 'pending.recipients'
PRINT 'recipients: ' + @iTmp0
EXEC sp_OAMethod @pdf, 'WriteFile', @success OUT, 'qa_output/memo_for_recipient.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
RETURN
END
-- PreLoadCheck says how the file is protected, without loading it.
DECLARE @reader int
EXEC @hr = sp_OACreate 'Chilkat.Pdf', @reader OUT
DECLARE @info int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @info OUT
EXEC sp_OAMethod @reader, 'PreLoadCheck', @success OUT, 'qa_output/memo_for_recipient.pdf', @info
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @reader, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
RETURN
END
EXEC sp_OAMethod @info, 'StringOf', @sTmp0 OUT, 'method'
PRINT 'saved file, method: ' + @sTmp0
EXEC sp_OAMethod @info, 'SizeOfArray', @iTmp0 OUT, 'recipients'
PRINT 'saved file, recipients: ' + @iTmp0
-- The recipient opens it with the certificate and its private key, here from a .pfx file.
-- (On Windows, a certificate that is installed in the Windows certificate store with its private
-- key is found automatically when the PDF is loaded, and SetDecryptCert is not needed for it.)
DECLARE @myCert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @myCert OUT
EXEC sp_OAMethod @myCert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/recipient.pfx', 'pfx-password'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @myCert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @myCert
RETURN
END
EXEC sp_OAMethod @reader, 'SetDecryptCert', @success OUT, @myCert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @reader, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @myCert
RETURN
END
EXEC sp_OAMethod @reader, 'LoadFile', @success OUT, 'qa_output/memo_for_recipient.pdf'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @reader, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @myCert
RETURN
END
DECLARE @empty int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @empty OUT
DECLARE @loadedInfo int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @loadedInfo OUT
EXEC sp_OAMethod @reader, 'Protection', @success OUT, 'getInfo', @empty, @loadedInfo
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @reader, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @myCert
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @loadedInfo
RETURN
END
EXEC sp_OAMethod @loadedInfo, 'StringOf', @sTmp0 OUT, 'loaded.openedWith'
PRINT 'opened with: ' + @sTmp0
EXEC sp_OAGetProperty @reader, 'NumPages', @iTmp0 OUT
PRINT 'pages: ' + @iTmp0
-- Sample output:
-- method: certificate
-- algorithm: aes-256
-- recipients: 1
-- saved file, method: certificate
-- saved file, recipients: 1
-- opened with: certificate
-- pages: 2
EXEC @hr = sp_OADestroy @recipient
EXEC @hr = sp_OADestroy @pdf
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonOut
EXEC @hr = sp_OADestroy @reader
EXEC @hr = sp_OADestroy @info
EXEC @hr = sp_OADestroy @myCert
EXEC @hr = sp_OADestroy @empty
EXEC @hr = sp_OADestroy @loadedInfo
END
GO