Colum-level encryptions
Colum-level encryptions are introduced at Microsoft SQL Server 2005.
Here in this article I am trying to demonstrate how to implement the columns level encryption.
Column-level encryption is implemented as a series of built-in functions and a key management hierarchy.
1. EncryptByCert() and DecryptByCert()
Encrypts and decrypts data using the public key of a certificate to generate a private asymmetric key
Encrypts and decrypts data using the public key of a certificate to generate a private asymmetric key
2. EncryptByAsymKey() and DecryptByAsymKey()
Encrypts and decrypts data using an asymmetric key
Encrypts and decrypts data using an asymmetric key
3. EncryptByKey() and DecryptByKey()
Encrypts and decrypts data by using a symmetric key
Encrypts and decrypts data by using a symmetric key
4. EncryptByPassphrase() and DecryptByPassphrase()
Encrypts and decrypts data by using a passphrase to generate a symmetric key
Encrypts and decrypts data by using a passphrase to generate a symmetric key
Using a passphrase
In our first example we are going to encrypt a column using a passphrase. It is simple to implement but it is not secured.
Step-1 [ Our Base Table ]
CREATE TABLE tbl_CardInfo
(idno INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Cname VARCHAR(50) NOT NULL,
CardNo VARCHAR(16) NOT NULL)
VALUES ('Sukamal Jana', '1234567891234567'),
('Sangram jit', '1234567891254321'),
('Rajesh Das', '4514367891254321')
GO
Step-2 [ Now Create the Master Key ]
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
BEGIN
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'joydeep@123'
END
GO
Step-3 [ Convert data type as VARBINARY ]
As in our base table the "CardNo" columns contain data type "VARCHAR", it takes the clear text information. To encrypt it, we must convert the columns data type as "VARBINARY" data type.
SELECT * FROM tbl_CardInfo
idno Cname CardNo
1 Sukamal Jana 1234567891234567
2 Sangram jit 1234567891254321
3 Rajesh Das 4514367891254321
As we can see the card the "CardNo" Columns contains clear text.
SELECT idno,
Cname,
CardNo_encrypt = CONVERT(VARBINARY(256),CardNo)
INTO tbl_CardInfo_Encrypt
FROM tbl_CardInfo
WHERE 1=2
Step-4 [ Now copying data By using EncryptByPassphrase() ]
BEGIN
DECLARE @passphrase VARCHAR(MAX)='joydeep@123'
INSERT INTO tbl_CardInfo_Encrypt(Cname, CardNo_encrypt)
SELECT Cname,
CardNo_encrypt = EncryptByPassPhrase(@passphrase,CardNo)
FROM tbl_CardInfo
END
Step-5 [ Now use SELECT to display records ]
SELECT * FROM tbl_CardInfo_Encrypt
Output:
idno Cname CardNo_encrypt
1 Sukamal Jana 0x01000000481B8F.....27
2 Sangram jit 0x01000000B4DD5...69
3 Rajesh Das 0x01000000C6BE7A...9D
The card number columns are encrypted.
Step-6 [ Now to Decrypt it by using DecryptByPassphrase() ]
BEGIN
DECLARE @passphrase VARCHAR(MAX)='joydeep@123'
SELECT idno,
Cname,
CardNo_encrypt =CONVERT(VARCHAR(16),DecryptByPassPhrase(@passphrase, CardNo_encrypt))
FROM tbl_CardInfo_Encrypt
END
Output:
idno Cname CardNo_encrypt
1 Sukamal Jana 1234567891234567
2 Sangram jit 1234567891254321
3 Rajesh Das 4514367891254321
Using Certificates
Certificates are used as encryption keys, used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.
The key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key.
After you create the certificate, the next step is to create a symmetric key that will be encrypted by the certificate. You can use many different algorithms for encrypting keys.
A number of different algorithms can be employed for encrypting symmetric key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
The primary benefit of certificates is that they relieve hosts of the need to maintain a set of passwords for individual subjects. Instead, the host merely establishes trust in a certificate issuer, which may then sign an unlimited number of certificates.
Step-1 [ Creating Certificate ]
CREATE CERTIFICATE cer_Cardinfo
WITH SUBJECT = 'Credit Card Info'
GO
Step-2 [Create a symmetric key that will be encrypted by the certificate ]
CREATE SYMMETRIC KEY symKey_CardInfo
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE cer_Cardinfo;
GO
Step-3 [ Delete all the records from Table tbl_CardInfo_Encrypt ]
TRUNCATE TABLE tbl_CardInfo_Encrypt
Step-4 [ Symmetric key associated with the certificate to encrypt by EncryptByKey() ]
OPEN SYMMETRIC KEY symKey_CardInfo
DECRYPTION BY CERTIFICATE cer_Cardinfo
GO
INSERT INTO tbl_CardInfo_Encrypt
(
Cname,
CardNo_encrypt
)
SELECT Cname,
CardNo_encrypt = EncryptByKey(KEY_GUID('symKey_CardInfo'),CardNo)
FROM tbl_CardInfo
GO
SELECT * FROM tbl_CardInfo_Encrypt
idno Cname CardNo_encrypt
1 Sukamal Jana 0x0064EB22A...8B5D
2 Sangram jit 0x0064EB22A...B621A
3 Rajesh Das 0x0064EB22...7B0D
Step-5 [Specifies the appropriate certificate can retrieve the data by using DecryptByKey ]
OPEN SYMMETRIC KEY symKey_CardInfo
DECRYPTION BY CERTIFICATE cer_Cardinfo
GO
SELECT idno,
Cname,
CardNo = convert(nvarchar(MAX), DecryptByKey(CardNo_encrypt))
FROM tbl_CardInfo_Encrypt
Output:
idno Cname CardNo_encrypt
1 Sukamal Jana 1234567891234567
2 Sangram jit 1234567891254321
3 Rajesh Das 4514367891254321
Step-6 [ To CLOSE the Key ]
CLOSE SYMMETRIC KEY symKey_CardInfo
The keys defined in a database can be viewed through the system catalog table, sys.symmetric_keys
SELECT name,
pvt_key_encryption_type,
issuer_name,
subject,
expiry_date = CAST(expiry_date as DATE),
start_date = CAST(start_date as DATE)
FROM sys.certificates
Hope you like it.
