March 13, 2017 at 11:39 pm
Hi Guru,
I've a project which dealing with confidential data, such as payroll, medical information and etc. All these confidential data are in file format from different ERP system. I need to process all these files and generate an output to the downstream system. Therefore I need to build an architecture which can meet the security requirements. Here my idea that I can think so far. Any advice and suggestions will be greatly appreciated.
1) Having a dedicated server for this project
2) Control access to the server.
3) Having notification alert if someone logon to the server
4) Input files in encrypted format
5) Files in decrypt format while processing by SSIS
6) Output files in encrypted format
March 14, 2017 at 6:47 am
Also review best practices:
http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 14, 2017 at 7:05 am
https://msdn.microsoft.com/en-us/library/mt163865.aspx
It's a much more secure version of encrypted data baked into SQL Server itself. since you can secure the keys separately from the server, each column can be secured using a different key, etc...
We're just starting to kick the tires on it, but it looks promising so far.
----------------------------------------------------------------------------------
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?
March 14, 2017 at 1:26 pm
It sounds like this server will be a temporary hub or perhaps even a permanent datamart for confidential data. Therefore, I'd suggest implementing TDE on the database. Also, remember that the server running SSIS needs to be locked down too. SSIS may spool intermediate buffer data as files to disk.
Integration Services SSIS for the DBA
https://www.youtube.com/watch?v=VEgp5YHFLAc
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 15, 2017 at 3:16 am
Eric M Russell - Tuesday, March 14, 2017 1:26 PMIt sounds like this server will be a temporary hub or perhaps even a permanent datamart for confidential data. Therefore, I'd suggest implementing TDE on the database. Also, remember that the server running SSIS needs to be locked down too. SSIS may spool intermediate buffer data as files to disk.Integration Services SSIS for the DBA
https://www.youtube.com/watch?v=VEgp5YHFLAc
Thanks for your feedback. Can you provide more detail info about SSIS may spool intermediate buffer data to disk ?
March 15, 2017 at 4:16 am
Eric M Russell - Tuesday, March 14, 2017 1:26 PMIt sounds like this server will be a temporary hub or perhaps even a permanent datamart for confidential data. Therefore, I'd suggest implementing TDE on the database. Also, remember that the server running SSIS needs to be locked down too. SSIS may spool intermediate buffer data as files to disk.Integration Services SSIS for the DBA
https://www.youtube.com/watch?v=VEgp5YHFLAc
TDE in SQL Server 2012 has an issue whereby decrypted data can be read from the TempDB log (and discovered by card-sniffing software such as Card Recon). Is anyone aware if this issue has been fixed?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2017 at 7:05 am
Matt Miller (#4) - Tuesday, March 14, 2017 7:05 AMFrom a storage point of view - check out AlwaysEncrypted for SQL 2016https://msdn.microsoft.com/en-us/library/mt163865.aspx
It's a much more secure version of encrypted data baked into SQL Server itself. since you can secure the keys separately from the server, each column can be secured using a different key, etc...
We're just starting to kick the tires on it, but it looks promising so far.
Read carefully. Lots of limits here, which may or may not be an issue for your project.
One thing I'd say. AlwaysEncrypted is for cases where you don't trust the server, but you trust the client. Traditional encryption (TDE, column) is where you trust the server, not the clients.
March 15, 2017 at 7:08 am
ChrisM@Work - Wednesday, March 15, 2017 4:16 AMTDE in SQL Server 2012 has an issue whereby decrypted data can be read from the TempDB log (and discovered by card-sniffing software such as Card Recon). Is anyone aware if this issue has been fixed?
Haven't heard of this and don't see anything in searches. Have a reference? There were some tempdb issues in 2008, but wasn't aware of TDE holes in 2012.
March 15, 2017 at 7:13 am
Regarding AlwaysEncrypted, what version of SQL Server is this we're discussing?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 15, 2017 at 7:38 am
sqlbaby2 - Wednesday, March 15, 2017 3:16 AMEric M Russell - Tuesday, March 14, 2017 1:26 PMIt sounds like this server will be a temporary hub or perhaps even a permanent datamart for confidential data. Therefore, I'd suggest implementing TDE on the database. Also, remember that the server running SSIS needs to be locked down too. SSIS may spool intermediate buffer data as files to disk.Integration Services SSIS for the DBA
https://www.youtube.com/watch?v=VEgp5YHFLAcThanks for your feedback. Can you provide more detail info about SSIS may spool intermediate buffer data to disk ?
BufferTempStoragePath is where on disk SSIS will write buffer contents in the event that there is no available memory, and BLOBTempStoragePath is where on disk it will write any BLOB data in the buffer.
http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath/
Also, a couple of other security related design and configuration considerations:
Enable SSL to encrypt data that is transmitted across a network between an instance of SQL Server and a client application.
https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx
Restrict who has access to the SSIS package source code and take care when checking your SSIS packages into version control. Set Protection Level to keep passwords and other sensitive settings encrypted within SSIS packages.
https://msdn.microsoft.com/en-us/library/ms141747.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 15, 2017 at 8:16 am
Steve Jones - SSC Editor - Wednesday, March 15, 2017 7:08 AMChrisM@Work - Wednesday, March 15, 2017 4:16 AMTDE in SQL Server 2012 has an issue whereby decrypted data can be read from the TempDB log (and discovered by card-sniffing software such as Card Recon). Is anyone aware if this issue has been fixed?
Haven't heard of this and don't see anything in searches. Have a reference? There were some tempdb issues in 2008, but wasn't aware of TDE holes in 2012.
The nearest I can find is this, but it also relates to 2008. The symptoms are similar.
Card Recon found intact card numbers in TempDB log of 2012 (it's all we currently use). We used the simplest solution - anything which used a temp table to cache decrypted card numbers was changed to use a permanent table instead, problem resolved and tested. I'm told this was reported to MS at the time (the reporter is on vacation until 20th). I'll keep this open if you are interested.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2017 at 4:15 pm
Steve Jones - SSC Editor - Wednesday, March 15, 2017 7:05 AMMatt Miller (#4) - Tuesday, March 14, 2017 7:05 AMFrom a storage point of view - check out AlwaysEncrypted for SQL 2016https://msdn.microsoft.com/en-us/library/mt163865.aspx
It's a much more secure version of encrypted data baked into SQL Server itself. since you can secure the keys separately from the server, each column can be secured using a different key, etc...
We're just starting to kick the tires on it, but it looks promising so far.
Read carefully. Lots of limits here, which may or may not be an issue for your project.
One thing I'd say. AlwaysEncrypted is for cases where you don't trust the server, but you trust the client. Traditional encryption (TDE, column) is where you trust the server, not the clients.
It's for some JSOX and cyber sec requirements. You don't technically fully trust either, since the DB controls access to the data, but your operators and such don't have access to the data: you have to validate BOTH to actually get to read the data (Separation of duties). Like it or not - more data thefts come from the human side than the technological weaknesses, so this kind of setup does sing the sec auditor's tune. Whether that tune is in key or not is what we still have to prove.
But yes - I work for the service organization for multiple insurance companies. SQL 2016 is beyond the stuff we've even approved AND this this is newish, so it will go through lots of gyrations before ever seeing the light of day.
----------------------------------------------------------------------------------
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?
March 15, 2017 at 5:32 pm
ChrisM@Work - Wednesday, March 15, 2017 8:16 AMThe nearest I can find is this, but it also relates to 2008. The symptoms are similar.
Card Recon found intact card numbers in TempDB log of 2012 (it's all we currently use). We used the simplest solution - anything which used a temp table to cache decrypted card numbers was changed to use a permanent table instead, problem resolved and tested. I'm told this was reported to MS at the time (the reporter is on vacation until 20th). I'll keep this open if you are interested.
Please, I'd like to know. I hadn't seen any tempdb issues since SQL 2008, where there were some problems with decrypting from tempdb. If there are holes in the log, that's bad. It should be encrypted, though if you enable TDE, and don't reboot, your tempdb log and data can be partially encrypted, partially not.
March 16, 2017 at 2:28 am
Steve Jones - SSC Editor - Wednesday, March 15, 2017 5:32 PMChrisM@Work - Wednesday, March 15, 2017 8:16 AMThe nearest I can find is this, but it also relates to 2008. The symptoms are similar.
Card Recon found intact card numbers in TempDB log of 2012 (it's all we currently use). We used the simplest solution - anything which used a temp table to cache decrypted card numbers was changed to use a permanent table instead, problem resolved and tested. I'm told this was reported to MS at the time (the reporter is on vacation until 20th). I'll keep this open if you are interested.Please, I'd like to know. I hadn't seen any tempdb issues since SQL 2008, where there were some problems with decrypting from tempdb. If there are holes in the log, that's bad. It should be encrypted, though if you enable TDE, and don't reboot, your tempdb log and data can be partially encrypted, partially not.
Sure, I'll push for more details. We use TDE with column encryption on top for card numbers and a few other pieces of data. We've run Card Recon on a regular basis and TDE has been in use here for some years. AFAIK it was BAU too, not even certificate renewal - which at a stretch might seem a likely culprit.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 20, 2017 at 7:21 am
If I had a similar requirement, I would start by looking at legal and contractual requirements for storing confidential data, and design from there.
I understand that in the US any patient-related data is covered by HIPAA and servers holding such data must by law be HIPAA compliant. Other countries may have their own legal requirements but in their absense aiming for HIPAA could mitigate any penalties if your data gets hacked.
If you are process card numbers then you are bound by contract terms to be PCI compliant. Some organisations decide to never hold card numbers and use a merchant gateway as this avoids some of the expense of PCI.
If you have any defense-related data then there may be legal and contractual terms around that, such at STIGG in the US.
Finally, there may be local data protection laws, and also your business may have its own requirements that are additional to all of the above.
When you know what your target is, you can aim for it. If you do not know your target then you may fall short and leave you and your organisation exposed to penalties, or you may over-engineer and cost your organisation money it could use better elsewhere.
All the previous ideas I have seen in this post are tactics to help you reach a target. There is nothing wrong with any of them, but you do need to work with your management to agree a target so that you can aim in the right direction. Also, it may be that reaching your target is a multi-phase operation, with the business agreeing to take some risk in order to get the product to market and having a plan to fix it later.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply