Verifying Signature Using SQL Server 2005
page 3 of 5
by Xianzhong Zhu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24505/ 41

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.


View Entire Article

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-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 2:42:38 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search