Loading and using OpenPGP keys in MS SQL Server

Loading .pgp keys into the database

OpenPGP keys can be stored in the more common text format (ASCII armored format) usually with file name extension .asc or in binary format. The T-SQL routines provided by MsSqlPGP accept the keys serialized in ASCII armor text format or as file system path locations on the machine where the MS SQL Server instance resides.

A helper function PGP_Load_Key(@key) helps to transform a .pgp key (no matter if it is binary or ASCII armored) into ASCII armored format. Here is an example:

DECLARE @KEY varbinary(MAX)
DECLARE @keyASCII VARCHAR(MAX)
 
--
-- load the public key
-- 
SELECT @KEY = BulkColumn FROM OPENROWSET(BULK'C:\Documents and Settings\Stoyko\My Documents\MsSqlPGP 1.0\Examples\public_key.asc', SINGLE_BLOB) AS X;
--
-- transform the key into ASCII armored format
--
SET @keyASCII = dbo.PGP_Load_Key(@KEY)

Observe the properties of a .pgp key

In your business logic you can load the keys and store them into a database table in ASCII armored format for later use. In the example code below we are going to insert the data contained in the variable @keyASCII from the example above into a temporary table and will obtain information for the key : its User ID, is it private or public key, etc.:

CREATE TABLE #MyKeys(KeyID BIGINT,
			KeyHexID VARCHAR(8),
			UserID nvarchar(2000),
			IsPrivate bit,
			KeyAsciiText text)
GO
 
 
-- @keyASCII - from example above
 
 
INSERT INTO #MyKeys(KeyID, 
                KeyHexID,
		UserID,
		IsPrivate,
		KeyAsciiText) 
VALUES(dbo.PGP_Get_Key_Id(@keyASCII), 
      dbo.PGP_Get_Key_Hex_Id(@keyASCII),
	dbo.PGP_Get_Key_User_Id(@keyASCII),
	dbo.PGP_Get_Key_Is_Private(@keyASCII),
	@keyASCII);
SELECT * FROM #MyKeys;

Summary

In this chapter we have illustrated how to load an arbitrary .pgp key file into MS SQL Server and transform it into ASCII armored format. Below is a list of the used Transact-SQL routines and their descriptions: