programming4us
programming4us
DATABASE

SQL Server 2012 : Encryption (part 2) - Certificate-Based Encryption, Transparent Data Encryption

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/19/2014 9:29:20 PM

Certificate-Based Encryption

In the previous section, you encrypted data and protected the symmetric key by using a password. Although this is a perfectly acceptable way of protecting the key, the problem is that whenever you want to access the encrypted data, you will have to specify a password. If you are accessing the data from a script file or stored procedure, you will have to store the password in clear text in your script or stored procedure, which defeats the purpose of encryption. For this reason, a better approach would be to create a certificate and give the user, in our case BankManagerUser, access to the certificate. The certificate will then be used instead of a password to protect the symmetric key.

When you create a certificate, you need to protect that as well since certificates contain private keys. Certificates can be protected using a password or by using the database master key. There is only one database master key per user database. The purpose of this key is to protect keys like private keys where no explicit password is provided.

The following is how you create a master key. Before creating it, you should issue a REVERT statement to revert your current connection context to SYSADMIN if have been following along on your own SQL Server.

REVERT
GO
USE [ContosoBank]
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Some!@Complex*@(39'
GO

Do not lose this password, because if you do, you won’t be able to back up the key. Now that you have created the database master key within the ContosoBank database, you can create a certificate that will be used to protect the symmetric key.

CREATE CERTIFICATE BankManagersCert
AUTHORIZATION BankManagerUser
WITH SUBJECT=’Bank manager’’s certificate’
GO

There is no dialog box in SSMS that will allow you to create a certificate, so you will have to use the CREATE CERTIFICATE T-SQL statement. Now, you can create your symmetric key, protecting it with a certificate instead of a password. Since you are going to replace the protection, you need to open the key first, add the certificate protection and then remove the password protection. This will ensure that the symmetric key is never stored in cleartext for any duration of time. The following is an example:

OPEN SYMMETRIC KEY [BankManager_User_Key] DECRYPTION BY PASSWORD='HighFeesRule!'
GO
ALTER SYMMETRIC KEY BankManager_User_Key
ADD ENCRYPTION BY CERTIFICATE BankManagersCert
GO
ALTER SYMMETRIC KEY BankManager_User_Key
DROP ENCRYPTION BY PASSWORD='HighFeesRule!'
GO
CLOSE ALL SYMMETRIC KEYS
GO

Now that you have protected your key with a certificate, BankManagerUser does not need to specify a password to open the key. To confirm this, you can change your context to BankManagerUser by using the EXECUTE AS statement, for example:

EXECUTE AS USER='BankManagerUser'
GO
USE [ContosoBank]
GO
OPEN SYMMETRIC KEY [BankManager_User_Key] DECRYPTION BY CERTIFICATE BankManagersCert
GO
SELECT customer_id,first_name + ' ' + last_name,
CONVERT(VARCHAR,DecryptByKey(social_security_number)) as 'Social Security Number'
FROM Customers
GO
CLOSE ALL SYMMETRIC KEYS
GO

Encrypting data is a very important tool that can be used to add some protection above and beyond column-level permissions. SQL Server provides addition encryption capabilities such as the ability to encrypt the entire database. This is known as transparent data encryption (TDE).

Transparent Data Encryption

The encryption capabilities within SQL Server are powerful. However, they require application changes that may or may not be feasible in your environment. For this reason, SQL Server 2008 comes with an ability to encrypt the entire database. Technically, what happens when you enable encryption at the database level is that SQL Server encrypts data pages before it writes them to disk. Conversely, when data pages are read from the disk, they are decrypted and placed into memory. To the user, the experience with SQL Server is unchanged. They do not know the data is encrypted.

This type of encryption is termed transparent data encryption and protects against the attack in which someone copies the database files themselves and attempts to attach them to a SQL Server instance that they themselves control. As an added benefit, if you perform a BACKUP DATABASE operation on a database that has encryption enabled, the backup will be encrypted as well.

To enable encryption on a database, you need to create a database encryption key (DEK). A DEK is a symmetric key that the SQL Server service uses to encrypt and decrypt the database files. Since this is a symmetric key, it needs to be protected. You protect the DEK through a certificate that is created in the master database. This certificate contains a private key and needs to be protected as well. The certificate is protected by the database master key of the master database.  To enable encryption on the ContosoBank database, use the following script:

USE master;
GO
--This database master key is created in master
-- and is used to protect the certificate
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EOhnDGS6!7JKv';
GO
--This certificate is used to protect the database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
--You are now ready to create the Database Encryption Key
USE ContosoBank
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE ContosoBank SET ENCRYPTION ON;
GO

There is a slight CPU performance hit when enabling TDE on databases. The actual amount depends on server hardware and many other factors. This should not prohibit you from enabling this useful feature. Perform testing and see for yourself that the benefits will most likely outweigh the performance hit.

Other  
  •  SQL Server 2012 : Auditing in SQL Server (part 3) - Database Audit Specification Object, User-Defined Audit Event
  •  SQL Server 2012 : Auditing in SQL Server (part 2) - Server Audit Specification Object
  •  SQL Server 2012 : Auditing in SQL Server (part 1) - Auditing Objects, Server Audit Object
  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
  •  Protecting SQL Server Data : Obfuscation Methods (part 3) - Numeric Variance,Nulling
  •  Protecting SQL Server Data : Obfuscation Methods (part 2) - Repeating Character Masking
  •  Protecting SQL Server Data : Obfuscation Methods (part 1) - Character Scrambling
  •  SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us