July 20, 2009 at 12:48 pm
We're looking to ditch a third party vendor that encrypts sensitive information and either replace it with another one or use SQL 2008. The way the current system works is we have a set of tables with an encrypted column. Whenever data is inserted into that column, the app's .dll, installed with SQL, makes a request to a device in another location, gets the encryption key, and encrypts the password.
In order to be compliant with standards rushing towards us, we'd like to see if it's possible to implement it with SQL 2008. Simply having the column encrypted by SQL, with the master password belonging to the CFO and Security manager, for example, may not be enough. I'm reading up on Extensible Key Management (EKM) where the key is in a separate location, such as another server, etc. Is there a way setup authentication like this with two SQL Servers, one containing the encrypted data, and one containing the encryption keys in a more secure location? Or would I be stuck with third party apps and .dll's?
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
July 20, 2009 at 2:08 pm
It would be helpful if you could explain the actual requirement that you are trying to meet.
July 20, 2009 at 2:42 pm
Michael Valentine Jones (7/20/2009)
It would be helpful if you could explain the actual requirement that you are trying to meet.
Sure. Basically the requirement is that the Encryption Key be in a secured location from the actual encrypted data. What happens now is that when a value, for example CC#, comes in and an attempt is made to write to the encrypted column, a .dll is launched from the sql server, a request is made to a different location (in this case a unix box) that has the master keys over a secure connection. This will soon be corrected to use SSL between the data and application servers.
However, going forward for other data sensitive databases, maybe not as critical as Credit Card info, we'd like have something similar, but use the native SQL encryption and authenticate with an external keyserver. The encryption mechanism shifts from the third party .dll to SQL itself, but still authenticate with some external key source. Is it possible to have both servers be SQL Server or will the external source have to be an app that is at least compliant with SQL 2008 encryption?
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
August 13, 2009 at 7:28 am
First, I'm in no way affiliated with SafeNet. But yesterday I watched a webinar about their Luna SA product that may do what you want. At this this will give you some ideas of what is out there. Bascially using EKM, SQL accesses their Hardware Security Module which stores all of the keys you create for SQL Server. I'm not sure of the cost, but the configuration and setup of this sounds very easy.
Here are some links that may be useful.
http://www.safenet-inc.com/products/pki/lunaSA.asp
http://www.safenet-inc.com/Library/EDP/Microsoft_SQL_Server_009384-001_sql_server2008_luna_sa.pdf
August 14, 2009 at 3:31 am
You can get very effective encryption within SQL Server.
You can achieve complete PCI compliance using an in-house application using SQL Server.
You potentially add a very large risk to your organisation's survival by running an in-house PCI system.
Part of PCI compliane is agreeting to the PCI T&Cs. These include huge fines if useable CC details get leaked from your organisation. It does not matter if the leak comes from a hacker reading your database, or from someone loosing a printed report of the CC numbers you do business with.
The company I work for has decided that the business risk of holding CC numbers in-house outweighs any benefits we might gain from this information. Basically, the potential fine if all CC numbers became public is far larger than our stock market capitalisation, and we are a multi $billion company.
We have decided to offload this risk to a third-party. When a customer wants to make a CC payment we pass the (encrypted) transaction details to the payment company, who prompt for the CC number using their system. We get a token returned to say if the transaction amount was authorised or not.
The end result is slightly more clunky than if we captured the CC details ourself, but because we never know the CC number we can never be exposed to the risk of those details becoming public.
My advice is to do a full risk assessment before you decide to host a PCI system in-house.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 15, 2010 at 9:59 am
sorry to piggy back this topic but i'm in a similar situation. We currently encrypt data at the column level using certs / symmetric keys. As part of our PCI compliance we've been requested to change the certs and keys as they chap who created them no longer works here. Now, the issue is that we have 100's of millions of records to decrypt then re-encrypt with a new set of certs/keys, which as you can imagine will require a lot of out of hours work as well as testing.
Will sql 2008 key management handle this sort of thing? Or do i need a 3rd party vendor? I thought sql 2008 Key Management would do this but as far as i can tell its basically functionality to allow 3rd party vendors to plug into it...........or have i massively got the wrong end of the stick??
any thoughts or ideas are welcome
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
February 16, 2010 at 3:34 am
You should be able to change the certificates without impacting the encrypted data. If you need to change the encryption keys then I think you will need to re-encrypt.
Is this a good point to talk to the business about the need to retain CC numbers? If you do not store information that makes you need to be PCI compliant then your costs of doing business can go down a lot. The game is changing and keeping CC numbers is now more risky and costly then it was when the decision was made to keep the CC numbers.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 16, 2010 at 4:18 am
I believe we will have to regenerate the keys as well as when the guy who set these up did it, he used ENCRYPTION BY CERTIFICATE but neglected to supply the KEY_SOURCE and IDENTITY_VALUE so we can't re-create the keys.
Unless i've got this wrong as i'm not an expert in this area.......?
If there is a way to re-create the certs and re-use the syskeys then i'd be over the moon to hear!!!! 😀
Going forward, if i did have to do what i think i may (regenerate the lot), is there a way i can set things up so i don't have to do this next year? For example just regenerating the database master key?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
February 17, 2010 at 1:29 pm
You could look at:
SQL Server 2008 Compliance Guide
http://www.microsoft.com/downloads/details.aspx?FamilyId=6E1021DD-65B9-41C2-8385-438028F5ACC2
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply