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 |