An Updated Simple Passphrase Generator

(Note: The original version of this work is published at http://aspalliance.com/703_A_Simple_Passphrase_Generator.all, this is a long overdue update)

Just about 9 years ago I was building a partner-facing reporting website, and I needed a way to generate passwords when partners were added by customer service (no public registration) as well as to generate new ones easily when a password needed to be reset.  I wanted to generate a passphrase, which is usually easier to remember than a random string of gibberish.  Some of the “more experienced” among us will recognize this format as AOL-style passwords, which were printed on the 3.5” floppies we received in the mail or our PC magazines of the day.

In 2014, we have nearly a decade of breaches and crappy passwords being stolen.  Even today, weak and obvious passwords are some of the most popular choices.  My hope is passphrases may become more of a standard, but I doubt it.  Some of the text of the original article is republished below; some of the links are broken, and I’ve replaced them where I could find a suitable alternative.

You can find the updated code in my BitBucket Git repo, at https://bitbucket.org/rjdudley/passphrase.  It’s pretty simple—one library project, a few tests in another project, and a console app to display the passphrases.  Use the library wherever it will run if you so wish, or fire up the console app anytime you need a good passphrase yourself.

Why Passphrases?

Perhaps first we should ask “What is a passphrase?”  Wikipedia may say it best:

A passphrase is a collection of ‘words’ used for access control, typically used to gain access to a computer system.

Passphrases were first proposed in 1981 by Sigmund Porter. Passphrases are distinguished from passwords by their virtue of being comprised of several words separated by spaces. Passphrases can satisfy even stringent security requirements, while being easier for the users to remember (http://technet.microsoft.com/library/cc512613.aspx). It’s this combination of complexity and ease of remembrance that make passphrases a good part of a password policy.

Our decision to use passphrases included another reason. By using passphrases when a user’s account is set up, we hoped to set an example to our users to use passphrases as well. We hoped that users would follow our example and choose passphrases they could remember easily, and that would be more than their dog’s name concatenated with a number 1. As a precedent, I cited that AOL has for years used multiple word passphrases as the login associated with all those floppies and CDs they send out. PGP and its variants also require using secure passphrases as your private key.

Recommended Passphrase Best Practices

With the intrinsic strength of some of the modern encryption, authentication, and message digest algorithms such as RSA, MD5, SHS and IDEA the user password or phrase is becoming more and more the focus of vulnerability. (http://www.totse.com/en/privacy/encryption/passch.html)

String passphrases are only one part of a comprehensive security policy. For additional security, you should include other best practices in your application’s login components. Microsoft makes a number of recommendations for Windows networks which are also applicable for ASP.NET applications (http://technet.microsoft.com/library/cc162924). These recommendations include:

  • Enforcing strong passwords
  • Ensure regular password changes
  • Maintain a history to prevent immediate reuse
  • Lock out accounts after a certain number of failed attempts

In a very good series of articles, Jesper Johansson reiterates many of these recommendations (http://technet.microsoft.com/library/cc512624.aspx), but disagrees about using account lockout policies. Several myths surrounding Windows passwords are addressed by Mark Burnett (http://online.securityfocus.com/infocus/1554/), and although focused on Windows passwords, some of the information is also applicable to ASP.NET applications. Designing a component that includes these recommendations is beyond the scope of this article, but you should familiarize yourself with these recommendations and incorporate the pertinent ones into your application.

Generating Passphrases

FAQ: How do I choose a good password or phrase?

ANS: Shocking nonsense makes the most sense. (http://virtualschool.edu/mon/Crypto/PGPPassPhraseFAQ.html)

There are a number of methods for generating passwords and passphrases. In this article, we’ll modify a method known as Diceware (http://world.std.com/~reinhold/diceware.html). This method consists of a numbered word list and five dice. Each word is assigned a 5-digit number, with only numbers 1-6 at each position, and covering every combination of numbers. The five dice are rolled, and the numbers are read from each face to form a 5-digit number. This number is cross-referenced with a word in the word list, which is then the first word in the passphrase. This process is repeated until the requisite length or number of words has been reached.

Instead of rolling dice, we’ll use pseudo-random number generators to simulate dice rolls. To make cross referencing easier, we’ll use the original wordlist loaded as a Dictionary object.  This version of the generator uses the RngCryptoServiceProvider to simulate the rolls of the dice.

A Simple Unit Test Script for SQL Server

As we develop applications, it often makes sense to put some functions in a SQL CLR assembly.  As these assemblies are deployed through test, beta and production environments we need an easy way to ensure the assemblies have been updated correctly and are functioning as designed.  Many things can go wrong—incorrect permissions, deploying the wrong version of an assembly, even assumptions we made in the business logic.  I’m a big fan of RedGate’s SQL Test plugin—it’s an attractive SSMS plugin which organizes and runs tests via an easy to read panel—and the tSQLt (a unit testing framework for SQL Serve) which SQL Test is based on.  My next post will show recreate the testing strategy shown here using SQL Test.

One downside to tSQt is that you have to install a number of stored procedures and functions into your database.  Depending on your choice of database, permissions in your database and willingness to add additional objects to your database which aren’t drectly related to the data, using tSQLt may not be an option.  Although we’re testing a user defined function, we could also test stored procedures and SQLCLR assemblies.  This technique is also not limited to SQL Server—I use this in both SQL Server and VistaDB.  As you build out your application, you may want to consider adding diagnostic pages (or screens) where you can execute these testing procedures should your application start throwing errors.

The function we’ll be testing will be simple—we’ll pass in an nvarchar, and return the same nvarchar with the word “wombat” added to the end.

Start by creating a custom function:

CREATE FUNCTION [AddWombat]
( @phrase NVarChar(50)
)
RETURNS NVarChar
AS
BEGIN
return @phrase + ' wombat'
END

Now it’s time for the test harness.  I’m using VistaDB, creating a stored procedure and using table variables, syntax supported by more recent versions of SQL Server.  You may need to optimize for the database you’re using.  The first table holds our test cases—the test name, the value we want to pass into our function, and the expected result.  We then loop through all the tests, calling the function we want to test, and updating the result.  Our result comparison is very simple, similar to an Assert.AreEqual.  With a little more work, additional test types could be added, and the test indicated in the test case.

create procedure TestAddWombat as
begin

DECLARE @testcases TABLE(TestNumber INT IDENTITY(1,1) PRIMARY KEY,
TestName NVARCHAR(100),
TestCase NVARCHAR(100),
Expected NVARCHAR(100),
Result NVARCHAR(20));

DECLARE @testname VARCHAR(100);
declare @testcase nvarchar(MAX);
DECLARE @expected NVARCHAR(MAX);
DECLARE @result NVARCHAR(max);

DECLARE @testCaseCount INT;
DECLARE @counter INT;

SET @counter = 1;

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_hello_wombat' , -- TestName - nvarchar(100)
N'Hello, ' , -- TestCase - nvarchar(100)
N'Hello, wombat'  -- Expected - nvarchar(100)
)

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_null' , -- TestName - nvarchar(100)
NULL , -- TestCase - nvarchar(100)
NULL -- Expected - nvarchar(100)
)

SELECT @testCaseCount = count(*) FROM @testcases

WHILE @counter <= @testCaseCount

BEGIN

SELECT @testname = TestName,
@expected = Expected,
@testcase = TestCase
FROM @testcases
WHERE TestNumber = @counter

SELECT @result = dbo.AddWombat(@testcase)

IF(@expected is NULL)
BEGIN
IF(@result is NULL)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
ELSE
BEGIN
IF(@expected = @result)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
SET @counter = @counter + 1;

END

SELECT * FROM @testcases

end

 

 

Running this sproc returns the following results:

 

image

 

From here, we have a very basic test harness which we can easily add additional test cases, and easily extend.  TDD is a best practice in any aspect of application development, but database development is often not treated as application development and is far behind adopting practices common to C# developers.  This script is a simple way to introduce some TDD into database development.