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