SQL SERVER
ENCRYPTION
Dr. SALAH SALEM BINDAHMAN
Introduction
Protecting data, both in storage and
during transmission, is important for the
integrity of your application.
Encryption is a process of taking a plain
text and converting it into unreadable
format called cipher text.
In any encryption process, there should
be an encryption algorithm and
encryption key.
Encryption Algorithm
Encryption algorithm is simply a
mathematical formula that describe how
to turn the data from plain text into
cipher text.
There are many encryption algorithms
offered by SQL server inlcudes:
DES
RC4
AES
..
Encryption Key
The key is the value that is used within that
formula to determine the actual output
based on the input.
Example : x + y = z
There are two kinds of keys:
Symmetric key: which uses the same value
for encryption and decryption (good for large
data, low security)
Asymmetric key: which uses different values
for encryption and decryption (good security,
but cost)
Database Master Key (DMK)
One of the first step you should take is
creating the database master key.
Database master key is a symmetric key
that encrypts all private keys within the
database,
It require control permission on the
database.
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘pa$$w0rD’
GO
Encryption Tools
There are three different ways that can
be used for encryption:
Symmetric key.
Asymmetric key.
Certificate.
For each one, we will see how to create it
and then how to use it.
Symmetric Key
Symmetric key offers an efficient model for being
able to encrypt large amount of data by using
the same key for encryption and decryption.
Syntax:
CREATE SYMMETRIC KEY name
WITH ALGORITHM = algorithm_name
ENCRYPTION BY mechanism
Mechanisms are:
Certificate certificate_name
Password = ‘password’
Symmetric key symmetric_key_name
Asymmetric key asym_key_name
Symmetric Key
To create symmetric key we use the
following:
CREATE SYMMETRIC KEY encrypt_card
WITH ALGORITHM = AED_128
ENCRYPTION BY PASSWROD = ‘PA$$W0RD’
GO
You can see all of your symmetric keys by
executing the following commands:
Select * from sys.symmetric_keys
GO
Asymmetric Key
Asymmetric key uses a pair of keys
rather than a single one.
One key is called public key and the
other one is called private key.
To create asymmetric key we use the
following:
CREATE ASYMMETRIC KEY NAME
WITH ALGORITHM = ALGORITHM
[ENCRYPTION BY PASSWORD = ‘PASSWORD’]
If you don’t specify a password, DMK will encrypt the private
key
Encrypting Data
It is very critical to choose the right data to be
encrypted (frequently queried column should
not be encrypted).
Before encrypting the data, the used key for
encryption must be opened first. Then it can
be used for encryption. After that it should be
closed.
OPEN SYMMETRIC KEY KEY_NAME;
ENCRYPTBYKEY(KEY_GUID(KEY_NAME),VALUE OR COLUMN_NAME);
CLOSE SYMMETRIC KEY KEy_NAME;
Encrypting Data
ALTER TABLE COSTUMER
ADD ENCRYPTEDCARD VARBINARY(128);
OPEN SYMMETRIC KEY ENCRYPT_CARD
ENCRYPTION BY PASSWORD = ‘PA$$W0RD’;
UPDATE COSTUMER
SET ENCRYPTEDCARD =
ENCRYPTBYKEY (KEY_GUID(‘ENCRYPT_CARD’),CARDNUMBER);
CLOSE SYMMETRIC KEY ENCRYPT_CARD;
Encrypting Data
In previous commands, we have added a new
column and then encrypt the values of
existing column and add the encrypted value
to EncryptedCard
the new column.
CardNumbe Name ID
r
0×00EEE065AF3D2 97754102 Ali 1
3EE
0×00EEE033CC567 34560923 Ahmad 2
FEE
We can use it in the insert command
INSERT INTO COSTUMER VALUES
(3,’SALAH’,913485944,ENCRYPTBYKEY(KEY_GUI(‘ENCRYPT_CARD’), 913485944));
Encrypting Data
We can decrypt the value by using the
function decryptByKey.
UPDATE COSTUMER
SET DECRYPTEDCARD =
DECRYPTBYKEY (ENCRYPTEDCARD);
In select statement
Select id, name,
convert(varchar,decryptbykey(encryptedcard))
From costumer;