[RESOLVED] Encrypt data columns

[sapator]

Hi I'm reading on encryption in SQL 2012 by a column of data.

https://technet.microsoft.com/en-us/...=sql.110).aspx

I have encrypted and decrypted successfully so I have some questions here.

I create a master key , certificate and symmetric key and I can encrypt and decrypt. So you else is able to do that? Everyone that access the database?
This ain't much of an encryption if everyone that have access and know the certificate name and symmetric key (that can view in sys.object anyhow) can encrypt and decrypt.There must be something I am missing here.
Also if that is the case and the data can only be decrypted from this server, then how about when the database is moved? Can it be decrypted then?
I can't really understand the security per users here.

Thanks.

[sapator]

Ah.
OK I got a part of it.
You will create a master key that this is the one used for the encryption.
OK but again, anyone using the database in the server can decrypt.

Also what is the point if I can just use PASSPHRASE Encryption? It's triple DES also anyhow.

[sapator]

OK.
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?

[sapator]

On a little more read:
Not anyone can access the database data but specific roles.
Passphrase is exactly the same and easier to use but the issue is that somehow it must be passed in the decrypted values. A Master key is a "create and forget" type of key that you can keep exported to a file, if needed.
Passphrase comes more into my mind, when we are using a web app. You can have it inside the web server hidden from public and export this on the queries of SQL sproc's whenever needed.
This will make the database more mobile than having to backup - restore - drop master key and certificates and re-create. But again this would be an one time backup restore again.
Another implication with passphrase is (on what we are currently facing), giving the web development of the database to an external company. You will then have to provide them with the passphrase if you want to have correct encryption, or you will not and hide the passphrase inside your sproc's that, if the database is compromised, will have the visible password somewhere in there. Or you can force the running sproc's to collect the passphrase from somewhere else (p.e. , I'm pretty sure sql can read a text file somewhere well protected (well, "well" is relative).
So that's about it i think.
If anyone wants to add something feel free but it think this will make a starting reference if someone asks me about SQL Key encryption.

[sapator]

I need to add the the max encrypted value is the varbinary(8000) limitation.
That sucks because I may need to encrypt bigger sizes.
There is a demo function here I haven't tried that spits the data into chunks.
https://blogs.msdn.microsoft.com/yuk...h-limitations/

We need to be legit with the EU GDPR low so I'm not sure that I can have solutions outside of the current SQL AES - TRIPLE DES mechanisms. Maybe yes maybe not. I need to see our law agency for that