SQLTeam.com | Weblogs | Forums

Encrypt column of data


#1

Hi I'm reading on encryption in SQL 2012 by a column of data.
https://technet.microsoft.com/en-us/library/ms179331(v=sql.110).aspx
I have seen that you must create a master key and a certificate.
I'm cannot understand however the decryption.
There is no indication of passing some key and if is not the case (probably) then how the permissions are used in order to decrypt?

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
GO
.......etc
 CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))  . 

OK but how do we know who has permissions to decrypt?
Any help?
Thanks

Also I would like to encrypt per value entered and not the whole column. Do I need to create a trigger that will encrypt the inserted value? If, so how? There is only a column encryption example here.
I could use PASSPHRASE encryption. I have done it in the past but I prefer to use something new and - thus more secure.


#2

So from what I can make out:
You can have your database with encrypted values but anyone that is accessing the database, based on an SQL Server that has the correct master key can pretty much decrypt everything.
If you move the database somewhere else then you must create an exact duplicate of the master key else the database cannot be decrypted.
Is this the case?
If that is so, then why is this better than passphrase? With passphrase you cannot decrypt the database unless you know the password, and provide it.
So even if the database is on you main server and everyone can view it, it cannot be decrypted without the passphrase.

Any thoughts?