title: Sql Server Encryption and Decryption
body: <p>I want to use sql server encryption and decryption in my database. I read many articles related to that and i tried that thing in my sql server as well.
  I tried <code>Symmetric Key</code> and <code>Transparent Data Encryption</code> both at my end. Symmetric key also know as field/cell based security in which the key save in <code>sys.symmetric_keys</code> table and one can who has administrator rights can view that key and use it and second one is <code>TDS</code> that works on <code>certificate + private key + Encription By Password</code> and basically used to protect our database like a way if someone has my database backup then they cannot restore it at there end without knowing the <code>certificate + private key</code> files and <code>Encription By Password</code>. These all are the points that i come to know while working with <code>Symmetric key and TDS</code>. As per my study i come to know that <code>TDS</code> is highly secure but <code>Symmetric key</code> is not secure because it save the key with in the database. </p>
  
  <p>So i want to know that the steps i mentioned underneath will be possible in <code>Symmetric Key</code> topology. If yes then How and if No then what are the other ways to achieve the same thing.</p>
  
  <ol>
  <li>How not to store the Key in the database means every time when i
  want    to encrypt the field will pass my key from front end.</li>
  <li><code>UPDATE dbo.tablename SET CARDnumber_encrypted= EncryptByKey(Key_GUID('CreditCards_Key'), FirstName);</code> How i can use some extra variable with <code>Key_GUID('CreditCards_Key')</code> so that if some one has the key then still they cannot <code>decrypt</code> the field.</li>
  </ol>
  
  <p>I can even use the cryptography from my front end like encrypt the value in front end and then save it into backend and for decrypt that field again i will do that thing in front end, but i want to do that all things into backend not for front end.</p>
  
  <p>So please guide me that how i can achieve those points i mentioned. If you are not getting my point please ask me again.</p>
  
  <p>Any help will be appriciated.</p>