Re: SQL Server 2000 / 2005 Encryption


"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.


.