Hi:

I need to protect some sentitive data in sql server 2000, I want to know which are the best ways to protect the data, but query the data to show in reports as well.


Thanks in advance.

In SQL 2000, you don't have the built in feature of encryption. You can still encrypt using your own method, which may cause not only performance issues but also manageability issues and I will strongly discourage you in moving in that path.
Why don't you separate the information and restrict access? Allow the reports to use a privileged connection to extract the data.


G.R.Preethiviraj Kulasingham

Unless you are a very skilled cryptogropher, never ever try to build your own. Takes amost nothing to crack most home grown attempts.

I believe the SDK for SQL Server comes with a method for encryption, but if I really needed to do encryption correctly, I'd buy certified 3rd part software to do it.

--Jeff Moden

Hi Jeff:

I am planning to do the following:
1. implement AES in a sql server user function.
2. implement a key management system in another enviroment, I've got two ISP. I am thinking of set up a web service in one of them and consume it through ssl and restrict only calls from the other ISP.
3. create a daily key in a private database where the web service will reside, then those will be used in the other system as an inout to encrypt the sensitive data.

I just want to know your comments for these solution, and if you can give me some advice on how to make it secure. Also how I can generate these keys.

The encryption that comes with SQL is extremely week.

Externally accessible third party tools are generally the best.

Ideally I try to add my own salt(cryptogrophy term) in addition to the external tool. Thus if someone has a crack for your third party tool, you additional layer adds just a bit more confusion to the would be hacker.

The feds outlawed certain encryption routines until they had a way of decrypting them. This will frustrate these methods if the wrong parties try to read your stuff. But everything can be cracked given enough force and time.