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

Syntax : https://msdn.microsoft.com/en-us/library/ms190357.aspx

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.

Syntax : https://msdn.microsoft.com/en-us/library/ms188910.aspx

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()

Syntax: https://msdn.microsoft.com/en-us/library/ms365420.aspx

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().

Syntax: https://msdn.microsoft.com/en-us/library/ms182559.asp 

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.

https://www.mssqltips.com/sqlservertip/3081/using-views-to-expose-encrypted-data-in-sql-server/

https://www.mssqltips.com/sqlservertip/1760/using-authenticators-with-sql-server-encryption/

 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.

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. 

http://sqlandmorewithkruti.blogspot.co.uk/2015/10/sql-server-encryption-certificates.html

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

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.

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-symmetric-keys.html

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-asymmetric-keys.html

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.

Syntax : https://msdn.microsoft.com/en-us/library/ms187798.aspx

  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.

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-symmetric-keys.html

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

Syntax : https://msdn.microsoft.com/en-us/library/ms174430.aspx

 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.