September 8, 2017 at 10:17 am
We have now finally moved all our customers to SQL 2014 from old or very old SQL versions and I have been given a task to enquire how to encrypt credit card numbers in the database.
Before I will start Google, thought to ask first people here what are the current approaches. Any suggestion will be much appreciated, as always
Thank you.
September 8, 2017 at 12:28 pm
My suggestion is that you start with Google. Google is great for the basics, forums like this are great for the nitty-gritty details. You're looking for basics, so use the tool that's good at the basics.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 8, 2017 at 12:38 pm
My recommendation is always to have the application handle encryption and decryption. If you use encryption builtin to SQL Server it makes it easier for people with access to the database to decrypt it.
I'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2017 at 1:07 pm
Column-level encryption is available in SQL Server.
If you can go to SQL Server 2016 (and your apps support it or can be changed to support it) there is a new feature called Always Encrypted.
Application-tier encryption is my best advice too because it scales-out the CPU drain that encryption entails. It will also work on any version of any RDBMS.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 8, 2017 at 2:41 pm
For credit card info specifically, you'll need to make sure you follow relevant laws on handling that data. Just SQL-based encryption methods is not enough, as I understand it, but I am not really that knowledgeable about this, and it's the law, so you'll want to research it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 9, 2017 at 6:17 am
Thank you all for your input - really appreciated!
September 9, 2017 at 6:24 am
Jack Corbett - Friday, September 8, 2017 12:38 PMI'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.
We are B2B organisation and our customers require us to periodically charge them, based on their settings, e.g. account number, credit card number or other meanings (industry's special credit cards)
September 11, 2017 at 8:09 pm
BOR15K - Saturday, September 9, 2017 6:24 AMJack Corbett - Friday, September 8, 2017 12:38 PMI'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.We are B2B organisation and our customers require us to periodically charge them, based on their settings, e.g. account number, credit card number or other meanings (industry's special credit cards)
We are as well - but our bank handles the CC info on our behalf. We give them a vendor ID - they go register themselves with our bank using that ID, and voila - we never see their card info.
----------------------------------------------------------------------------------
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?
September 20, 2017 at 10:24 am
BOR15K - Saturday, September 9, 2017 6:24 AMJack Corbett - Friday, September 8, 2017 12:38 PMI'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.We are B2B organisation and our customers require us to periodically charge them, based on their settings, e.g. account number, credit card number or other meanings (industry's special credit cards)
This is often a violation of CC/PCI principles unless you explicitly get certified to do this. A loss of data or a complaint could get you removed from your bank for processing transactions.
I would ensure that your company actually understands the implications of storing a CC number.
September 20, 2017 at 10:44 am
Steve Jones - SSC Editor - Wednesday, September 20, 2017 10:24 AMBOR15K - Saturday, September 9, 2017 6:24 AMJack Corbett - Friday, September 8, 2017 12:38 PMI'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.We are B2B organisation and our customers require us to periodically charge them, based on their settings, e.g. account number, credit card number or other meanings (industry's special credit cards)
This is often a violation of CC/PCI principles unless you explicitly get certified to do this. A loss of data or a complaint could get you removed from your bank for processing transactions.
I would ensure that your company actually understands the implications of storing a CC number.
I believe they did, hence my focus is on the actual solution.
September 20, 2017 at 10:58 am
If they did, that's fine. I'd be sure, since if there is an issue, they may hold you liable. Double check.
If you do this in the db, you need to decide which part is responsible. If it's the db, then column level encryption works fine. Be careful assigning permissions to certs, use certs to protect an asymmetric/symmetric key, and encrypt with that key.
If you want the app to be responsible, store the encrypted cc as a binary value and have the app manage the encryption/decryption with the .NET namespace. If you don't do this, then the CC is potentially being sent across the network to the app in an unencrypted fashion. That's often a violation of PCI requirements, hence my note.
Most business people think, we're not doing anything that's a big deal. One reason we have so many security issues.
September 20, 2017 at 11:07 am
Steve Jones - SSC Editor - Wednesday, September 20, 2017 10:58 AMIf they did, that's fine. I'd be sure, since if there is an issue, they may hold you liable. Double check.If you do this in the db, you need to decide which part is responsible. If it's the db, then column level encryption works fine. Be careful assigning permissions to certs, use certs to protect an asymmetric/symmetric key, and encrypt with that key.
If you want the app to be responsible, store the encrypted cc as a binary value and have the app manage the encryption/decryption with the .NET namespace. If you don't do this, then the CC is potentially being sent across the network to the app in an unencrypted fashion. That's often a violation of PCI requirements, hence my note.
Most business people think, we're not doing anything that's a big deal. One reason we have so many security issues.
Thank you, Steve!
September 20, 2017 at 12:17 pm
I'll try to blog or write a better article(s) on this stuff as well. I demo it, but don't have great pieces on it. I'll rectify. If you have questions, ask.
September 20, 2017 at 2:21 pm
For anything involving keys, you must have a proper key management procedure in place. It should be owned by your company's security department, not by developers or dba's. They get paid to do that, don't risk your job if something ends up not being bulletproof.
September 20, 2017 at 2:25 pm
Jack Corbett - Friday, September 8, 2017 12:38 PMMy recommendation is always to have the application handle encryption and decryption. If you use encryption builtin to SQL Server it makes it easier for people with access to the database to decrypt it.
I'd also be interesting in knowing why you are storing Credit Card information instead of using a service (there are few out there) to handle credit card transactions so you don't have to store that information.
+1000 to that. It also means that the transmission between the database and the GUI will also be encrypted to help seriously reduce any line sniffers.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply