Re: SQL Server 2000 / 2005 Encryption

Editing error. It should say "...but SQL 2000 requires 3rd-party tools..." Sorry 'bout that.

No worries, and excellent! For a little while there I thought just when I am starting to get it, its actually more complicated than I thought.
:-)

As for your comments about getting the data to end users, much appreciated. I've been trying to explain to all that this is going to be a lot more involved than originally first thought, and your comments validate that for me. At least now I can go into the next meeting a lot more prepared, so I thank-you for that.

I'm sure I will come out with more questions at that point.

Thanks again Mike.

Rubens

"Mike C#" <xyz@xxxxxxx> wrote in message news:OyxvxNbfIHA.968@xxxxxxxxxxxxxxxxxxxxxxx

"Rubens" <rubensrose@xxxxxxxxxxx> wrote in message news:%23856lAafIHA.5620@xxxxxxxxxxxxxxxxxxxxxxx
Ok, encrypting the connection makes sense to me. So on to the data at rest.

2) You're also talking about encrypting the data stored "at rest" in the database. This is possible on SQL 2005 via built-in T-SQL statements and functions, but requires 3rd-party tools, usually some sort of extended procedures (XPs).

You require 3rd-party tools even when using the native SQL 2005 encryption to encrypt the data "at rest"? I thought SQL 2005 supported that natively, but SQL 2000 required the 3rd party tools to accomplish this?

Editing error. It should say "...but SQL 2000 requires 3rd-party tools..." Sorry 'bout that.

The certificates used to encrypt your data stored on SQL Server 2005 and to encrypt your SSL connections will be different. SQL Server can generate its own self-signed certificates though, and that should make the SSL encryption/connectivity easier for you. Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form.

Ok, I can figure out the details on the certificates when I get to that point. But you mentioned Excel cannot decrypt this information. If we have end users using Tablet PC's that need to decrypt this data, are they going to have to use a T-SQL statement calling the decrypting functions / XP's through QA or SSMS to get it? Or is there a means of having this done seamlessly for them? They are not technical at all.

The end users wouldn't necessarily have to write T-SQL statements themselves. You could put layers of abstraction like stored procs and web services between the Table PC's and the SQL Server, and put a pretty UI on it. Requests to the web services would connect to the database and invoke SP's to do the decryptions for you, then the results would be returned to the Tablet PCs. It might actually end up being a little more complex than this to implement with the Excel piece, but that's the basic idea. I had to do something similar a while back with Word and VBA, the end result being that encrypted data stored in the database was decrypted server-side, retrieved from the database, and inserted in Word documents with the push of a button. I imagine it wouldn't be too much more difficult with Excel, but don't quote me on that...

Many thanks again Mike,
Rubens

No problem.

.