Verifying Signature Using SQL Server 2005
 
Published: 27 Oct 2006
Abstract
In this article you will learn how to verify signatures using SQL Server 2005 with the help of code samples.
by Xianzhong Zhu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 26953/ 82

Introduction

Encryption is a mechanism to help protect data.  Encryption helps provide data confidentiality by obfuscating the data so that only authorized people can access and read the data.  SQL Server 2005 provides many security-related features that help protect the data in an organization.

In this article after looking at the safety architecture in SQL Server 2005, you will see detailed examples of how to achieve verifying signature using Asymmetric Encryption in SQL Server 2005.

Examining SQL Server 2005 Cryptography architecture

SQL Server 2005 implements a framework to help protect encryption keys by using an encryption key hierarchy, as shown in Figure 1.  In this hierarchy each layer encrypts the layers that are below it.

Figure 1 – SQL Server 2005 encryption key hierarchy.

As seen from Figure 5, The Data Protection API (DPAPI) is at the top of this encryption key hierarchy, which in fact is a pair of function calls that provide operating system–level data protection services to user and system processes.  Instances of SQL Server 2005 encrypt their primary keys by the DPAPI service of Windows and then they encrypt the primary key of each database using these primary keys.  Each database's primary key is then used to encrypt the certificates or asymmetric keys inside the database.  Later, the symmetric keys can continue to be encrypted through these certificates and asymmetric keys.  This forms a full key management architecture.

Asymmetric Key

An asymmetric key consists of a private key and the corresponding public key.  Each of these keys can decrypt data that the other keys encrypt.  In SQL Server 2005 the asymmetric keys are public and private key pairs.  The public key does not have a particular format as a certificate would have and the developer cannot export it to a file. 

In SQL Server 2005 a developer can encrypt asymmetric keys by using the following two methods:

1. A user key that is derived from a user-supplied password

2. The Database Master Key

Symmetric Key

A symmetric key is a single key that is used for both encryption and decryption.  The encryption and decryption operations perform quickly with symmetric encryption.  Therefore, symmetric encryption is well suited for encrypting bulk data in SQL Server 2005.

Generally speaking, symmetric encryption is of great efficiency but of less safety, while asymmetric encryption is much more secure but with no efficiency.  We should, therefore, encrypt the key data, such as a symmetric key, with asymmetric encryption policy and then use symmetric key to encrypt large amounts of data.

A simple example

As we have seen, an asymmetric encryption is to encrypt the public key of the recipient who holds the only private key and, therefore, only the recipient can unlock the door.

We can use asymmetric encryption to verify the signature in SQL Server 2005 when both the signature and the verifying by an asymmetric key require the integrity of the data.  The following steps are commonly taken to perform electric signing.

·         Calculate the hash value of the data required to be verified

·         Encrypt the hash value with the sender’s private key

While verifying the signature you need to complete the following steps.

·         The recipient uses the public key of the sender to decrypt the encrypted hash value

·         Calculate the needed signed data using the same hash algorithm and compare the two values.  If they are the same then it means the data comes from the man who holds the private key and does not modify even a byte of the data.

Listing 1 gives a short example of this ability in SQL Server 2005.

Listing 1 – Achieving asymmetric encryption/decryption and signature

 
--Test the encryption/decryption and signature using asymmetric key
USE TempDB
GO
--Create the asymmetric key pair
CREATE ASYMMETRIC KEY keyRSA 
      WITH ALGORITHM = RSA_2048 
      ENCRYPTION BY PASSWORD = N'password'--Create a table to hold the encrypted data and signature
--DROP TABLE tblTest
CREATE TABLE tblTest(C1 INT IDENTITY(1,1) PRIMARY KEY,Org NVARCHAR(100), 
Encrypt VARBINARY(MAX),Signature VARBINARY(MAX))
GO
 
--After we encrpt the data with key pair created above and signing, we store the result into the table
DECLARE @str NVARCHAR(100)
SET @str=N'Hello RSA 2048'
INSERT INTO tblTest values(
      @str,
      EncryptByAsymKey(AsymKey_ID('keyRSA'), @str),
      SignByAsymKey(AsymKey_Id('keyRSA'),@str, N'password')
)
GO
SELECT * FROM tblTest
 
 
--
--Decrypt and verify the signature
--
SELECT Org,CONVERT(nvarchar(100),
      DecryptByAsymKey(AsymKey_Id('keyRSA'),CAST(Encrypt AS NVARCHAR(MAX)),   N'password')) AS Decrypt,
      CASE WHEN VerifySignedByAsymKey(AsymKey_Id('keyRSA' ),Org,Signature) = 1
      THEN
            N'The data is correct!'
      ELSE
            N'The data is to be modified!'
      END
      as IsSignatureValid
FROM tblTest
--Modify the data purposely to verify the signature
UPDATE tblTest SET Org=Org + 'a'
 
SELECT Org,CONVERT(nvarchar(100),
      DecryptByAsymKey(AsymKey_Id('keyRSA'),CAST(Encrypt AS NVARCHAR(MAX)),   N'password')) AS Decrypt,
      CASE WHEN VerifySignedByAsymKey(AsymKey_Id('keyRSA' ),Org,Signature) = 1
      THEN
            N'The data is correct!'
      ELSE
            N'The data is to be modified!'
      END
      as IsSignatureValid
FROM tblTest
 
--Clean up
DROP TABLE tblTest
DROP ASYMMETRIC KEY keyRSA

Obviously, we build the asymmetric key pair using CREATE ASYMMETRIC KEY and then specify the encryption/decryption algorithm.  Next, we use the EncryptByAsymKey/DecryptByAsymKey to encrypt/decrypt the data.  Finally, we use SignByAsymKey/VerifySignedByAsymKey to sign and achieve verification, during which the public/private key is stored in the database.

Achieve verifying signature using Asymmetric Encryption

In this section let us look into a simple complete web application, FormAuthDemo, which is ASP.NET 2.0 based and uses SQL Server 2005 to persist the logging-in users' information.

This core part of the following should be step 3, where we use asymmetric encryption to encrypt the hashed password and decrypt the hashed password later.  

1. Create a Web Application with a Login Page

This procedure creates a simple C# Web application that contains a login page which allows a user to enter a username and password.

Start Visual Studio 2005 and create a new C# ASP.NET Web application called SQLAuthTest.  Then add a few simple controls to Default.aspx to create a simple login form.  Your final Web page in Design View should look like the one illustrated in Figure 2.

Figure 2

Do not forget to set the TextMode property of the txtPassword to Password.

Also note that we use Forms authentication mode in this sample.  For more details please refer to the downloaded sourcecode of the article.

2. Generate a Hash and Salt value

This procedure adds two utility methods to your Web application; one to generate a random salt value and the other to create a hash based on a supplied password and salt value.

Open Default.aspx.cs and add the following using statements to the top of the file beneath the existing using statements.

Listing 2

//……
using System.Security.Cryptography;
using System.Web.Security;

Add the following static method to the helper class AuthenticationUtilities to generate a random salt value and return it as a Base 64 encoded string.

Listing 3

private static string CreateSalt(int size)
{
  // Generate a cryptographic random number using the cryptographic
  // service provider
  RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
  byte[]buff = new byte[size];
  rng.GetBytes(buff);
  // Return a Base64 string representation of the random number
  return Convert.ToBase64String(buff);
}

Add the following static method to generate a hash value based on a supplied password and salt value.

Listing 4

private static string CreatePasswordHash(string pwd, string salt)
{
  string saltAndPwd = String.Concat(pwd, salt);
  string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile
    (saltAndPwd, "SHA1");
  hashedPwd = String.Concat(hashedPwd, salt);
  return hashedPwd;
}

3. Create a User Account Database

This procedure is the most attractive part of the whole story.  First logon to SQL Server Management Studio and connect to your local SQL Server 2005.  In this scenario I use the Windows Authentication Mode.  Click the New Query icon and then enter the following SQL script. Remember to replace “LocalMachine” with your own computer name (for the downloaded sourcecode, I use my test computer name “zxzcom”) towards the end of the script.

Listing 5

USE master
GO
-- create a database for the security information
IF EXISTS (SELECT * FROM   master..sysdatabases WHERE  name =’UserAccounts’)
      DROP DATABASE UserAccounts
GO
CREATE DATABASE UserAccounts
GO
USE UserAccounts
GO
 
CREATE TABLE Users (
      UserName varchar(40) NOT NULL ,
      EncryptedPasswordHash varbinary(8000) NOT NULL ,
      salt varchar(10) NOT NULL,
      CONSTRAINT PK_Users PRIMARY KEY  CLUSTERED
       
      UserName
      )  ON [PRIMARY] 
) ON [PRIMARY]
GO
--
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘p@ssw0rd’;
CREATE ASYMMETRIC KEY Asym_Password
WITH ALGORITHM = RSA_512;
go
-- create a stored procedure to register user details
CREATE PROCEDURE RegisterUser
@userName varchar(40),
@passwordHash varchar(50),
@salt varchar(10)
AS
      BEGIN
            Declare @encrystaff varbinary(8000);
            -- here,we use asymmetric encryption to cipher the hashed password
            SET @encrystaff=EncryptByAsymKey(AsymKey_ID(‘Asym_Password’),                 @passwordHash);
            INSERT INTO Users VALUES(@userName,@encrystaff , @salt)
      END
GO
-- create a stored procedure to retrieve user details
CREATE PROCEDURE LookupUser
@userName varchar(40)
AS
      BEGIN
            SELECT CONVERT(nvarchar(50),
                  -- use the private key inside the asymmetric key pair
                  -- created above to decipher the hashed password.
                  DecryptByAsymKey(AsymKey_ID(‘Asym_Password’), EncryptedPasswordHash)  ), salt 
            FROM Users
            WHERE UserName = @userName
      END
GO
 
exec sp_grantlogin [LocalMachine\ASPNET] 
-- Add a database login for the UserAccounts database for the ASPNET account
exec sp_grantdbaccess [LocalMachine\ASPNET]
-- Grant execute permissions to  stored procs
grant execute on LookupUser to [LocalMachine\ASPNET]
grant execute on RegisterUser to [LocalMachine\ASPNET]

Now, run the query to create the UserAccounts database.

4. Use ADO.NET to Store Account Details in the Database

Return to VS2005 and double-click the Register button on the Web form to create a button click event handler.  Then add the following code to the handler method.

Listing 6

int saltSize = 5;
string salt =AuthenticationUtilities.CreateSalt(saltSize);
string passwordHash =
AuthenticationUtilities.CreatePasswordHash(txtPassword.Text, salt);
try
{
  AuthenticationUtilities.StoreAccountDetails(txtUserName.Text, passwordHash, salt);
}
catch (Exception ex)
{
  lblMessage.Text =” Execption in btnLogin_Click event. ”;
}

Next, create the following StoreAccountDetails utility method. First, add the following using statement at the top of the file beneath the existing using statements.

Listing 7

using System.Data.SqlClient;
//Stores the account details in a SQL table-----UserAccounts
internal static void StoreAccountDetails(string userName, string passwordHash,
  string salt)
{
  using(SqlConnection conn = new SqlConnection("Server=localhost;" +
    "Integrated Security=SSPI;" + "database=UserAccounts"))
  {
    SqlCommand cmd = new SqlCommand("RegisterUser", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sqlParam = null;
 
    sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar, 40);
    sqlParam.Value = userName;
    sqlParam = cmd.Parameters.Add("@passwordHash", SqlDbType.VarChar, 50);
    sqlParam.Value = passwordHash;
 
    sqlParam = cmd.Parameters.Add("@salt", SqlDbType.VarChar, 10);
    sqlParam.Value = salt;
 
    try
    {
      conn.Open();
      cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      throw new Exception("Execption StoreAccountDetails. " + ex.Message);
    }
    finally
    {
      conn.Close();
    }
  }
}

Here we use ADO.NET to connect to the UserAccounts database and to store the supplied username, password hash and salt value in the Users table.

5. Authenticate User Credentials Against the Database

Return to the Default.aspx.cs and add the VerifyPassword helper method as shown in the following code.

Listing 8

//Method that indicates whether
//the supplied username and password are valid
internal static bool VerifyPassword(string suppliedUserName, string
  suppliedPassword)
{
  bool passwordMatch = false;
  using(SqlConnection conn = new SqlConnection("Server=localhost;" +
    "Integrated Security=SSPI;" + "database=UserAccounts"))
  {
    SqlCommand cmd = new SqlCommand("LookupUser", conn);
    cmd.CommandType = CommandType.StoredProcedure;
 
//one parameter only: parameters passed to the StoredProcedure
    SqlParameter sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar,
      40);
    sqlParam.Value = suppliedUserName;
    try
    {
      conn.Open();
      using(SqlDataReader reader = cmd.ExecuteReader())
      {
        reader.Read();
        string dbPasswordHash = reader.GetString(0);
        string salt = reader.GetString(1);
// Now take the salt and the password entered by the user
// and concatenate them together.
        string passwordAndSalt = String.Concat(suppliedPassword, salt);
// Now hash them
        string hashedPasswordAndSalt =
          FormsAuthentication.HashPasswordForStoringInConfigFile
          (passwordAndSalt, "SHA1");
// Now verify them. Returns true if they are equal
        passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash);
      }
    }
    catch (Exception ex)
    {
      throw new Exception("Execption verifying password. " + ex.Message);
    }
    finally
    {
      conn.Close();
    }
  }
  return passwordMatch;
}

6. Test the Application

This procedure is fairly direct, just follow the steps below.

1. Open the Default form and double-click the Login button to create a corresponding btnLogin_Click event handler.

2. Add the following code to the Login button click event handler.

Listing 9

bool passwordVerified = false;
try
{
  passwordVerified = AuthenticationUtilities.VerifyPassword(txtUserName.Text,
    txtPassword.Text);
  if (passwordVerified)
  {
    FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, false);
  }
  else
  {
    Response.Redirect("Default.aspx");
  }
}
 
catch (Exception ex)
{
  lblMessage.Text = string.Format(CultureInfo.InvariantCulture, ex.Message);
  return ;
}
 
if (passwordVerified == true)
{
// The user is authenticated
  lblMessage.Text = "Logon successful: User is authenticated";
  ;
}
 
else
{
  lblMessage.Text = "Invalid username or password";
  ;
}

Here we call the VerifyPassword method and display a message based on whether or not the supplied user name and password are valid.

3. In Solution Explorer, right-click Default.aspx and then click View in the Browser.  Enter a user name and password and then click Register.

Note that you may use SQL Server 2005 Management Studio to view the contents of the Users table.  You should see a new row for the new user name together with a generated asymmetrically encrypted password hash.

4. Return to the Login Web page, re-enter the password, and then click Login.  You should see the message "Logon successful: User is authenticated."

5. Now enter an invalid password (leaving the user name the same).  You should see the message "Invalid username or password."

That is about the whole story!

Conclusion

In a real web farm scenario, security relates to various aspects, ranging from Windows to Database Server to Application Server and even to web developing techniques.  In other words, it is highly demanding that developers familiarize themselves with these aspects as much as possible.

In this article you learned how to achieve verifying signature using Asymmetric Encryption supported by SQL Server 2005 through a simple but complete web project.  This, however, is just a tiny part of security in SQL Server 2005.  It is only through the efforts of all developers, management staffs and final users that web projects can be built with more security and higher performance.



User Comments

Title: SQL Course Help   
Name: Christi parks
Date: 2013-01-18 8:20:59 AM
Comment:
I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
And has anyone studied from this course www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help
Title: Encrypt/Decrypt password   
Name: Keerthi
Date: 2009-07-13 2:30:05 AM
Comment:
Hi,
Nice Article!

Iam using the same in my application..

but if the user forgot his password then how to Reset/decrypt the password in database. Please help me...
Title: ASP.NET Signature Capture Control   
Name: Online Signatures
Date: 2008-08-25 11:14:39 PM
Comment:
Online Signature Capture ASP.NET Application http:// mysignature. brinkster. net (For ASP.NET 1.1 2.0) is an ASP.NET Web Custom Control that creates an online signature box to capture signature. Users can now Sign Documents Online using mouse as a pen!. Your clients can use this signature box to sign or upload their signature in your web page. Pure .NET code and HTML based, no plugins, activeX required. It is compatible with all browsers and available with full source code @ $19.00 no extra license fees required.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:48:37 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search