June 3, 2016 at 6:50 pm
Hello,
I'm in the process of encrypting several fields in a SS2K8 R2 and would like some advise on best practices.
Is it within the scope of best practices to decrypt field in a view by Cert, then use that view as a base (FROM) for stored procedures?
Thank you!
David
June 4, 2016 at 3:05 am
In the end, you need to ask yourself why you are encrypting the data. That is, what do you want to protect yourself against, and what is out of scope?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 7, 2016 at 5:37 pm
I just took a job for a small company that holds NPI (non public information), but due to client requirements I have to treat it as SSN's. I'll write a bit about my plan and maybe someone can chime in.
I have a mirrored SQL 2008R2 backend with a Access (2007+) front end. I'm in the process of encrypting the ODBC connection using TLS 1.2. Still not how sure how to get the mirror to use TLS...
I also need column level encryption on several fields. My plan is to use a symmetric key encrypted by certificate. My users use Microsoft's mail merge based a data source to a view in the backend. So far my initial tests have shown that i can decrypt the data in the view via DecrpytByKeyAutoCert, but I don't know if there are any pitfalls to using this command. I've read more articles on encryption then i care to count and still have not been able to determine where a column is decrypted (server vs client side). I need to find a solution where the data is decrypted on the client side.
Any idea's? am I on the right path?
any help is always greatly appreciated,
David929595
June 8, 2016 at 1:11 am
The solution you have currently encrypts/decrypts server side - obviously. SQL Server functions do not run on the client.
If you want to encrypt client side and stay on SQL 2008 R2, you will have to find solutions outside SQL Server.
On the other hand, if you upgrade to SQL 2016, there is a new feature "Always Encrypted" where encryption occurs client-side, but the server knows that data is encrypted and therefore can prevent you from mixing encrypted and unencrypted data in the same column.
A few caveats: Always Encrypted may be available in Enterprise Edition only. You mentioned Access - I'm 100% sure that there is support in ODBC for Always Encrypted, or if it is only available in .NET.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 8, 2016 at 6:00 pm
Thank your for that clarification. You may have just made my case to upgrade to 2016 🙂
David92595
June 14, 2016 at 6:19 am
David92595 (6/8/2016)
Thank your for that clarification. You may have just made my case to upgrade to 2016 🙂David92595
Setting up and using server side encryption with SQL 2008 isn't that difficult. If you must only use TLS 1.2 than just insure you have the sufficient service pack upgrade INFO HERE
On the server side check the box labeled FORCE ENCRYPTION under protocols in the SQL Server Configuration Manager window (SQL Server Network Configuration for TCP/IP for your instance in question)
INFO HERE on how to set TLS 1.2 only for your server.
June 14, 2016 at 6:39 am
Smendle,
TLS is for securing the communication channel, so that no one can eavesdrop on the conversation with a network listener. But it does not encrypt any data in the database. I understood David's question that he wanted to store data encrypted.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 14, 2016 at 6:51 am
Erland,
Yes of course. For some reason I equated.
A few caveats: Always Encrypted may be available in Enterprise Edition only. You mentioned Access - I'm 100% sure that there is support in ODBC for Always Encrypted, or if it is only available in .NET
With Davids comment about moving to 2016...and so I ran with it as him being interested in TLS only encryption which is communication level only not TDE or database level encryption.
June 14, 2016 at 8:48 am
FWIW, the ODBC driver that will support Always Encrypted is in preview, not GA : https://msdn.microsoft.com/en-us/library/mt637351%28v=sql.110%29.aspx
As Erland mentioned, this is completely server side, so anyone with access to the server, and permissions to the cert (including sysadmin) can decrypt the data. If you want to limit this, I'm not sure how flexible Access is, but you could embed code to build a symmetric key in Access or use a password passed in to manage encryption/decryption.
Or go to AlwaysEncrypted in 2016. However, read the limitations. You need to move to a xx_BIN2 collation, which means case sensitivity, and you lose the ability to query encrypted columns with anything other than = or !=. If you can live within the limits, it's a nice technology.
June 14, 2016 at 1:50 pm
The issue with case-sensitivity and not being able to do range queries always applies if you encrypt data on cell level, no matter how you do it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 14, 2016 at 6:37 pm
Erland Sommarskog (6/14/2016)
The issue with case-sensitivity and not being able to do range queries always applies if you encrypt data on cell level, no matter how you do it.
Yes and no. Depending on how you structure things. Certainly I can decrypt in a query or use partial bucket hashing to reduce the amount of decrpytions I need to do in order to return results. Since column level happens on the server, I have flexibility (temp tables/table var/cte) to decrypt and evaluate data.
With Always Encrypted, I can't do that. I'd have to do query result filtering on the client.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply