Knowledge Base

DidiSoft OpenPGP Library for .NET 1.7.14 was released today offering extended support for OpenPGP Web of Trust.

In previous versions of the library only setting own trust was available and plain signing of keys.

With this new release keys status can be checked by Trust amount and Verified status. We can also sign public keys with plain and trust signatures, with the option for specifying trust depth.

Check the online Tutorial for Web of Trust in order to learn this new functionality.

Upgrade notes

With this version of the library newly generated keys have Trust value of Ultimate automatically. Unfortunately if we have key pairs generated with previous versions of the library, they have Trust value Unknown. We can upgrade them with a one time execution of the code below:

C#

// ks1 is of type DidiSoft.Pgp.KeyStore
foreach (KeyPairInformation k in ks1.GetKeys())
{
      if (key.HasPrivateKey)
      {
            ks1.SetTrust(k.KeyIdHex, TrustLevel.Ultimate);
      }
}

VB.NET

' ks1 is of type DidiSoft.Pgp.KeyStore
For Each k As KeyPairInformation In ks1.GetKeys()
  If key.HasPrivateKey Then
    ks1.SetTrust(k.KeyIdHex, TrustLevel.Ultimate)
  End If
Next

DidiSoft OpenPGP Library for .NET can be used without any problems in Mono projects under Mac OSX and Linux.

The library DLL files that you have to reference in your Mono project are the ones located in the root of the \Bin folder.

A limitation for Mono developers is that they have to first install the library setup on a Windows machine in order to get the DLL files and then copy them on their Mac OSX or Linux development station.

A common scenario that we have evidenced in our technical support practice for DidiSoft OpenPGP Library for .NET is that some customers by mistake try to decrypt .pgp files with an OpenPGP public key.

Although the exception was of class DidiSoft.Pgp.Exceptions.WrongPrivateKeyException,  the exception text so far wasn’t very helpful and it stated: “Decryption of data encrypted using KEY-ID(s) : XXX failed, The provided key is not a valid OpenPGP private key.”.

As of version 1.7.11.11 the library was updated to check if the supplied key is a public key and in that case the exception will be “Decryption of data encrypted using KEY-ID(s) : XXX failed, The provided key is a Public Key (You need a Private Key for decryption!)“

In this article we are going to illustrate how to invoke DidiSoft OpenPGP Library for .NET from MS SQL Server (tm) stored procedures and T-SQL code.

We also provide a dedicated set of Transact-SQL routines in the product MsSqlPGP.

Note: This information applies to MS SQL Server 2005 and above. All the demonstrated code below should be executed from SQL Server Management Studio ™ or similar environment, if not specified otherwise.

Table of Contents

• Database configuration
• Registering the assemblies
• Wrapping the code in Stored procedures
• Invoking
• Upgrading

Database configuration

In order to allow execution of .NET CLR code in a database, a special setting has to be activated for it:

1
2
3

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;

The database must allow unsafe code:

1

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON;

Assembly registration

The next step is to register the DLL files of the library:

1
2
3
4
5
6
7

CREATE ASSEMBLY [BouncyCastle.CryptoExt]
FROM 'C:\Program Files (x86)\OpenPGP Library for .NET 1.7.10\Bin\BouncyCastle.CryptoExt.dll'
WITH PERMISSION_SET = UNSAFE;
 
CREATE ASSEMBLY [DidiSoft.Pgp]
FROM 'C:\Program Files (x86)\OpenPGP Library for .NET 1.7.10\Bin\DidiSoft.Pgp.dll'
WITH PERMISSION_SET = UNSAFE;

Wrapping the OpenPGP functionality in Stored procedures

In order to use the functionality provided by the library, we have to create wrappers as managed Stored procedures. Below is an example that exposes the basic encryption and decryption methods offered by the library:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
 
using DidiSoft.Pgp;
 
namespace MyStoredProcedures
{
    public partial class MsSqlPgp
    {
        [SqlProcedure()]
        public static void EncryptFile(SqlString data, SqlString publicKey, SqlString outFile)
        {
            PGPLib pgp = new PGPLib();
            pgp.EncryptFile(data.Value, publicKey.Value, outFile.Value);
        }
 
        [SqlProcedure()]
        public static void DecryptFile(SqlString data, 
                                       SqlString privateKey, 
                                       SqlString keyPassword, 
                                       SqlString outFile)
        {
            PGPLib pgp = new PGPLib();
            pgp.DecryptFile(data.Value, privateKey.Value, keyPassword.Value, outFile.Value);
        }
    }
}

Of course, the assembly where the above code resides must also be registered in the same database. Let’s assume that it is registered with the alias MyStoredProcedures. Each of its methods must be registered as a stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE PROCEDURE usp_PgpEncryptFile
@dataFile nvarchar(MAX),
@publicKey nvarchar(MAX),
@OUTFILE nvarchar(MAX)
AS EXTERNAL NAME [MyStoredProcedures].[MyStoredProcedures].EncryptFile;
 
CREATE PROCEDURE usp_PgpDecryptFile
@pgpFile nvarchar(MAX),
@privateKey nvarchar(MAX),
@keyPassword nvarchar(MAX),
@OUTFILE nvarchar(MAX)
AS EXTERNAL NAME [MyStoredProcedures].[MyStoredProcedures.MsSqlPgp].DecryptFile;

Invoking the wrapped Stored procedures

The invocation of the wrapped code is no different than any other stored procedure call:

1
2
3

EXEC usp_PgpEncryptFile 'c:\Test\mydata.txt', 'c:\Test\public.asc', 'c:\Test\output.pgp';
 
EXEC usp_PgpDecryptFile 'c:\Test\input.pgp', 'c:\Test\private.asc', 'my password', 'c:\Test\output.txt';

Upgrading the library

In order to upgrade the library to a newer version we have to first DROP any linked stored procedure, and afterwards DROP the assemblies, but in reverse order:

1
2

DROP ASSEMBLY [DidiSoft.Pgp];
DROP ASSEMBLY [BouncyCastle.CryptoExt];

After that we shall proceed as the normal setup.

Summary

This chapter illustrated how to invoke functionality from DidiSoft OpenPGP Library for .NET in MS SQL Server ™ hosted environment.

If you need a complete solution with Transact-SQL OpenPGP cryptography, check our dedicated product MsSqlPGP.