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
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:
ENCRYPTBYKEY. DECRYPTBYKEYOPEN SYMMETRIC KEYOPEN MASTER KEYAnd 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).
Dev, I think in your case the only choice is to encrypt from application.
Alternatively you can rename your table, substitute the VIEW instead of renamed table. Encrypt all existing data: update real_table set field1 = call_encrypt_function(field1), field2 = call_encrypt_function(field2) In that view you can do select call_decrypt_function(field1),call_decrypt_function(field2) from real_table. For insert and updates you need to master INSTEAD OF INSERT and INSTEAD OF UPDATE triggers. Of course you need to use WITH ENCRYPTION for that objects. I saw that approach in XP_CRYPT, however I prefer free solutions. SQL Server provides basic encryption functions for free.
