encryption

Question

encryption

krypto69

SSChampion

Points: 13604
More actions
October 14, 2009 at 11:37 am

#142560

No expierence with encryption
my boss asked me to :
Could you provide me with any screen shots that demonstrate encryption for the transit of data from our DB’s?
How would I do this?

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I would assume this is for audit purposes. You might be able to satisfy him by demonstrating that the data is encrypted in the database (via certificate or otherwise). If he needs a screenshot showing that the data is encrypted across the wire - that is a different story. For that, either show that there is nothing decrypting the data from its source before being sent, or implement an additional mechanism (maybe ssl) to encrypt data transmissions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

krypto69 (10/14/2009)
No expierence with encryption
my boss asked me to :
Could you provide me with any screen shots that demonstrate encryption for the transit of data from our DB’s?
How would I do this?

Show him this video using EncryptByPassPhrase:

http://www.youtube.com/watch?v=425ehlBxqo4

EncryptByPassPhrase:

http://technet.microsoft.com/en-us/library/ms190357%28SQL.90%29.aspx

Alex S

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

There are a lot of ways to do encryption, one is in the database which means that the DB will usually handle encryption and decryption, and the ENCRYPTBYPASSPHRASE is fine for that. But you need to safeguard your key data (which is true no matter what solution you use). Or external encryption where SQL stores a binary blob of encrypted data. In the case only the app usually has the ability to decrypt the data.

A bigger question is what problem are you trying to solve, encryption is not something we generally do unless we need to.

CEWII

krypto69 SSChampion Points: 13604 More actions · Answer 4

This is customer sensitive data, SSN#, etc....

I was told my predecessor had configured encryption, but all the data I have seen in all the databases..none of that is encrypted..

Wouldn't I be able to see if the data is encrypted in the DB? Or does it look like normal data in Enterprise Manager?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I would have to say it SHOULDN'T be visible in the clear to you. Which leads me to say that it probably wasn't encrypted. That is not good.

CEWII

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

krypto69 (10/15/2009)
This is customer sensitive data, SSN#, etc....
I was told my predecessor had configured encryption, but all the data I have seen in all the databases..none of that is encrypted..
Wouldn't I be able to see if the data is encrypted in the DB? Or does it look like normal data in Enterprise Manager?

Unless you are passing the decrypting key when selecting - it should be encrypted when viewed from Enterprise Manager / Management Studio.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

krypto69 SSChampion Points: 13604 More actions · Answer 7

Unless you are passing the decrypting key when selecting - it should be encrypted when viewed from Enterprise Manager / Management Studio.

So there is no other form of SQL encryption that may be present that I am unaware of?

So basically if I look at the data and can see/read it..it is NOT encrypted, correct?

Or is there some other form of encryption I could be missing?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

If encrypted in the database it isn't stored in readable/understandable text in the database.

There could be encryption in place that is encrypting across the wire that is not database encryption (i.e. the use of SSL certs between your desktop and server).

Based on information presented it does not appear that your database has encryption set on those private data columns.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

I would be hesitant to say there isn't a possibility, but I would be willing to say probably not.

One of the uses of encryption is to keep that trusted information out of as many hands as possible, including the DBA's. And being able to see the data without doing anything but a SELECT would not be very effective.

A solution I have implemented uses a pair of encrypted sprocs, the value encryption sproc can be executed by just about anybody who has access to the DB. The decrypt can only be executed by 1 role, and that role has a single user. Role membership is audited and monitored. We can tell if someone is added and then removed. Also, all access to this sproc is logged. It doesn't completely prevent decryption by a highly trusted user, it just makes it more difficult and the encrypted data is accessable by any user who has access to the table, they just can't decode it. Another solution is the DB is treated as nothing more than a storage engine for the encrypted value and does not know how the value was encrypted, this method is more secure in many ways, the data, the key, and the decryption method do not all exist together in the same place. The solution I was talking about also allows for this, but I haven't implemented the external piece.

CEWII

krypto69 SSChampion Points: 13604 More actions · Answer 10

Very Interesting...and informative

Thanks guys....good karma to you all.