CODEWITHSUNDEEP
sqlsql-serversql-server-2008encryptionsql-server-2008-r2
Nasser Hadjloo
Nasser Hadjloo

Reputation: 12610

How to Encrypt SQL Server 2008 Database

I have a database which currently is using by my application. I dodn't want to change anything in my application CORE. But I want to encrypt DATA and INFORMATION in which stored or is storing in database.

SQL Server 2008 (or 2008 R2) should have an internal mechanism to encrypt and decrypt information which given and taken to application.

How to get it to work? Encryption is needed for all of the fields in Database, numerics, strings, booleans, datetimes, etc.

Any help is appriciated

Upvotes: 3

Views: 15041

Answers (5)

Dhyan Mohandas
Dhyan Mohandas

Reputation: 1134

Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) is a technology developed by both Microsoft and Oracle to encrypt database files. It offers file level encryption and protects data at rest by encrypting databases both on the hard drive and backup media. It does not protect Data in transit or Data in use.

The encryption will be completely transparent to the applications that access the database. It encrypts the data in the database's data file (.mdf) and log file (.ldf) using Advanced Encryption Standard or Triple DES encryption.

The same key can be used to encrypt all the columns in a table, regardless of the number of columns in the table that is to be encrypted. The database server master key provides encryption to these encryption keys and are stored in a dictionary table in the database.

For more info please refer the link How to implement TDE encrytion on SQL Server

Upvotes: 0

user2708351
user2708351

Reputation: 139

If you really encrypt everything by field, you will lose advantage with indexing and going to have enormous performance degradation.

Free option: place database files on encrypted partition. This however will play no difference for DBA of course. He will be able to select as it would un-encrypted database.

There is an inexpensive solution similar to Microsoft TDE called DbDefence (yes, I'm associated with that company)

As you might know, Microsoft is going to implement new encryption feature in SQL Server 2016 called "Always Encrypted". DbDefence has been using that method for SQL Servers since version 2005. It is really effective and you would not need to change anything in the application.

Upvotes: 0

user1309871
user1309871

Reputation: 257

You may substitute your real table with a VIEW add INSTEAD OF INSERT and INSTEAD OF UPDATE triggers, create several User Defined Functions. You'd probably need to modify your application slightly.

Instead of that, consider using encryption on file level with schema protection. Such tools like DbDefence available in free edition for small databases and very moderate price for mid-size databases.

Upvotes: 0

Slogmeister Extraordinaire
Slogmeister Extraordinaire

Reputation: 3171

SQL 2008 has TDE or, Total Database Encryption. This encrypts the entire database and is transparent to any users of the database. The details of how to go about it can be found in these two Microsoft articles:

http://msdn.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx

http://msdn.microsoft.com/en-us/library/bb934049.aspx

Upvotes: 2

DooDoo
DooDoo

Reputation: 13447

If you want cell level Encryption you should do it yourself (using Triggers or any other wayes) if you want Encrypt Database file you can use Transparent Data Encryption (TDE)

Upvotes: 3

Related Questions