SQL Server Data Encryption
SQL Server 2005 dan SQL Server 2008 menyediakan fitur eknripsi untuk melindungi data dari serangan hacker.
Hacker mungkin mampu untuk melakukan akses ke database maupun table, tetapi dengan memberi enkripsi mereka tidak akan memahami data dan menggunakannya. Saat ini sudah menjadi hal yang krusial untuk melakukan enkripsi data yang disimpan didalam database sama seperti keamanan pada saat transmisi data melalui jaringan antara client dan server.
Hirarki enkripsi dibedakan dalam 3 level sekuritu. Level-level tersebut menyediakan mekanisme yang berbeda pada saat melindungi data melalui jaringan maupub server lokal. Hirarki level yang berbeda memperbolehkan lebih dari 1 instan server untuk berjalan dalam 1 server.
Windows Level – Highest Level – Menggunakan Windows DP API untuk enkripsi
SQL Server Level – Moderate Level – Menggunakan Services Master Key untuk enkripsi
Database Level – Lower Level – Menggunakan Database Master Key untuk enkripsi
Sistem ini relatif lebih rumit dimana pengirim hanya dapat melakukan enkrip tetapi tidak dapat melakukan dekrip.
Sedangkan penerima disisi lain dapat menggunakan kuncinya untuk melakukan dekrip tetapi tidak dapat melakukan enkrip.
Cara lain untuk melakukan enkripsi data adalah melalui certificates. Sertifikat kunci public adalah sebuah signed statement digital yang berisi sebuah public key untuk mengidentifikasi pengguna, device, atau service yang berkaitan dengan private key yang cocok.
Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.
Dibawah ini adalah demo sederhana tentang proses enkripsi dan dekripsi yang dieksekusi menggunakan Symmetric Key dan algoritma enkripsi Triple DES.
/* Create Database */
USE master
GO
CREATE DATABASE EncryptTest
ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
GO
Pertama, buat sebuah table contoh dan diisi dengan data contoh kemudian kita akan melakukan satu dari 2 kolom di table tersebut.
/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
Kode sebelumnya akan mengembalikan hasil yang digambarkan pada gambar berikutnya.
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult1
Result of the SQL query
Setiap database bisa memiliki satu master key. Kunci master database adalah kunci simetris yang digunakan untuk melindungi kunci privat dari sertifikat dan kunci asimetris yang ada dalam database. Menggunakan algoritma Triple DES bersamaan dengan kata sandi yang disediakan pengguna untuk mengenkripsi kunci.
/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'SQLAuthority'
GO
Certificate digunakan untuk mengamankan kunci enkripsi, yang digunakan untuk melakukan enkripsi data di dalam database. SQL Server telah mempunyai kemampuan untuk men-generate self-signed X.509 certificate sejak versi 2005.
/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO
Kunci symetricdapat di enkripsi menggunakan beberapa pilihan seperti certificate, password, kunci symetric, dan kunci asymetric. Beberapa jenis algoritma dapat digunakan untuk melakukan enkripsi terhadap kunci. Algoritma-algoritma yang disupport diantaranya DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO
Sekarang tambahkan sebuah kolom dengan tipe varbinary, yang akan menyimpan nilai enkripsi untuk kolom kedua.
/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO
Sebelum kunci digunakan, perlu didekripsi menggunakan metode yang sama yang digunakan untuk mengenkripsi. Dalam contoh kami, kami telah menggunakan sertifikat untuk mengenkripsi kunci. Karena alasan yang sama, kami menggunakan sertifikat yang sama untuk membuka kunci dan membuatnya tersedia untuk digunakan. Setelah membukanya dan membuatnya tersedia untuk digunakan, kita dapat menggunakan fungsi enkripsi dan menyimpan nilai terenkripsi dalam database, di kolom EncryptSecondCol.
/* Update binary column with encrypted data created by certificate and key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO
Kita bisa menjatuhkan kolom SecondCol asli, yang sekarang kita enkripsi di kolom EncryptSecondCol. Jika Anda tidak ingin menjatuhkan kolom, Anda dapat menyimpannya untuk perbandingan data di masa mendatang saat mendekripsi kolom.
/* DROP original column which was encrypted for protect the data */
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO
------------------------------------------------------------------------------------------------------------------------
We can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data.
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult2
Hasil dari SQL Query sebelumnya
User yang terotorisasi dapat menggunakan function "decryptbykey" untuk melihata data asli dari kolom yang terenkripsi.
If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. An important point to bear in mind here is that the original column and the decrypted column should have the same data types. If their data types differ, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.
/* Decrypt the data of the SecondCol */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script sqlQueryResult3
If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few.
/* Clean up database */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE [master]
GO
DROP DATABASE [EncryptTest]
GO
Summary
Encryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.
Salam,
Hacker mungkin mampu untuk melakukan akses ke database maupun table, tetapi dengan memberi enkripsi mereka tidak akan memahami data dan menggunakannya. Saat ini sudah menjadi hal yang krusial untuk melakukan enkripsi data yang disimpan didalam database sama seperti keamanan pada saat transmisi data melalui jaringan antara client dan server.
Hirarki enkripsi dibedakan dalam 3 level sekuritu. Level-level tersebut menyediakan mekanisme yang berbeda pada saat melindungi data melalui jaringan maupub server lokal. Hirarki level yang berbeda memperbolehkan lebih dari 1 instan server untuk berjalan dalam 1 server.
Windows Level – Highest Level – Menggunakan Windows DP API untuk enkripsi
SQL Server Level – Moderate Level – Menggunakan Services Master Key untuk enkripsi
Database Level – Lower Level – Menggunakan Database Master Key untuk enkripsi
Terdapat dua jenis kunci yang digunakan dalam enkripsi:
Symmetric Key
Dalam cryptography symmetric, pengirim dan penerima pesan berbagi sebuah kunci yang sama yang digunakan untuk melakukan enkrip dan dekrip pesan. Hal ini relatif lebih mudah untuk diimplementasikan dan pengirimin maupun penerima keduanya dapat melakukan enkrip dan dekrip pesan.Asymmetric Key
Asymmetric cryptography, sering disebut sebagai Public-key cryptography, adalah sebuag sistem dimana pengirim dan penerima pesan mempunya pasangan kunci yaitu sebuah public key dan sebuah private key – yang digunakan untuk melakukan enkrip dan dekrip pesan.Sistem ini relatif lebih rumit dimana pengirim hanya dapat melakukan enkrip tetapi tidak dapat melakukan dekrip.
Sedangkan penerima disisi lain dapat menggunakan kuncinya untuk melakukan dekrip tetapi tidak dapat melakukan enkrip.
Cara lain untuk melakukan enkripsi data adalah melalui certificates. Sertifikat kunci public adalah sebuah signed statement digital yang berisi sebuah public key untuk mengidentifikasi pengguna, device, atau service yang berkaitan dengan private key yang cocok.
Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.
Dibawah ini adalah demo sederhana tentang proses enkripsi dan dekripsi yang dieksekusi menggunakan Symmetric Key dan algoritma enkripsi Triple DES.
/* Create Database */
USE master
GO
CREATE DATABASE EncryptTest
ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
GO
Pertama, buat sebuah table contoh dan diisi dengan data contoh kemudian kita akan melakukan satu dari 2 kolom di table tersebut.
/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
Kode sebelumnya akan mengembalikan hasil yang digambarkan pada gambar berikutnya.
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult1
Result of the SQL query
Setiap database bisa memiliki satu master key. Kunci master database adalah kunci simetris yang digunakan untuk melindungi kunci privat dari sertifikat dan kunci asimetris yang ada dalam database. Menggunakan algoritma Triple DES bersamaan dengan kata sandi yang disediakan pengguna untuk mengenkripsi kunci.
/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'SQLAuthority'
GO
Certificate digunakan untuk mengamankan kunci enkripsi, yang digunakan untuk melakukan enkripsi data di dalam database. SQL Server telah mempunyai kemampuan untuk men-generate self-signed X.509 certificate sejak versi 2005.
/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO
Kunci symetricdapat di enkripsi menggunakan beberapa pilihan seperti certificate, password, kunci symetric, dan kunci asymetric. Beberapa jenis algoritma dapat digunakan untuk melakukan enkripsi terhadap kunci. Algoritma-algoritma yang disupport diantaranya DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO
Sekarang tambahkan sebuah kolom dengan tipe varbinary, yang akan menyimpan nilai enkripsi untuk kolom kedua.
/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO
Sebelum kunci digunakan, perlu didekripsi menggunakan metode yang sama yang digunakan untuk mengenkripsi. Dalam contoh kami, kami telah menggunakan sertifikat untuk mengenkripsi kunci. Karena alasan yang sama, kami menggunakan sertifikat yang sama untuk membuka kunci dan membuatnya tersedia untuk digunakan. Setelah membukanya dan membuatnya tersedia untuk digunakan, kita dapat menggunakan fungsi enkripsi dan menyimpan nilai terenkripsi dalam database, di kolom EncryptSecondCol.
/* Update binary column with encrypted data created by certificate and key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO
Kita bisa menjatuhkan kolom SecondCol asli, yang sekarang kita enkripsi di kolom EncryptSecondCol. Jika Anda tidak ingin menjatuhkan kolom, Anda dapat menyimpannya untuk perbandingan data di masa mendatang saat mendekripsi kolom.
/* DROP original column which was encrypted for protect the data */
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO
------------------------------------------------------------------------------------------------------------------------
We can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data.
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult2
Hasil dari SQL Query sebelumnya
User yang terotorisasi dapat menggunakan function "decryptbykey" untuk melihata data asli dari kolom yang terenkripsi.
If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it. An important point to bear in mind here is that the original column and the decrypted column should have the same data types. If their data types differ, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.
/* Decrypt the data of the SecondCol */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script sqlQueryResult3
If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few.
/* Clean up database */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE [master]
GO
DROP DATABASE [EncryptTest]
GO
Summary
Encryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.
Salam,
