In recent past there are many projects around ETL just only Data transfer from the legacy databases. But because of security concerns some of the data needs to encrypted and pushed across the Server. Here I just want to give a brief detail. And just to help the beginners.
Cryptography can be implemented in the SQL Server. This article explains about the encryption and decryption of data in SQL Server 2008. Following are the steps that we need to follow. SQL Server 2008 supports AES_128 or AES_192 or AES_256 or TRIPLE_DES_3KEY encryption algorithms.
Step 1: Create a Master Key
The database level cryptographic feature in SQL Server depends on a database master key. There can be one master key per database and has to be created manually by administrators because it is not created automatically during installation.
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
By default master key is encrypted by using the Triple DES algorithm and a user-supplied password. This default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of ALTER MASTER KEY. A master key that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '23987hxJ#KL95234Chinna';
PASSWORD --> Specifies a password with which to encrypt or decrypt the database master key. Password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.
Example: Before creating Master Key Check if it already created or not; use the following query.
SELECT * FROM
sys.symmetric_keys WHERE
symmetric_key_id = 101
-- PASSWORD should meet the SQL Server BOX OS
-- Password policy
CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='Password!2';
Step 2: Create Certificate
A certificate is a database level securable that follows the X.509 standard.
CREATE CERTIFICATE can also load a certificate from a file or assembly. This statement can also generate a key pair and create a self-signed certificate. Private keys generated by SQL Server are 1024 bits long. Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 3,456 bits. The length of an imported private key must be an integer multiple of 64 bits.
CREATE CERTIFICATE Cert_Password
ENCRYPTION BY PASSWORD = 'Password!2'
WITH SUBJECT = 'Password protection',
EXPIRY_DATE = '12/31/2099';
Execute and check certificate creation
SELECT * FROM
sys.certificates;
Step 3: Create Symmetric Key
The symmetric key must be encrypted by using at least one of the following or multiple keys. Those are by Certificate or password or symmetric key or asymmetric key or PROVIDER.
When a symmetric key is encrypted with a password instead of the public key of the database master key, the TRIPLE DES encryption algorithm is used. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm. There many more options to create Symmantic key, but here I am showing one simple approach.
CREATE SYMMETRIC KEY Sym_password
WITH ALGORITHM = AES_256
ENCRYPTION BY
CERTIFICATE Cert_Password;
Check the database how it is been created
SELECT * FROM
sys.symmetric_keys WHERE
symmetric_key_id = 256
Step 4: Encrypt Data
Now it is time to encrypt some test data and see how it works. To encrypt the data first we have to open the Symmantic key and the use the Certificate to encrypt the data. Make sure to close the symmantic key.
If certificate is created with password then we'll need to use password to open the certificate.
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE
Cert_Password WITH PASSWORD
= 'Password!2';
INSERT INTO Security (UserID, Password)
VALUES ('schinna',ENCRYPTBYKEY(KEY_GUID(N'Sym_password'), 'Chinna Srihari'))
CLOSE SYMMETRIC KEY Sym_password;
Step 5: Decrypt Data
Now we see how to decrypt the same data back to text. To decrypt the data first we have to open the Symmantic key and the use the Certificate to encrypt the data. Make sure to close the symmantic key.
If certificate is created with password then we'll need to use password to open the certificate.
OPEN SYMMETRIC KEY Sym_password
DECRYPTION BY CERTIFICATE
Cert_Password WITH PASSWORD
= 'Password!2';
SELECT CAST(DECRYPTBYKEY([Password]) as varchar(200))
FROM Security
CLOSE SYMMETRIC KEY Sym_password;