Encryption… it’s one of the oldest methodologies that began thousands of years back during the time of the Greeks.
With advancement in engineering and technology in the 21st century and with larger and powerful supercomputers there’s a necessity to create stronger encrypting algorithms with an mathematical approach to the subject than a digital one.“Elliptical Curve Cryptography” being the most recent advancement in this field .The topic of encryption as a whole is very vast to cogitate and is out of the scope of this article. I will leave it to the reader to do more self-reading on latest available methodologies of encryption.
Encryption in SQL Server
Encryption in SQL Server comes in two forms
- TDE(Transparent Data Encryption)
- Column level encryption
TDE encrypts the entire database including the database backups whereas cell level encryption provides encryption at far more granular level i.e. at the column level. Both these methods are primarily certificate based.
In this article we will focus into cell level or the column level encryption where you may want to for example cipher a column in a SQL Server table that stores Credit card numbers or SSN or other types of sensitive data.
The biggest advantage with column level encryption is that you simply don’t have to develop your own encryption algorithm for its application but on the flip side the biggest disadvantage is that the schema should be modified to varbinary irrespective of the source data type and this change hurts the performance while querying the encrypted column. In SQL Server encryption of the data occurs at the page level on the disk however once these pages are moved to the memory buffer pool they’re decrypted and saved as clear text.
The supported algorithms for column level encryption and TDE are AES with 128,196,256 bit keys and three key triple DES. Implementing column level encryption in SQL Server is a simple four step method
- Create a Masterkey
- Create a certificate
- Create a certificate key and secure it with the certificate created earlier
- Encrypt the column with the symmetric key
In the following example we will encrypt a single value 1 using the above 4 steps. We will use the inbuilt AES_256 encryption algorithm for encryption.
- Create a Master Key with a password :
CREATE MASETR KEY ENCRYPTION BY PASSWORD=’#123$’’
- Create a certificate with a subject :
CREATE CERTIFICATE EncryptCer WITH SUBJECT=‘This my encryption’
- Create a symmetric key and cipher it with the certificate created earlier with an AES_256 encryption algorithm :
CREATE SYMMETRIC KEY EncryptKey WITH ALGORITHM=AES_256 ENCRYPTION BY CERTIFICATE EncryptCer
Note : You can query the sys.certificates and sys.symmetric_keys system catalogs to check the creation of the certificate and symmetric keys.
- Open the symmetric key with the encryption certificate :
OPEN SYMMETRIC KEY EncryptKey ENCRYPTION BY CERTIFICATE EncryptCer
Once the above four steps are done, use the KEY_GUID function that takes symmetric key (which was created in step 3) as a parameter and the value that needs to be encrypted. In this case we are encrypting value 1
DECLARE @MyVar UNIQUEIDENTIFIER
SET @MyVar=KEY_GUID (‘EncryptKey’)
SELECT ENCRYPTBYKEY (@var,CONVERT(VARBINARY(256),1))
The output of the above command will be a very strong encrypted value.
Decryption is done by using the inbuilt DECRYPTBYKEY function that takes the parameters of keys by which the original value was encrypted and is then converted back to the original data type of the encrypted value.
SELECT CONVERT (INT, DECRYPTBYKEY (ENCRYPTBYKEY (KEY_GUID (‘ EncryptKey’), CONVERT (VARBINARY (256), 1))))
The output of the above command is 1 which is the original value that was encrypted.
Always make sure that the symmetric key is closed.It can be done using the following command
CLOSE SYMMETRIC KEY EncryptKey
In the production environment one would not want to grant rights to the encryption certificates to all the users that exist in the database. To grant full rights to the key and certificate to a privileged user, use the following commands.
GRANT CONTROL ON CERTIFICATE:: [EncryptCer] TO [User]
GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [EncryptKey] To [User]
The performance takes a big hit on tables that has data saved in encrypted format. For example if a column stores SSN numbers and has a clustered index on it , querying this column will return data quite quickly due to presence of the clustered index(provided there is not much fragementation present on it). But if you would want to implement encryption on a column the data type has to be changed to varbinary which inadvertently will void the index.
A simple query on a column would change from this
SELECT * FROM YourTable wherever SSN=’XXX-XX-XXXX’
SELECT * FROM YourTable wherever CONVERT (NVARCHAR (30), DECRYPTBYKEY (SSN)) =’XXX-XX-XXXX’
In one of the performance tests it was observered that there was a performance degradation of as much as 20% on a very simple and basic query that was executed against a encrypted column.The performance inversely scaled against an increased workload which became more worst when the whole database was encrypted.
One way to get past the performance issue is to create another computed column which stores the hash values for the encrypted column and while querying use the checksum function to compare the input values to the encrypted computed column to return the matching resultsets.
Cell level encryption offers a rather more granular approach than TDE because it doesn’t cause the overhead of maintaining the security at a database level. If performance is of secondary importance then in built SQL Server Cell level encryption is one of the best choices and can be enforced in an exceedingly larger scope. Additionally the cell level encryption is available on all versions of SQL Server whereas TDE is available only with the Enterprise and Developer Editions.
Sachin Nandanwar, Senior SQL Developer – Avitas Technologies