SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Data Encryption Options

SQL Server Data Encryption Types

Businesses today have a greater need than ever to protect their data from security breaches.  Data stored in databases are a prime target for hackers since it contains data that is normalized and contained in a central location.  Once a hacker accesses a database all of the tables and columns are a potential risk.  SQL Server 2012 provides different way to protect your data at rest in the database via encryption.  Encryption is available in the following formats:

  • Transparent Data Encryption (TDE)
  • Symmetrical Keys
  • Asymmetrical Keys

When choosing your encryption options consider the following points.

  • Encryption consumes CPU resources
  • The longer the key the stronger the encryption
  • Encrypted data cannot be compressed, however, compressed data can be encrypted

Transparent Data Encryption (TDE)

If you need to quickly encrypt an entire database use Transparent Data Encryption (TDE).  Using this method the data in all of the data is encrypted and decrypted using a database encryption symmetical key.  For a business looking to secure sensitive data, this option is easy to implement and requires no code changes to the existing applications.  Data is encrypted and decrypted as the records are added or read from the database.

For complete information on TDE see Technet : http://technet.microsoft.com/en-us/library/bb934049.aspx

Symmetric Keys

When you use a Symmetric Key for encryption the one key is used to encrypt the data as it is inserted into the table.  Likewise, the same key is used as data is extracted to decrypt the data.  This type of encryption is fast, because the same key is being used for all encryption.  It is good to use for sensitive data in applications that require lots of quick transactions.  However, it is not a secure as Asymmetric Keys.

Asymmetric Keys

When performing encryption with asymmetric keys there are two keys required to encrypt and decrypt the data.  One key is setup as the private key and the other is setup as the public key.  Data can only be encrypted when you have access to both of these keys.  While this is a higher level of security than Symmetric keys, it does require more processing due to the encryption and decryption needing to use both different keys.

Regardless of the type of security you need on your database.  SQL Server provides multiple options to encrypt you sensitive data.  I encourage you to read up on the options above and test out options for your particular need.  Ultimately, you need to select the encryption type based upon the level of security you are looking for in your solution.


The post SQL Server Data Encryption Options appeared first on Derek Wilson - Blog .

Business Intelligence and Enterprise Architecture

Derek Wilson delivers tactical and strategic Business Intelligence and Enterprise Architecture solutions. His primary focus in on Microsoft SQL Server technologies and aligning business problems to technology solutions. He architects BI solutions leveraging SQL Server, SharePoint and any other technologies that help his clients achieve better data driven decisions. By leveraging the information learned while collecting requirements for BI projects, he helps align business processes to technology helping further organizations Enterprise Architecture. He is an author, trainer, blogger and has been using SQL Server since version 6.5.


Leave a comment on the original post [derekewilson.com, opens in a new window]

Loading comments...