How to encrypt columns in SQL Server 2008 R2

Question

I would be using shared hosting for my SQL Server. I wanted to encrypt the primary key column(auto generated ID) and a varchar column. I searched around and came across TDE. But, being a shared hosting, i cannot use it. So, was looking for any alternatives. The data is already there in the tables, so encrypting from application is not possible now. And there are many SQL statements that search using the above said varchar column. So, performance is also a concern.

Thanks, Dev

Solution

encrypt the primary key column(auto generated ID)

And how will you be able to find a record if its ID is encrypted?? Answering 'I serach by encrypted ID' disqualifies you for not salting the key...

And now for the real issue. You said that you'll be deploying on shared hosting, but you do not mention what kind of protection are you expcecting that ecnryption will provide. The problem is key management. Data will be encrypted with a key and the server will need to decrypt that dtaa somehow. No matter how you turn the problem arround, the root key used to decrypt all the other keys will be also on shared hosting, and hence you will not achieve anything but a slight bump on the road to your data. To put up a fence, the key must be retrieved somewhere from ouside the scope of the shared hosting, eg. your applicaiton will ask for the password to decrypt the root key when interacting with the users, which is by all practical means impossible. Note that TDE would had solved nothing since the root of trust has the very same problem in TDE or in columnar encryption. If you need privacy, use private hosting.

And to answer the question:

• to encrypt data you use ENCRYPTBYKEY.
• to decrypt data use DECRYPTBYKEY
• the encryption key should be encrypted with a certificate and the certificate should be encrypted with a password, or with the database master key. See Encryption Hierarchy.
• you open the encryption key in the session using OPEN SYMMETRIC KEY
• you open the master key in the session using OPEN MASTER KEY
• you search encrypted dtaa by creating a hash and searching the hash, see Indexing encrypted data

And if you use columnar encryption you never encrypt the primary key. Doing so is completely non-sensical. And claiming any sort of privacy in shared hosting environment is a pipe dream. The only thing you could protect agfainst, at best, is accidental media loss (the hosting HDD turning up at a flea market).

Comments

[Groo]

What would you accomplish by encrypting an auto generated ID key?