Encrypting

MsSqlPGP offers routines for encrypting VARCHAR and VARBINARY data and external files on the MS SQL Server file system (or a network share).

Encrypt NVARCHAR
Encrypt VARBINARY
Encrypt external files

Encrypting NVARCHAR

When encrypting we can pass the public OpenPGP encryption key as a file system location on the MS SQL Server machine or serialized in a VARCHAR column in ASCII armored. Below we can see both ways:

String encryption with external keys

--  String encryption and decryption with external keys
DECLARE 
@enc AS nvarchar(MAX);
 
SET @enc = dbo.PGP_Encrypt_String('Hello World', 'C:\Keys\public_key.asc');
print 'Encrypted text is :'
print @enc;

String encryption with pre-loaded keys

DECLARE 
@enc AS nvarchar(MAX);
 
DECLARE @public_key varbinary(MAX)
DECLARE @public_keyASCII VARCHAR(MAX)
 
--
-- load the public key
-- 
SELECT @public_key = BulkColumn FROM OPENROWSET(BULK'C:\Keys\public_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @public_keyASCII = dbo.PGP_Load_Key(@public_key)
 
SET @enc = dbo.PGP_Encrypt_String('Hello World', @public_keyASCII);
print 'Encrypted text is :'
print @enc;

Encrypting VARBINARY

With binary data we have the same options as with VARCHAR:

DECLARE @enc AS varbinary(MAX)
DECLARE @DATA AS varbinary(MAX)
 
SELECT @DATA = BulkColumn FROM OPENROWSET(BULK'C:\Data\binary.dat', SINGLE_BLOB) AS X;
 
SET @enc = dbo.PGP_Encrypt_Binary(@DATA, 'C:\Keys\public_key.asc');
print 'Encrypted data is :'
print @enc;

And encrypting by using pre-loaded keys:

DECLARE @enc AS varbinary(MAX)
DECLARE @DATA AS varbinary(MAX)
 
DECLARE @public_key varbinary(MAX)
DECLARE @public_keyASCII VARCHAR(MAX)
 
--
-- load the public key
-- 
SELECT @public_key = BulkColumn FROM OPENROWSET(BULK'C:\Keys\public_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @public_keyASCII = dbo.PGP_Load_Key(@public_key)
 
SELECT @DATA = BulkColumn FROM OPENROWSET(BULK'C:\Data\binary.dat', SINGLE_BLOB) AS X;
 
SET @enc = dbo.PGP_Encrypt_Binary(@DATA, @public_keyASCII);
print 'Encrypted data is :'
print @enc;

Encrypting external files

When we create external .pgp encrypted archives, we can specify should the result file be in binary format or in ASCII armored text format, through the last parameter of the PGP_Encrypt_File procedure:

DECLARE @asciiOutput bit
SET @asciiOutput = 1 -- should the encrypted file be ASCII armored (1) or binary (0)
 
EXEC dbo.PGP_Encrypt_File 'C:\Data\data1.txt', 'C:\Keys\public_key.asc', 'C:\Output\data1.pgp', @asciiOutput

Summary

This article illustrated how to use the OpenPGP encryption routines offered by DidiSoft MsSqlPGP for Transact-SQL developers.

List of methods used

dbo.PGP_Encrypt_String encrypts NVARCHAR data into ASCII armored output format
dbo.PGP_Encrypt_Binary encrypts binary data into binary .pgp output format
dbo.PGP_Encrypt_File OpenPGP encrypts external files