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:
PGP_Load_Key | Transforms a .pgp key binary contents into an ASCII armored text format |
PGP_Get_Key_Id | Returns the bigint raw Key ID |
PGP_Get_Key_Hex_Id | Returns the hexadecimal Key ID from the lower 4 bytes of the raw Key ID |
PGP_Get_Key_User_Id | Returns the first (primary) User ID of a key |
PGP_Get_Key_Is_Private | Returns bit indicating is this a private key (1) or a public key (0) |