OpenPGP signing in MS SQL Server

The OpenPGP signature format contains the data combined with the signature. In this article we will show how to create such signed content in Transact-SQL with the help of DidiSoft MsSqlPGP. The provided routines will require a private .pgp key in either ASCII armored format or as database server file system location.

Signing text data

The input for the dbo.PGP_Sign_String routine is NVARCHAR data and the output is again NVARCHAR. In this sample the private key is specified as a file system location on the database server machine:

DECLARE 
@signed AS nvarchar(MAX);
 
DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
 
--
-- OpenPGP signing with our private key
--
SET @signed = dbo.PGP_Sign_String('Hello World', 
                                  'c:\MsSqlPgp\Examples\Data\private_key.asc', 
                                  @private_key_password);
print 'Signed text is :'
print @signed;

Signing binary data

The input for the dbo.PGP_Sign_Binary is binary data and the output is again binary data. In this example the private key is first loaded from the file system:

DECLARE @enc AS varbinary(MAX)
DECLARE @DATA AS varbinary(MAX)
 
SELECT @DATA = BulkColumn FROM OPENROWSET(BULK'c:\MsSqlPgp\Examples\Data\binary.dat', SINGLE_BLOB) AS X;
 
 
DECLARE @private_key varbinary(MAX)
DECLARE @private_keyASCII VARCHAR(MAX)
 
--
-- load the private key
-- 
SELECT @private_key = BulkColumn FROM OPENROWSET(BULK'c:\MsSqlPgp\Examples\Data\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 @enc = dbo.PGP_Sign_Binary(@DATA, @private_keyASCII, @private_key_password);

Signing external files

Signing external files is done with the dbo.PGP_Sign_File T-SQL procedure. Here we have control will the output be binary or ASCII armored:

DECLARE @asciiOutput bit
SET @asciiOutput = 1 -- should the signed file be ASCII armored (1) or binary (0)
 
DECLARE @private_key_password nvarchar(2000)
SET @private_key_password = 'changeit'
 
EXEC dbo.PGP_Sign_File 'c:\MsSqlPgp\Examples\Data\data1.txt', 
			'c:\MsSqlPgp\Examples\Data\private_key.asc', 
			@private_key_password, 
			'c:\MsSqlPgp\Examples\Data\data1.pgp',
			@asciiOutput

Summary

This chapter illustrated how to sign VARCHAR, VARBINARY and external files with MsSqlPGP T-SQL routines.

List of methods used

dbo.PGP_Sign_String Creates an OpenPGP ASCII armored signed NVARCHAR output
dbo.PGP_Sign_Binary Creates an OpenPGP signed VARBINARY output
dbo.PGP_Sign_File OpenPGP signs external files