Stairway to SQL Server Security Level 8: Data Encryption

Question

Post reply

Stairway to SQL Server Security Level 8: Data Encryption

Don Kiely

SSC Eights!

Points: 959
More actions
March 19, 2015 at 6:26 am

#398212

Comments posted to this topic are about the item Stairway to SQL Server Security Level 8: Data Encryption

Viewing 8 posts - 1 through 7 (of 7 total)

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

akljfhnlaflkj

SSC Guru

Points: 76202

April 15, 2015 at 6:39 am

#1791072

Thanks for the nice read.

*Prashant Thakwani SSCommitted Points: 1827 More actions · Answer 2

Don, Reading your excellent and crisp article on Data Encryption in 'Stairway to SQL Server Security Level 8: Data Encryption' series. Have a quick question. Into the article you made the statement But you don't have to handle those details, because SQL Server will take care of key management for you. It will encrypt the new key for you, using whatever method you specify as part of the CREATE statement, and store the data as securely as it stores any other sensitive information., can you elaborate But you don't have to handle those details, because SQL Server will take care of key management for you

Prashant Thakwanithakwani_prashant@yahoo.co.in

Don Kiely SSC Eights! Points: 959 More actions · Answer 3

*Prashant Thakwani (4/16/2015)
...But you don't have to handle those details, because SQL Server will take care of key management for you

Sure. The difference is that if you supply a password when you create any of the keys, YOU are taking responsibility for keeping that password secret. That includes providing it in a secure way from any applications or T-SQL code that needs to encrypt or decrypt the data.

But if you let SQL Server protect the key using the database master key--or another custom key--then SQL Server takes charge of keeping the key secure, opening it as necessary (although sometimes you have to explicitly open it). In this case, you don't need to worry about keeping anything secret; SQL Server does it for you.

Make sense?

Don

mark_ferdenzi SSC Rookie Points: 37 More actions · Answer 4

I ran the examples in this article but there seems to be a limit of 30 characters in the data e.g. 'One of our best customers. Treat like royalty.' is stored as 'One of our best customers. Tre'.

Any ideas?

Don Kiely SSC Eights! Points: 959 More actions · Answer 5

Sorry, that was a bit of sloppiness with my use of CONVERT. The length returned from that function is by default 30. You can change the select statement to something like the following to get the full data back:

SELECT CustID, Name, City,

CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CardType,

CONVERT(VARCHAR, DecryptByKey(CreditCardNumber)) AS CardNumber,

CONVERT(VARCHAR(50), DecryptByKey(Notes)) AS Notes

FROM Customer;

This issue has nothing to do with the encryption.

Thanks for pointing that out!

Don

daniel.gaddis SSC Rookie Points: 43 More actions · Answer 6

>>You'll use a symmetric key to encrypt the data in the table, but remember that a symmetric key requires either a certificate or asymmetric key to protect it in the database. So, first create an asymmetric key to protect the symmetric key using the code in Listing 8.2.

You are encrypting the symmetric key with an asymmetric key. I have seen other articles that encrypt the symmetric key with a certificate. Do you have any thoughts on why I should pick one over the other?

Don Kiely SSC Eights! Points: 959 More actions · Answer 7

daniel.gaddis (4/23/2015)
>>You'll use a symmetric key to encrypt the data in the table, but remember that a symmetric key requires either a certificate or asymmetric key to protect it in the database. So, first create an asymmetric key to protect the symmetric key using the code in Listing 8.2.
You are encrypting the symmetric key with an asymmetric key. I have seen other articles that encrypt the symmetric key with a certificate. Do you have any thoughts on why I should pick one over the other?

Not really. It depends a lot on your security setup in the database and server instance, but either one will be secure, as long as either you keep the secret securely or let SQL Server manage it for you.

Generally, though, you're not going to create dozens of keys and certificates, so design something that will work to protect the data from your specific threats. Keeping it simple is the best security strategy you can employ.