encryption

Question

encryption

Greg Larsen

SSC-Insane

Points: 20961
More actions
April 4, 2006 at 9:55 am

#365831

Is there a seamless/transparent way to encrypt/decrypt a column without changing an applications that uses that column, in SQL Server 2000? What about 2005?
Gregory A. Larsen, MVP

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

I'm not sure I understand what you're asking, Greg, but I'll take a stab...

If you're changing the application, as long as the application can encrypt/decrypt using the same mechanism and have the same keys or secrets, you probably wouldn't have to. SQL Server 2000, since any encryption is going to be 3rd party, is going to be completely dependent on the 3rd party implementation. SQL Server 2005 this is fairly simple. If you've used, say a symmetric key to encrypt the data, the second app's login(s) would just need permission to use said key.

K. Brian Kelley
@kbriankelley

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

Basically I'm being asked if it is possible to encrypt/decript a column between the application and the database. This way the application doesn't have to be changed. They are looking for some database process or pre-database process to capture the commands, and encrypt sensative data as it is being stored in sql server, and having a post-database process the decrypts the data when the application needs to read this sensative data. The pre and post processes will not be part of the application but will be done somewhere between the application and the database. Here is a little is a text representation to display this process:

A -> E -> D

Application A send SQL commands to instance of SQL Server, just as they do today. Some encryption process "E" intercepts the command prior to reaching a SQL Server instance and encrypts the sensative columns. process "E" then passes on the encrypted data to D. D is just a SQL Server instance. To retrieve data from the database it would go like through a similar processs like os:

D -> DC ->A

Where DC is just a decrypt process.

So I'm asking is the a way to do that in SQL 2000 where E and DC don't require A to change, and can be managed totally outside of A? Can this process I described be done in SQL 2005 using summetric key?

Gregory A. Larsen, MVP

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

You might take a look at Protegrity's DefianceDPS or App Sec Inc.'s dbEcrypt

I believe both of those are supposed to do so seamlessly to the application.

K. Brian Kelley
@kbriankelley

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

So you have to buy a 3rd party tool. SQL Server 2005 or 2000 for that matter doesn't support doing this kind of seamless encryption/decryption. Right?

Gregory A. Larsen, MVP

Chris Stamey SSCrazy Points: 2695 More actions · Answer 5

We have been discussing this for a project we are doing and we have settled on the encryption that is part of the OSes API.

I thought about third-party tools but one of the senior programmers at work told me how to do it with what's readliy available.

We will be using a web browser to access the app, and the Windows OS that will be running IE will also have the crypto API in it to take care of encrypting/decrypting the data. I'm not completely sure how it works as I am not the programmer, but it was important to this project that we not have to have a specific key that was a static thing and the user had to log in from a certain workstation in order to decrypt.

Wish I could give more details, but I have been assigned to a different project for a while and have forgotten...

Chris

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

SQL Server 2000 doesn't have any sort of built-in encryption, so there's no choice but to go with a 3rd party tool. With SQL Server 2005 if you have the ability to alter stored procedures without affecting the application (good logical layer design), it is possible to built the encryption/decryption mechanisms in a manner that does appear seamless to the application. However, if you're talking about direct queries against tables, no. In that case you'd have to look at a 3rd party tool.

K. Brian Kelley
@kbriankelley

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 7

In general how does the 3rd party tools capture the direct queries and encrypt/decript between the application and the database engine? Where is the hook?

Gregory A. Larsen, MVP

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

To be honest, I'm not sure. I've not had to install any of the two, I just know they are two most mentioned. We typically handle our encryption at the app level, so we've not had a need to implement such a product.

K. Brian Kelley
@kbriankelley

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 9

In 2005 use certificates. The data is encytped when writted to the column, and decrypted when read. Very nice.

Terry

Chris Stamey SSCrazy Points: 2695 More actions · Answer 10

Just ran across this. See the "How do I perform encryption with SQL Server?" question at: http://www.sqlsecurity.com/FAQs/SQLServerFAQ/tabid/55/Default.aspx

Chris

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

True, very true. However, you're still having to execute EncryptByCert and DecryptByCert on the column. Therefore, if the app hits the base tables directly, you can't make this change seamlessly. However, if it relies on other objects, such as through stored procedures, then such a change can be made.

K. Brian Kelley
@kbriankelley