PageViews Last Month

Thursday 15 October 2015

SQL server Encryption – Transact SQL Functions.

In the previous blogs we came across various encrypt functions like ENCRYPTBYKEY(),ENCRYPTBYASYMKEY(),ENCRYPTBYCERT().
We also saw the corresponding decrypt functions like DECRYPTBYKEY(),DECRYPTBYCERT(),DECRYPTBYASYMKEY(),DECRYPTBYCERT(),
We shall be covering a few more in this blog 
1)  ENCRYPTBYPASSPHRASE() 
This function accepts the data of nvarchar type and after encryption the data is returned in the varbinary format
You could look up the syntax and arguments on the Microsoft website. @passphrase & @cleartext are the mandate arguments. The passphrase is an important argument here. Until now all the functions we have seen require an alphanumeric password for either the keys or the certificates that we created. In scenarios where we do not wish to implement the windows policy of password rather we go for a phrase that will be used for encryption this function comes in handy. After all we are good with mnemonics like “My very educated mother just showed us nine planets “ .... rather than recalling planets like this

Planet 1 = Mercury

Planet 2 = Venus

and so on so forth.



2)  DECRYPTBYPASSPHRASE()
This fucntions access the varbinary encrypted data that was derived from ENCRYPTBYPASSPHRASE() function. And when the same phrase is passed the data is decrypted in the nvarchar form.
Not much of rocket science required here to use this function. It’s similar to DECRYPTBYKEY() or DECRYPTBYCERT() function. We are however spared from opening any certificates or keys, rather we just recall the phrase that we used. Simple enough just like ENCRYPTBYPASSPHRASE() function's the mandate arguments are first 2. @passphrase & @ciphertext.
Let’s see this with a use of example
USE TESTDB
GO

DECLARE @DATAforENCRYPTION NVARCHAR(4000)
DECLARE @ENCRYPTEDdata VARBINARY(8000)
DECLARE @PHRASE VARCHAR(MAX)
DECLARE @DECRYPTEDdata NVARCHAR(4000)

SET @DATAforENCRYPTION = 'SECRET CODES ARE NEVER REVEILED'
SET @PHRASE = 'ILL ACT AS THE ENCRYPTING KEY'


SET @ENCRYPTEDdata = ENCRYPTBYPASSPHRASE(@PHRASE,@DATAforENCRYPTION)
       PRINT 'DATA AFTER ENCRYPTION IN VARBINARY FORM'
       PRINT @ENCRYPTEDdata

SET @DECRYPTEDdata = DECRYPTBYPASSPHRASE(@PHRASE,@ENCRYPTEDdata)
       PRINT 'DATA AFTER ENCRYPTION IN NVARCHAR FORM AGAIN'
       PRINT @DECRYPTEDdata

The only drawback here is if you know the location or column within the table where data is encrypted and stored and if someone gets hold of your passphrase.....; well nothing more is left to decrypt then. Often you may want to club this with TDE.
That leaves us with an explanation of 2 functions
3)  DECRYPTBYKEYAUTOASYMKEY()
This function is used to decrypt data that is encrypted using symmetric keys which in turn is protected by an asymmetric key. 
Its combines the functionality of code segments OPEN SYMMETRIC KEY & DecryptByKey() in one function DECRYPTBYKEYAUTOASYMKEY()

4)  DECRYPTBYKEYAUTOCERT().


This function is used to decrypt data that is encrypted using symmetric keys which in turn is protected by certificate. 
Its combines the functionality of code segments OPEN SYMMETRIC KEY & DecryptByKey() in one function DECRYPTBYKEYAUTOCERT().

None other than MVP K Brian Kelly has already described them beautifully in the below blogs.
 I'd suggest you refer to them once. 

This being the last blog for this series covers the 5 techniques to encrypt data within SQL server. However that's not all. Its the responsibility of the person encrypting the data to ensure the keys, certificates are backed up accordingly and in a timely fashion. Just like stored procedures and tables access will have to be granted for users to view the encrypted data or decrypt the data. The very sound mechanism provided by SQL server to protect data can become a nuisance if access is not granted wisely and keys or certificates are lost during decrypt or restore operations.

Tuesday 13 October 2015

SQL server Encryption – Transparent Data Encryption

In the previous blogs we saw the use of certificates to encapsulate symmetric keys. How it benefits the user by maintaining the password rather than user having to mention it every time a key is opened for use.
In all the above methods we are protecting data at a granular level like tables and columns. 


What if we wish to protect data at the higher level?  Let’s say no one should be able to see any data in my database until I authorize to do so. I wish to protect all my data which implies all the data that resides in my database files and log files. From SQL server 2008 this functionality was provided through Transparent Data Encryption. As per definition by Microsoft, TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.

Isn’t it amazing even if accidentally someone were to get access to the data and log files they would be able to attach the database and view the information. Or let’s say they get access to the backup files they still wouldn’t get the data by a normal restore operation.

The encryption of files and decryption happens by SQL server in memory. The data at rest is cipher text. It does impact the performance while these operations are performed in memory. Pages are first decrypted and then the data is provided to the receiving client.

How it happens

1)  CREATE MASTER KEY

USE TESTDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1211!pwD##';
GO

2) CREATE CERTIFICATE

CREATE CERTIFICATE TestCertificate
 WITH SUBJECT = 'For TDE for TESTDB’,
 GO

3) CREATE DATABASE ENCRYPTION KEY
     
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestCertificate
 GO


4) ALTER DATABASE SET ENCRYPTION ON

ALTER DATABASE TESTDB
SET ENCRYPTION ON
GO




Saturday 10 October 2015

SQL server encryption – Certificates

In the previous blogs we saw how to encrypt data using symmetric and asymmetric keys.
We saw how to use functions like ENCRYPTBYKEY, DECRYPTBYKEY & ENCRYPTBYASYMKEY,DECRYPTBYASYMKEY.


Its important to note that while using these functions we need to ensure the password is included correctly. This involves a risk.In-case we lose the password, the keys render useless. Apart from this the password would have to be maintained in case of changes.
 In this blog we shall see something similar but with the use of SQL server certificates.
Certificates encapsulate data like password, user information, validity,etc. Their prime benefit is relieving the hosts of the need to maintain a set of passwords for individual subjects.

1) Create Database Master Key
2) Create Certificate
3) Create Key using Certificate
4) Encrypt Data
  • Open Key
  • Encrypt Data
  • Close Key
5)  Using EncryptByCert(), DecryptByCert() Functions

1) Create Database Master Key

USE TESTDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '456##456hhhh';
GO

2) Create certificate

Now we proceed with creating a ceritificate which will serve as our container for the key.

  USE TESTDB;
  GO

   CREATE CERTIFICATE SymKeyCertificate
   ENCRYPTION BY PASSWORD = '456##456hhhh'
   WITH SUBJECT = 'Symmetric Key Certificate',
   EXPIRY_DATE = '10/31/2020';
   GO

The reason we created a database master key here is if we dint mention the password the certificate would be encrypted by the DMK.

3) Create symmetric key using certificate

    CREATE SYMMETRIC KEY SymKeyCertTest
    WITH ALGORITHM = TRIPLE_DES
     ENCRYPTION BY CERTIFICATE SymKeyCertificate;
     GO

We shall be using a different table structure this time

  USE [TESTDB]
  GO

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO

CREATE TABLE [dbo].[CertificateDemo1](
       [EmpId] [int] NOT NULL,
       [EmpName] [nchar](50) NULL,
       [EmpSal] [varchar](10) NOT NULL
) ON [PRIMARY]


Data is as follows

EmpId
EmpName
EmpSal
1
abc                                              
5000
2
def                                              
15000
3
hij                                              
25000

4) Encrypt Data

We shall be encrypting and showing the data using the same functions for symmetric keys but this time we skip mentioning the password instead we use certificates.

USE TESTDB;
GO

-- Create symmetric key using the certificate       
OPEN SYMMETRIC KEY SymKeyCertTest
DECRYPTION BY CERTIFICATE SymKeyCertificate

-- Encrypt the data 
GO
SELECT empID,empname,
EncryptByKey(Key_GUID('SymKeyCertTest'), empsal) as "Encrypted Salary"
FROM CertificateDemo1
      
--Close symmetric key 
GO
CLOSE SYMMETRIC KEY SymKeyCertTest;
      
Im leaving the use of DecryptByKey()  function here. You can try that on your own.

5)  Using EncryptByCert(), DecryptByCert() Functions

If one wishes to use the certificate directly you can try the below options as well

EncryptByCert() function

ALTER TABLE CertificateDemo1
ADD certcolumn varbinary(MAX) NULL
GO

UPDATE certificatedemo1
SET certcolumn = (EncryptByCert(cert_id('SymKeyCertTes'),empsal))

DecryptByCert() function

SELECT empid,empname,convert(varchar(max), DecryptByCert(cert_id('SymKeyCertTesT'),empsal,'456##456hhhh') as "decrypted salary"
FROM certificatedemo1

Its that simple. In the next blog we see how to use the Transparent Data Encryption Technique


SQL server encryption – Asymmetric Keys

In the previous blog we learnt about symmetric key encryption. We also learnt through an example how data is stored in varbinary form after encryption and how the original text can be retrieve using the corresponding decrypt function.


In this blog we will see something similar with the same table structures using Asymmetric keysAs stated earlier Asymmetric keys consists of public keys and private keys. Each key can decrypt data encrypted by the other. This is a fairly resource intensive operation as compared to Symmetric keys. This option may be chosen over the previous if we need a higher degree of data protection.

1) Create Database Master Key 

2) Encrypt Data
  • Create Asymmetric Key
  • Open Key
  • Encrypt Data
  • Close Key
3) Decrypt Data
  • Open Key
  • Dencrypt Data
  • Close Key

1) Create Database Master Key (DMK)

  USE TESTDB;
  GO
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1211!pwD##';
  GO

2)  Encrypt Data

I have a table with the below structure

CREATE TABLE [dbo].[BankDemo1](
            [BankID] [tinyint] NOT NULL,
            [BankName] [varchar](10) NOT NULL,
                                [BankAmt] [varchar](10) NOT NULL,

 CONSTRAINT [PK_BankDemo1] PRIMARY KEY CLUSTERED
(
            [BankID] ASC
)
) ON [PRIMARY]

BankID
BankName
BankAmt
1
abc
1000
2
def
2000
3
hij
3000

We shall be encrypting the [BankAmt] column. But the cipher data generated during encryption can only be of type varbinary. So the first step is for us to change the structure of the table.

 USE TESTDB;
 GO
 ALTER TABLE BankDemo1
 ADD BankAmtAsymKey varbinary(MAX) NULL
 GO

Now that our table schema is modified we proceed with creation of the asymmetric key


 USE TESTDB;
 GO

-- Create symmetric key 
CREATE ASYMMETRIC KEY TestAsymmKey
 WITH ALGORITHM = RSA_512
 ENCRYPTION BY PASSWORD = '1211!pwD##';
 GO

Let’s do the trick!!!

USE TESTDB
GO

-- Open the asymmetric key
OPEN ASYMMETRIC KEY TestAsymmKey
DECRYPTION BY PASSWORD = '1211!pwD##';
GO

--Encrypt Data
UPDATE BankDemo1
SET BankAmtSymKey = ENCRYPTBYASYMKEY(ASYMKEY_ID('TestAsymmKey'),BankAmt)
GO

 -- Close the asymmetric key
CLOSE ASYMMETRIC KEY TestAsymmKey;
GO

Let’s see the magic we created!!!


BankID
BankName
BankAmtSymKey
1
Abc
0x00F5998B8C856F4E92697DBA96BD44AD01000000547D8E1730DEF80F
F7B2055C8478DA0DDCF7FCAC8E1C23B9C3E7F4A9D1712F75
2
Def
0x00F5998B8C856F4E92697DBA96BD44AD0100000015C40BFAD6271818
6D1EC41375B167FE96C96CB177F8A171C2C81E48E171D1C9
3
Hij
0x00F5998B8C856F4E92697DBA96BD44AD01000000738A3FA8152E5CD6
6335CF2231B37BD5FBFD94BECD0CF056389E6614E5E40037


In the current example I’m saving both the text prior to encryption under the [BankAmt] column and the cipher text in [BankAmtAsymKey] column. In practical scenarios it’s obvious to use a mechanism like stored procedures to perform the encryption task. This stored procedure would ideally be taking inputs for data to be encrypted and storing only the cipher text in the table.

    3) Dencrypt Data
      
      Well since we used ENCRYPTBYASMKEY function your guess is right we will use the corresponding DENCRYPTBYASMKEY function to retrieve the data.

 USE TESTDB
 GO

  -- Open the asymmetric key
 OPEN ASYMMETRIC KEY TestAsymmKey
 DECRYPTION BY PASSWORD = '1211!pwD##';
 GO

 -- Decrypt Data
 SELECT BankID,BankName,
 (CONVERT(CHAR(52), DECRYPTBYASYMKEY(ASYMKEY_ID('TestAsymmKey'),   BankAmtAsymKey, N'1211!pwD##')))
 AS 'Decrypted Amount' FROM BankDemo1

 -- Close the asymmetric key
CLOSE SYMMETRIC KEY TestSymmKey;
GO


Now that we are clear on using both symmetric and asymmetric keys with their respective functions we shall see how to use certificates which encapsulate key information in the next blog.