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.

SQL server encryption – Symmetric Keys

In the previous blog we learnt about encryption and a brief about the SQL server option provided for data protection through encryption. 

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

In this blog we will learn with a demo how to use the first option Symmetric Keys for encryption.As stated earlier symmetric keys use the same key to encrypt and decrypt data.

When a symmetric key is created, the symmetric key must be encrypted by using at least one of the following: certificate, password, symmetric key, asymmetric key, or PROVIDER. 

In this blog we will be using one protected with password.  In later modules we will be using asymmetric keys and certificates

1) Create Database Master Key

2) Encrypt Data

• Create Symmetric Key
• Open Key
• Encrypt Data
• Close Key

3) Decrypt Data

• Open Key
• Dencrypt Data
• Close Key

 1)  Create Database  Master Key

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 BankAmtSymKey varbinary(MAX) NULL

GO

               

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

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

USE TESTDB;

GO

CREATE SYMMETRIC KEY TestSymmKey

WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = '1211!pwD##';

GO

Let’s do the trick!!!

USE TESTDB

GO

 -- Open the symmetric key

OPEN SYMMETRIC KEY TestSymmKey

DECRYPTION BY PASSWORD = '1211!pwD##';

 --Encrypt Data

GO

UPDATE BankDemo1

SET BankAmtSymKey = ENCRYPTBYKEY (Key_GUID('TestSymmKey'),BankAmt)

GO

 -- Close the symmetric key

CLOSE SYMMETRIC KEY TestSymmKey;

GO

Let’s see the magic we created!!!

BankID	BankName	BankAmtSymKey
1	Abc	0x00F5998B8C856F4E92697DBA96BD44AD01000000547D8E1730 DEF80FF7B2055C8478DA0DDCF7FCAC8E1C23B9C3E7F4A9D1712F75
2	Def	0x00F5998B8C856F4E92697DBA96BD44AD0100000015C40BFAD6 2718186D1EC41375B167FE96C96CB177F8A171C2C81E48E171D1C9
3	Hij	0x00F5998B8C856F4E92697DBA96BD44AD01000000738A3FA8152 E5CD66335CF2231B37BD5FBFD94BECD0CF056389E6614E5E40037

  

In the current example have saved both the text prior to encryption under the [BankAmt] column and the cipher text in [BankAmtSymKey] column. 

In practical scenarios it’s obvious to use a mechanism like stored procedures to perform the encryption task. This stored procedure would ideally taking inputs for data to encrypt and storing only the cipher text in the table.

3) Dencrypt Data

Well since we used ENCRYPTBYKEY function your guess is right we will use the corresponding DENCRYPTBYKEY function to retrieve the data.

USE TESTDB

GO

 -- Open the symmetric key

OPEN SYMMETRIC KEY TestSymmKey

DECRYPTION BY PASSWORD = '1211!pwD##';

GO

-- Decrypt Data

SELECT BankID,BankName,

CONVERT(varchar, DecryptByKey(BankAmtSymKey)) AS 'Decrypted Amount'

FROM BankDemo1

 -- Close the symmetric key

CLOSE SYMMETRIC KEY TestSymmKey;

GO

In the next blog we shall see the same example but this time we shall be using asymmetric keys.

SQL Server Encryption

Encryption is a methodology used to hide confidential information from any illegitimate user is such a way that they do not see the actual contents of the information. Rather they get access to some cipher text. This text can be decrypted to its original form by legitimate users by the use of certain algorithms and secret code known as encryption keys or passwords.

Windows OS Encryption Interface

Starting windows 2000 an API is provided by the operating system to accommodate such data protection mechanism.  

How does the operating system do this? In a nutshell there are 2 functions namely CryptProtectData() & CryptUnprotectData(). As the name suggests first one will be used to encrypt the data the latter will be used to decrypt the cipher text.

Both these functions are available under Crypt32.lib library. If the library is not linked to the application that needs to use these windows functions one can dynamically call the Crypt32.dll dynamic link library in their application.The functions run in the security context of the Local Security Authority (LSA). For those who are unaware LSA is a system process that loads when the system is started and runs till the system is shut down. 

The CryptProtectData() requires a password to create the cipher. The LSA provides the existing user's password hash. Obviously all applications running under the same user account will be able to access these functions and decrypt any protected data if aware of the location under the same context.The operating system however only provides the mechanism for data protection. This implies that the applications calling these functions should implement their own storage for protected data. When you aren’t aware of the location where the cipher text is stored; the functions and password hash serve no purpose to get unauthorized access to sensitive information. But it’s certainly possible hence DPAPI allows an application to use an additional secret when protecting data. 

This additional secret is then required to decrypt the data.

DPAPI generates MasterKeys which are in turn used to generate session keys. These keys along-with some random data & encryption algorithm are used for data encryption. The operating system stores this random data and on discovery of any cipher text uses this random text to generate the session key and decrypt the actual data.

One may wonder how does DPAPI work and get the same MasterKey back if the password is changed. This has also been taken care of by the operating system so we need not worry. I’d recommend you read the below article to understand in depth the WindowsOperating System DPAPI functionality.

This master key is used by SQL server to create something called as Service Master Key (SMK).  The SMK sits at the top of hierarchy of SQL server encryption.

Encryption in SQL server.

The SQL server service master key (SMK) is generated at the time of SQL server setup with the master key from Windows DPAPI and the service account configured credentials provided for setup. One service master key per instance.  

Note: SMK is a symmetric key.

One will find its entry in the below table

USE master;

GO

SELECT *

FROM sys.symmetric_keys

WHERE name = '##MS_ServiceMasterKey##';

GO

The service master key is used to encrypt the Database Master Key (DMK).  The DMK is used to generate asymmetric keys, symmetric keys and certificates in SQL server. Microsoft depicts this beautifully in the below Microsoft diagram.

Thus we have 3 techniques to encrypt data in SQL server

1) Symmetric Keys
    Symmetric keys use the same key to encrypt and decrypt data.

2) Asymmetric Keys
    Asymmetric key ideally uses a public key to encrypt data and a matching private key is used to decrypt the data. It can also be used vice versa but only the matching pair of keys can decrypt the data encrypted by other.

3) Certificates.
    Certificates are containers for the user's public key which store additional data identifying the organization that issued the certification.

There is a utility called certmgr.msc used to view/install certificates on the current machine.

Until SQL server 2005 these were the only 3 techniques for encryption. With the advent of SQL server 2008 SQL server introduced 2 new techniques for encryption namely

4) Transact-SQL functions

This feature is available in all editions of SQL server. This is also known as Cell-Level Encryption /Column Level Encryption in SQL server. SQL server makes use of the below encrypt,decrypt functions to protect data within columns of record.

The encryption functions are:

·         ENCRYPTBYKEY, which uses a symmetric key to encrypt data

·         ENCRYPTBYCERT, which uses the public key of a certificate to encrypt data

·         ENCRYPTBYPASSPHRASE, which uses a passphrase to encrypt data

·         ENCRYPTBYASYMKEY, which uses an asymmetric key to encrypt data

The decryption functions are:

·         DECRYPTBYKEY, which uses a symmetric key to decrypt data

·         DECRYPTBYCERT, which uses the private key of a certificate to decrypt data

·         DECRYPTBYPASSPHRASE, which uses a passphrase to decrypt data

·         DECRYPTBYASYMKEY, which uses an asymmetric key to decrypt data

·         DECRYPTBYKEYAUTOASYMKEY, which decrypts data by using a symmetric key   that's automatically decrypted with an asymmetric key

·         DECRYPTBYKEYAUTOCERT, which decrypts data by using a symmetric key        that's automatically decrypted with a certificate

     5)  Transparent Data Encryption

The entire database is encrypted both the data and the log files. Data at rest is encrypted. Data transformation happens in memory by SQL server; thus the application using the database need not bother about the encryption decryption process. This feature is only available in Enterprise Edition. Backups of these databases are also encrypted.

In the continued blogs we learn how to encrypt data using each of these techniques.

Windows DPAPI

https://msdn.microsoft.com/en-gb/library/ms995355.aspx

http://www.overclock.net/t/1293731/windows-data-protection-api-c-and-c

Encryption Hierarchy

https://msdn.microsoft.com/en-us/library/ms189586(v=sql.130).aspx

https://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

Choosing and algorithm

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

Encryption types

http://sqlmag.com/database-security/sql-server-encryption

http://sqlmag.com/database-security/sql-server-encryption-options