CODEWITHSUNDEEP
sql-serverencryptioncryptography
Rahul
Rahul

Reputation: 5636

Sql Server Encryption and Decryption

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 Symmetric Key and Transparent Data Encryption both at my end. Symmetric key also know as field/cell based security in which the key save in sys.symmetric_keys table and one can who has administrator rights can view that key and use it and second one is TDS that works on certificate + private key + Encription By Password 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 certificate + private key files and Encription By Password. These all are the points that i come to know while working with Symmetric key and TDS. As per my study i come to know that TDS is highly secure but Symmetric key is not secure because it save the key with in the database.

So i want to know that the steps i mentioned underneath will be possible in Symmetric Key topology. If yes then How and if No then what are the other ways to achieve the same thing.

  1. 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.
  2. UPDATE dbo.tablename SET CARDnumber_encrypted= EncryptByKey(Key_GUID('CreditCards_Key'), FirstName); How i can use some extra variable with Key_GUID('CreditCards_Key') so that if some one has the key then still they cannot decrypt the field.

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.

So please guide me that how i can achieve those points i mentioned. If you are not getting my point please ask me again.

Any help will be appriciated.

Upvotes: 2

Views: 2814

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

The ENCRYPTBYKEY() function takes an optional authenticator as specified in the documentation. The stated purpose is to avoid wholesale replacement of one encrypted value by another when some intrinsic property of the plaintext is known (the cited example is an encrypted salary column and knowing that the CFO gets paid more than an employee in the copy room).

However, there's nothing preventing you from using this same mechanism to use a static authenticator not stored in the database as a sort of password. Failure to provide that authenticator at decryption time will result in a failure to decrypt.

Upvotes: 2

Related Questions