Implementing PGP File Encryption in ASP.Net/C# Using GnuPG

I was recently asked to implement PGP encryption for a file before sending it to a vendor.  In performing my research, I found GnuPG to be the easiest solution to understand as well as posing the least amount of risk for implementation.  The great thing about GnuPG is that you can download it and the instructions are easy to follow for encrypting files.

Unfortunately, I also found are some challenges during the implementation.  The existing documentation on the web is also confusing and is old.  This post is an attempt to update the existing documentation to 2009, provide a working coded example, and give a quick and easy roadmap for implementing PGP in an ASP.Net project.

Step 1 – Download and install GnuPG (GPG)

The main GnuPG page is http://www.gnupg.org.  The Windows download can be found here.  The version I refer to in this post is GnuPG 1.4.10b.

Installing the GnuPG software is easy as it uses a standard installer. 

Note: Be careful when installing the application on the server.  Choose a Windows account that is generic to installations.  There is a mistake in the installation process where important application data is installed in the Application Data directory of the account performing the installation. 

You can find the files by installing GnuPG on your local machine and then looking in C:\Documents and Settings\[%Windows User Name%]\Application Data\GnuPG.  Several key files are installed there and you will need to reference this directory from the ASP.Net code.  This is the biggest ‘gotcha’ for the entire process.

---

Update (2011-01-12): For 64-bit servers, the directory is C:\Users\[user name]\AppData\Roaming\gnupg

---

Step 2 – Learn GPG

An excellent introduction to GPG has been written by Brendan Kidwell and can be found here.
You will find excellent instructions for installation and use in Windows and Linux.  Pay special attention to the GPG Cheat Sheet and make sure you successfully encrypt and decrypt files from the command line before proceeding with coding.
You will need to install any public keys needed for encryption before running the code, as well as understanding how to retrieve the name of the name of the key (using –list-keys) before running the C# below.

Step 3 – Implement the PGP Object

Following is the code that I use to encrypt files.  It is heavily indebted to an article at the Code Project on decrypting files by Kurt Mang.

---

using System;
using System.Diagnostics;
using System.IO;
using System.Security;

/// <summary>
/// File encryption wrapper.  Executes GNU PGP as described here:
/// http://www.glump.net/howto/gpg_intro
/// And downloaded from here:
/// http://www.gnupg.org/download/index.en.html#auto-ref-2
/// </summary>
public class PGP
{
/// <summary>
/// Path to the PGP software
/// </summary>
string _pgpPath = @"C:\Program Files\GNU\GnuPG\gpg.exe";

/// <summary>
/// The path to the PGP file can be changed (if needed) 
/// or executed
/// </summary>
public string PgpPath {
get { return _pgpPath; }
set { _pgpPath = value; }
}
/// <summary>
/// The home directory argument in GnuPG
/// </summary>
string _homeDir;
/// <summary>
/// The location of the PubRing and SecRing files
/// </summary>
public string HomeDirectory {
get { return _homeDir; }
set { _homeDir = value; }
}
/// <summary>
/// Public constructor stores the home directory argument
/// </summary>
public PGP(string homeDirectory) {
HomeDirectory = homeDirectory;
}

/// <summary>
/// Encrypts the file
/// </summary>
/// <param name="keyName">Name of the encryption file</param>
/// <param name="fileFrom">Source file to be encrypted</param>
/// <param name="fileTo">Destination file (after encryption)</param>
public bool Encrypt(string keyName, string fileFrom, string fileTo) {

/// File info
FileInfo fi = new FileInfo(fileFrom);
if(!fi.Exists) {
throw new Exception("Missing file.  Cannot find the file to encrypt.");
}

/// Cannot encrypt a file if it already exists
if(File.Exists(fileTo)) {
throw new Exception("Cannot encrypt file.  File already exists");
}

/// Confirm the existence of the PGP software
if(!File.Exists(PgpPath)) {
throw new Exception("Cannot find PGP software.");
}

/// Turn off all windows for the process
ProcessStartInfo s = new ProcessStartInfo("cmd.exe");
s.CreateNoWindow = true;
s.UseShellExecute = false;
s.RedirectStandardInput = true;
s.RedirectStandardOutput = true;
s.RedirectStandardError = true;
s.WorkingDirectory = fi.DirectoryName;

/// Execute the process and wait for it to exit.  
/// NOTE: IF THE PROCESS CRASHES, it will freeze
bool processExited = false;

using(Process p = Process.Start(s)) {
/// Build the encryption arguments
string recipient = " -r \"" + keyName + "\"";
string output = " -o \"" + fileTo + "\"";
string encrypt = " -e \"" + fileFrom + "\"";
string homedir = " --homedir \"" + HomeDirectory + "\"";
string cmd = "\"" + PgpPath + "\"" + homedir + recipient + output + encrypt;

p.StandardInput.WriteLine(cmd);
p.StandardInput.Flush();
p.StandardInput.Close();
processExited = p.WaitForExit(3500);
p.Close();
}
return processExited;
}

}

---

A couple of things should be noted about this object:

• The constructor takes a HomeDirectory argument.  This is the Application Settings directory where the GPG application files have been installed. 
• Obviously the Windows account that runs the ASP.Net code needs to have access to the executable as well as the ability to run executables and create files.
• The code checks for the existence of the GPG executable, the source file and the destination file.  The reason is that the application can hang without throwing an exception if anything goes wrong.

Some useful SQL Date functions

I have been working with dates lately and have come up with a few useful SQL Date functions lately. I find myself recreating these functions on all the projects, so I have saved below in the form of a script.

-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns the number of days in a month for the passed date/time
-- ============================================================================
CREATE FUNCTION [dbo].[fncDaysInMonth]
(
@date AS DATETIME
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(mm, DATEDIFF(mm, -1, @date), -1))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns a date from 3 integers
-- ============================================================================
CREATE FUNCTION dbo.fncDate
(
@Year INT,
@Month INT,
@Day INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, @Day - 1, DATEADD(mm, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Calculates the last day of the month
-- ============================================================================
CREATE FUNCTION [dbo].[fncLastDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, -DAY(DATEADD(m,1,@dt)), DATEADD(m,1,@dt))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Returns the first day of the month for a given date
-- ============================================================================
CREATE FUNCTION [dbo].[fncFirstDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, -DAY(@dt) + 1, @dt)
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-02-17
-- Description: Returns a smalldatetime with the time removed
-- ============================================================================
CREATE FUNCTION [dbo].[fncRemoveTime]
(
@dt AS DATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN CAST(DATEADD(month,((YEAR(@dt)-1900)*12)+MONTH(@dt)-1,DAY(@dt)-1) AS SMALLDATETIME)
END
GO

Debugging Print Statement

I am doing some Stored Procedure testing and trying to push performance as much as possible. One thing that helps is the ability to time how long individual statements take to execute. The print statement has to display the time down to the second or millisecond.

A useful print statement looks like this:

PRINT '[string value]: ' + CONVERT(varchar,GETDATE(),109)

How to Deploy and Test An SSIS Package

When working with SSIS, it is not immediately obvious how to deploy a package.  Following are my short notes on deploying an SSIS package*.

Deploy the Package

1. While in the Package designer, choose Project > [Package Name] Properties.  The Configuration manager dialog will appear.
2. Choose Deployment Utility from the tree.
3. Change the CreateDeploymentUtility option from False to True.  Note the DeploymentOutputPath variable.  Push OK to close the dialog.
4. Open the Solution Explorer and right-click on the .dtsx file and choose Properties.  Copy the Full Path variable and use it to find the bin\Deployment folder.
5. Locate the [Package Name].SSISDeploymentManifest file.  Double-click on the file and follow the steps outlined by the wizard to deploy the package.

Test the deployed Package

1. Open MSFT SQL Server Management Studio and choose Connect > Integration Services from the UI.  Choose the Server and connect.
2. The packages will be saved under the MSDB folder.  Right-click on the package to run it.

---

*  To re-deploy a package, follow steps 1-5 again.

ASP.Net as Crystal Reports data source

There are not many good articles on using an ASP.Net web source as a data source.  This post attempts to enumerate the settings and make the process straightforward.

Before starting, I want to make a couple of recommendations:

• In my experience, embedded schemas do not work.  That means that working with native types (such as DataSets) that have embedded schemas won’t work well.
• Since embedded schemas don’t work, use a simple XMLDocument object as your return value.
• You will need to write your own schema for service.  Again – keep the return XML simple.

The instructions for creating an Crystal Reports XML Data Source are as follows*:

1. Open the database expert.  Choose Create New Connection / XML.
2. XML (data source) type and location:  Choose enter in your service in the URL HTTP(S) XML URL textbox.  Check the box for Specify Schema File.  Click ‘Next’.  There are a couple of items to note here.
   • Crystal gives an option for choosing Use Web Service Data Source.  I cannot get this to work – don’t choose it.
   • The URL for the service is the .asmx path/service name.  For instance, if your service is getGrid on the web-apps server, the path is http://web-apps/services.asmx/getGrid.  Adjust the path as needed to your .asmx file.
3. HTTP Authentication for XML file:  This screen deals with security.  I typically work inside a corporate firewall without security, so I leave these fields blank.  If necessary, fill in the User Id and Password.
4. Schema file type and location:  I put my schema files on the server,  but usually test with a local XSD file.  Choose HTTP and the path, if the file is on your server.  Otherwise, if the file is local, choose Use Local Schema and point the path to the local schema.
5. HTTP Parameters:  Click the Add Property … button to add your parameters.  The Value can be left blank, but the Property must be filled in with the parameter names.  Service parameters are case sensitive, so make sure that the case matches.
6. Clicking Finish will take you back to the data screen.  You will be given an options for the XPaths to the data in your source.  If successful, you will then have data from a web service in your report.

---

* Tested on CR XI R2/CR 2008

Regular Expression List

In an earlier post, I showed some regular expressions I had found for manipulating strings in a PDF project.

The following is a list of other expressions I have developed for use on other projects. I plan to update this list as I create expressions.

Regular Expressions

---

Expression (C#):
@"[\(.\d+.\)]+$"

Purpose:
I used this expression to rename spreadsheets. This expression finds ([digits]) at the end of a string. Further code parses and increments the number inside the parentheses.

Return equation

The following equation calculates a return for 1 period:

((Previous Return + 100) * (Current Return / 100)) + Previous Return

Where:

• Previous Return is the previous period return
• Current Return is the current period return
  

Find the row counts for all the tables in a SQL Server 2005 database

Have you ever needed to investigate an application database and quickly determine where the records are?
The following script returns a rowset that contains all the table names of a SQL Server 2005 database along with the row counts.

---

-- declare the variables

DECLARE @i INT, -- integer holder

  @table_name VARCHAR(50), -- table name

  @sql NVARCHAR(800) -- dynamic sql

-- create the temp table. Initialize count column to -1

CREATE TABLE #t (

[name] NVARCHAR(128),

rows CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

-- Initialize i to run at least once

SET @i = 1

-- loop while rows are still being selected

WHILE (@i > 0)

BEGIN

 -- create the dynamic sql that updates the row counts

 -- for each table

SET @sql = 'INSERT #t ([name], rows, reserved, data, index_size, unused)

EXEC sp_spaceused ['+ @table_name + ']'

 -- execute the dynamic sql

 EXEC sp_executesql @sql

 -- find out the name of the next table 

 SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

  AND [name] NOT IN

     (

        SELECT [name]

FROM #t

     ) 

 -- stop looking if no rows are selected

 SET @i = @@ROWCOUNT

END

-- return the results

SELECT *

FROM #t

ORDER BY reserved DESC

DROP TABLE #t

Script to change object owners

I have recently started working with SQL Server 2005, and the first thing I noticed is that I cannot automatically set the owner of a script at creation time.  SQL Server 2005 automatically adds my user name as the object owner.

The following script, adapted from Scott Forsyth's blog, batches up the changes.  Change the name of the old owner (@old) and the new owner (@new) and it will change the ownership for all the database objects.

Script to update SQL Server Object Ownership

DECLARE @old nvarchar(30), @new nvarchar(30), @obj nvarchar(50), @x int; SET @old = 'domain\user-name'; SET @new = 'dbo'SELECT @x = count(*); FROM INFORMATION_SCHEMA.ROUTINES a WHERE   a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')   AND a.SPECIFIC_SCHEMA = @old; while(@x > 0)   BEGIN     SELECT @obj = [SPECIFIC_NAME]     FROM INFORMATION_SCHEMA.ROUTINES a     WHERE        a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')        AND a.SPECIFIC_SCHEMA = @old;      SET @x = @@ROWCOUNT - 1;     EXEC sp_changeobjectowner @obj, @new; END