4

GDPR is causing some headaches in this office. We already have a database table in production, lets call it personal_data, that now requires some columns to be encrypted. We are using SQL Server 2012. I've read that columns can be encrypted and decrypted with a symmetric key stored in the database.

We have dozens of existing queries, stored procedures and views that join to this table, so we'd like to avoid changing them if possible.

Is it possible to encrypt the necessary existing columns and query them without modifying these existing queries?

My thought was that if we renamed the personal_data table to something else, then created a view called personal_data, that queried the personal_data table columns and handled the decryption there, so everything that referenced 'personal_data' would still work as before. But if this is possible, what are the pitfalls with this solution?

Improve this question
1
  • 1
    I think you have a reasonable approach. I would also consider SQL2016 SP1 encryption options, especially always encrypted, that are available even in standard editions. This would put you at the latest and most compliant level in regards to security in my opinion. Commented Jan 24, 2018 at 13:41

2 Answers 2

Reset to default
2

I would suggest creating another table, say _personal_data. Encrypt the data in that table and replace the current table with a view on the table that returns acceptable columns.

You can give everyone access to the view, while restricting access to the underlying table.

This is a reasonable interim approach. For GDPR and other privacy initiatives, I prefer stronger restrictions, with personal data being in an entirely separate database -- because that is easier to control access to and to log accesses.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Great so I was on the right track then, I guess we'll need to recreate the table anyway to make the columns encrypted
How it works if we are using any encrypted column as foreign key reference to another table?
I don't know your database design, but foreign keys should not need to be encrypted to meet GDPR requirements.
For ex I am encrypting a column EmailId and it is foreign key reference to another table. How it will work? I cant able to encrypt a column in this case
@Melody . . . Then then other table needs to use the encrypted emailidl as the key.
1

SQL Server 2005 enables developers to encrypt and decrypt sensitive data using EncryptByKey and DecryptByKey functions You can find a sample case illustrated at SQL Server Database Encryption

But this requires code update for INSERT, UPDATE and READ statements For example,

SELECT
CONVERT(nvarchar, DecryptByKey(EncryptedData)) AS 'DecryptedData'
FROM myTable;

Instead of direct read as

SELECT EncryptedData AS 'DecryptedData' FROM myTable;

Another encryption method is SQL Server Transparent Data Encryption aka TDE. Once you enable it, you don't need to make any code changes to write and read data. But this is a protection for securing disk files at all not for specific data fields. And once you connect database with a valid connection all data is transparent to you.

Improve this answer

1 Comment

I was under the impression that TDE was only available in SQL Server 2016 but now I see that's not true, so this helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.