Entire Database Encryption

Question

Entire Database Encryption

Vinayak15580

SSC-Addicted

Points: 436
More actions
September 26, 2017 at 12:20 am

#349351

Dear Experts,
My requirement is to encrypt entire database.
Even DBA does not view data from the database(Not talking about column level encryption). Use private key from HSM to view data from database.
Only application can access data with help of HSM.
Is there any solution meets my requirement. Please guide.
Thanks,

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

vinayakhupare 15580 - Tuesday, September 26, 2017 12:20 AM
Dear Experts,
My requirement is to encrypt entire database.
Even DBA does not view data from the database(Not talking about column level encryption). Use private key from HSM to view data from database.
Only application can access data with help of HSM.
Is there any solution meets my requirement. Please guide.
Thanks,

The easiest and quickest way is to upgrade to SQL 2016 and the use of Always Encrypted

Otherwise you are down to encrypting at column level

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

In fact, just about the only way you can have the DBA not see the data (without rewriting the app and bringing it to its knees with performance problems) is with client-side encryption. Always Encrypted is the easiest, but it is NOT whole database encryption and should not be. It should be sensitive data encryption (no point in encrypting columns that are country names for example, or guids, or similar)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vinayak15580 SSC-Addicted Points: 436 More actions · Answer 3

anthony.green - Tuesday, September 26, 2017 12:54 AM
vinayakhupare 15580 - Tuesday, September 26, 2017 12:20 AM
Dear Experts,
My requirement is to encrypt entire database.
Even DBA does not view data from the database(Not talking about column level encryption). Use private key from HSM to view data from database.
Only application can access data with help of HSM.
Is there any solution meets my requirement. Please guide.
Thanks,
The easiest and quickest way is to upgrade to SQL 2016 and the use of Always Encrypted
Otherwise you are down to encrypting at column level

Thank you for your quick reply.
Is there any option to achieve this in SQL server 2012. (Except column level.)

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

No, column level if you actually want to encrypt the data so only the application can see it, but be warned you will cripple your performance.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

vinayakhupare 15580 - Tuesday, September 26, 2017 3:22 AM
Thank you for your quick reply.
Is there any option to achieve this in SQL server 2012. (Except column level.)

Change the application so that it writes data encrypted to SQL and decrypts it in the application.
This will cripple your performance though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vinayak15580 SSC-Addicted Points: 436 More actions · Answer 6

anthony.green - Tuesday, September 26, 2017 3:33 AM
No, column level if you actually want to encrypt the data so only the application can see it, but be warned you will cripple your performance.

Yes, that's why I am looking for entire database encryption. So there is only way to switch to version 2016 for entire database encryption.. Please correct if I am wrong?
In 2016 we need to enable encryption option right? so data will saved in encrypted format only in every table.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Always Encrypted is not intended to be entire DB encryption, but to encrypt the data so that the admins can't read it. The idea is that you encrypt the sensitive portions of the DB.
The only 'full database encryption' is TDE, but that's file-level, encryption at rest, and anyone with access to the DB through SQL can read all the data.

What's the threat model here? What are you trying to protect against?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vinayak15580 SSC-Addicted Points: 436 More actions · Answer 8

GilaMonster - Tuesday, September 26, 2017 6:32 AM
Always Encrypted is not intended to be entire DB encryption, but to encrypt the data so that the admins can't read it. The idea is that you encrypt the sensitive portions of the DB.
The only 'full database encryption' is TDE, but that's file-level, encryption at rest, and anyone with access to the DB through SQL can read all the data.
What's the threat model here? What are you trying to protect against?

Thank you dear for your reply.

I had already tried TDE. But my requirement is, if anyone even administrator is fired select query, result should be encrypted format. Original data is not shown in text. Data should be access through application only with private key which is stored in HSM.

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Vinayak15580 - Tuesday, September 26, 2017 6:45 AM
GilaMonster - Tuesday, September 26, 2017 6:32 AM
Always Encrypted is not intended to be entire DB encryption, but to encrypt the data so that the admins can't read it. The idea is that you encrypt the sensitive portions of the DB.
The only 'full database encryption' is TDE, but that's file-level, encryption at rest, and anyone with access to the DB through SQL can read all the data.
What's the threat model here? What are you trying to protect against?
Thank you dear for your reply.
I had already tried TDE. But my requirement is, if anyone even administrator is fired select query, result should be encrypted format. Original data is not shown in text. Data should be access through application only with private key which is stored in HSM.

Then you need to look at doing column by column encryption, always encrypted, or getting the application to do it all in the application code. But your going to kill performance and dramatically increase the size of your database, through the use of VARBINARY columns.

What is the end goal / business requirements for this? What are you trying to protect so dearly? What is the threat?

I'm still dubious about doing full database encryption, why encrypt what is in the public domain, eg if you have an address table, why encrypt the country? A quick Google search will give the list of countries so why bother going through the headache of encrypting it. I could even go to Royal Mail here in the UK and download the full list of addresses, again so why bother encrypting it, now yes I would potentially encrypt the join values between person and address so you cant easily see who lives where but that's two columns to encrypt not two full tables.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Agreed. Full database encryption protects against theft of the DB file or backups. For preventing the admin from seeing sensitive data, you should identify the sensitive data and encrypt just that. Encrypting every single column of every single table is not generally recommended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

Vinayak15580 - Tuesday, September 26, 2017 6:45 AM
GilaMonster - Tuesday, September 26, 2017 6:32 AM
Always Encrypted is not intended to be entire DB encryption, but to encrypt the data so that the admins can't read it. The idea is that you encrypt the sensitive portions of the DB.
The only 'full database encryption' is TDE, but that's file-level, encryption at rest, and anyone with access to the DB through SQL can read all the data.
What's the threat model here? What are you trying to protect against?
Thank you dear for your reply.
I had already tried TDE. But my requirement is, if anyone even administrator is fired select query, result should be encrypted format. Original data is not shown in text. Data should be access through application only with private key which is stored in HSM.

It would help at this point to take a step back and think about who are in functional role of "DBA" and who technically needs to be in the SYSADMIN role, rather than trying to block the sysadmin from doing certain things. There does need to be an account of some type with full access to the data just in case something goes wrong. This doesn't need to be anyone's user level account, just a login kept locked away in a safe somewhere under the custody of the Data Steward, CIO, or whomever. If backups, monitoring, and deployments are all done through automated processes there may be no need for anyone to have ad-hoc login access to the server.
If all the DBA does on this server are operational tasks (backups, configuration settings, adding users, etc.), then they need not necessarily be in the full sysadmin role, there are other server level roles (like serveradmin or dbcreator) that grant specific permission admin privileges.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/media/permissions-of-server-roles.png

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

For this level of encryption It sounds to me that you should be looking at object or document stores, NOT a RDBMS. If the DB admin is not to read any part of the contents, then there is no point in storing anything in separate columns or tables for that matter.

Assuming you don't have any wiggle room on the requirement, then I'd look at open source document store type solutions. SQL Server will just get in your way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?