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
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; |
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
The database must allow unsafe code:
1
| ALTER DATABASE MyDatabase SET TRUSTWORTHY ON; |
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; |
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);
}
}
} |
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; |
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'; |
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]; |
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.
