CODEWITHSUNDEEP
sql-serverencryption
Blankman
Blankman

Reputation: 267000

Tools to encrypt sql server database

I don't want customers to be able to make backups of my sql server database and access the tables data etc.

I know there are some products that will encrypt the data in the tables, and their product will decrypt it when displaying in my application.

What products do you guys know of? What options do I have?

(This is a business requirement, however silly it might seem to some hehe).

Update

This is for sql server 2008 express

Upvotes: 4

Views: 6726

Answers (6)

user1309871
user1309871

Reputation: 257

Another solution for transparent SQL Server encryption is DbDefence Free for databases less than 77 MBs.

Upvotes: 0

Brent Ozar
Brent Ozar

Reputation: 13274

The problem with encrypting data inside the database is that as long as the database lives on the client's machine (as you indicated, they're running SQL 2008 Express, so I'm betting it lives on the client's desktops or laptops) then they can get into the data. They can set up security on the instance so that they have SA privileges, and from there, they can get the data, period. There's no way around that.

What you have to do is encrypt the data before it hits the database: encrypt it in your application. Inside the app, encrypt the data that you want to store in each sensitive field. As another poster indicated, you don't want to encrypt ID fields because those are used for indexing.

Upvotes: 3

Jim McLeod
Jim McLeod

Reputation: 952

Transparent Data Encryption will encrypt the database on disk, but is unencrypted in memory, so appropriate security would also be necessary to ensure unauthorised users cannot access the table. As it's an Enterprise-only feature, you can safely move away from it.

SQL Server 2005 and above have built-in encryption features - have a look at Books Online, and especially Chapter 5 - Encryption of Adam Machanic's Expert SQL Server 2005 Development book (technically, Lara Rubbelke wrote chapter 5 though).

Note that you'll only want to encrypt some columns - those that you'll never try to look up, as encrypted columns are pretty much useless for indexing. Adam Machanic's book suggests ways to solve this problem.

Upvotes: 0

gbn
gbn

Reputation: 432261

There is the 3rd party xp_crypt. It's been around for years. It's an extended stored proc (that is, DLL)

Upvotes: 2

Sam
Sam

Reputation: 7678

You can encrypt stored procedures, which can protect your logic.

TDE is available only Enterprise edition.

I can't find if it supports native sql encryption - but you could find this out with a little searching. But if it did you could probably set the database master key with your application and keep all of the decryption/encryption code in your application.

If it doesn't support native encryption, you might want to creat/find your own encryption functions in your application language and lock away the keys in your code.

Upvotes: 0

Rune Grimstad
Rune Grimstad

Reputation: 36300

SQL Server 2008 supports database encryption natively. Check the documentation for Transparent Data Encryption (TDE).

Upvotes: 0

Related Questions