SQL Server 2016 - Always Encrypted

Question

SQL Server 2016 - Always Encrypted

Anil Kumar

SSC Veteran

Points: 245
More actions
September 5, 2019 at 12:00 am

#3672900

Comments posted to this topic are about the item SQL Server 2016 - Always Encrypted

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rob Newbie Points: 2 More actions · Answer 1

excellent article. So what prevents (or permits someone) from seeing the data if they use the encryption option in the connection. Do you have to send them (the client) the certificate that was created?

Anil Kumar SSC Veteran Points: 245 More actions · Answer 2

Yes, need to send/move those certificate to client (app or web server) machine.

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 3

Nice article, but I think it could need a second follow up article

In your example you created the certificate by yourself (you = SA).
The reason for Always Encrypted is, that the SA should never / no longer see the encrypted data.
Because you created the certificate by yourself this requirement would not be fulfilled (even if you delete the Certificate from your local Certificate store, the security guy could not be 100 % sure, that you not created a backup of the cert, that you could use later to access the secret informations again).
So how would you implement AE in a real project, where the SA / dbo are not permitted to know the content? Who would (and where / how) generate the certificate (and ensures that it does not get lost)?
How do you set up AE, if you have only those "external" certificates
what would you need to install/configure on the server to encrypt existing data? Or would / have you need to create an empty copy of the table (with encrypted columns) and run a
```
INSERT INTO <copy> SELECT * FROM <original>?
```
on client side?
Is there a suggested / good way to roll out the certificates on client side?
can I use different certificates for different columns in either different or even the same table? So the doctors can see all the medic data but the nurses only the most important (e.g. the birth day and medication but not the exact diagnostics and not the social ID)
I assume a yes for the question above and for this reason I guess that the certificates has to be installed on user level instead of the whole PC level (or can I specify in the connection string or better via permission rules, who is allowed to use which certificate)
What happens, when somebody changes department (e.g. from HR to sales) and must no longer access the payment data?
Certificates have usually a validation date. Can I easily switch the certificate (as for TDE)? What happens, if the date passed before someone replaced the certificate (for TDE the certificate duration will be ignored, so you can still use a certificate that experied two years ago)

God is real, unless declared integer.