     Print Add To Favorites Email To Friend Rate This Article Verifying Signature Using SQL Server 2005
 page 3 of 5
by
Feedback
Views (Total / Last 10 Days): 26489/ 96
Article Contents:

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
--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),
)
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.

 « (Page 2) View Entire Article (Page 4) »    