Stairway to SQL Server Security Level 8: Data Encryption

  • Don Kiely

    SSC Eights!

    Points: 849

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the nice read.

  • *Prashant Thakwani

    SSCommitted

    Points: 1687

    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 Thakwani
    thakwani_prashant@yahoo.co.in

  • Don Kiely

    SSC Eights!

    Points: 849

    *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

    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: 849

    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

    >>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: 849

    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.

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

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