Decrypting .pgp data in T-SQL

Decrypting OpenPGP content with MsSqlPGP is very easy. We just have to specify the correct private decryption key and its unlocking password. Again just like when performing encryption. the private key can be specified as database server file location or serialized in ASCII armored text format.

Decrypting VARCHAR data

DECLARE 
@encryptedData AS nvarchar(MAX); -- OpenPGP encrypted data
 
DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
 
print 'Decrypted text is :'
print dbo.PGP_Decrypt_String(@encryptedData, 'C:\Keys\private.key', @private_key_password);

Decrypting with pre-loaded keys

DECLARE 
@encryptedData AS nvarchar(MAX); -- OpenPGP encrypted data
 
DECLARE @private_key varbinary(MAX)
DECLARE @private_keyASCII VARCHAR(MAX)
DECLARE @private_key_password nvarchar(2000)
 
--
-- load the private key
-- 
SELECT @private_key = BulkColumn FROM OPENROWSET(BULK'C:\Keys\private_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @private_keyASCII = dbo.PGP_Load_Key(@private_key)
 
SET @private_key_password = 'changeit'
 
print 'Decrypted text is :'
print dbo.PGP_Decrypt_String(@encryptedData, @private_keyASCII, @private_key_password);

Decrypting VARBINARY

When we decrypt VARBINARY data, we will end up with decrypted binary content:

DECLARE @encryptedData AS varbinary(MAX) -- OpenPGP encrypted data
 
DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
SET @DATA = dbo.PGP_Decrypt_Binary(@encryptedData, 'C:\Keys\private_key.asc', @private_key_password);

Decrypting binary with pre-loaded keys:

DECLARE @encryptedData AS varbinary(MAX) -- OpenPGP encrypted data
 
DECLARE @private_key varbinary(MAX)
DECLARE @private_keyASCII VARCHAR(MAX)
 
--
-- load the private key
-- 
SELECT @private_key = BulkColumn FROM OPENROWSET(BULK'C:\Documents and Settings\Stoyko\My Documents\MsSqlPGP 1.0\Examples\private_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @private_keyASCII = dbo.PGP_Load_Key(@private_key)
 
DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
SET @DATA = dbo.PGP_Decrypt_Binary(@encryptedData, @private_keyASCII, @private_key_password);

Decrypting external files

When we decrypt external files outside the MS SQL Server database, we have to explicitly specify where should the decrypted content be stored:

DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
 
EXEC dbo.PGP_Decrypt_File 'C:\Data\data1.pgp', 
							'C:\Keys\private_key.asc', 
							@private_key_password, 
							'C:\Data\data1_out.txt'

Summary

This chapter contains samples showing how to decrypt OpenPGP encrypted data inside MS SQL Server using the Transact-SQL (T-SQL) language and the MsSqlPGP routines.