July 13, 2012 at 4:41 pm
What is the best way to encrypt SSN's and FEIN Numbers (Employer Tax ID Number) in SQL Server 2008?
I want to restrict how can display this information.
Also I'm populating a Data Warehouse from Oracle and DB2 in SSIS PAckages and I'm concerned that the information could be compromised in the Data Transfer. What do I need to do to safeguard this information in the ETL Processes?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2012 at 2:59 am
Depends, what are you protecting against?
If a backup is restored, do you want to ensure that some 3rd party can't restore the backups?
Do you want to ensure that if some user accesses the database via their usual login they cannot see those columns
Do you want to ensure that non one, not even a sysadmin can see those columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2012 at 3:07 am
GilaMonster (7/14/2012)
If a backup is restored, do you want to ensure that some 3rd party can't restore the backups?
Yes.
Do you want to ensure that if some user accesses the database via their usual login they cannot see those columns
Yes.
Do you want to ensure that non one, not even a sysadmin can see those columns?
Certain users should have the ability to view the information, A sysadmin should not be able to view with information without decrypting.
I want to make sure that when the data is transferred from a remote Oracle Database that someone with a sniffer could not read the sensitive Data.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2012 at 5:41 am
TDE for the first (ensure backups can't be restored).
For the second, since you want to encrypt at source, you'll need some 3rd party encryption tool, or the use of encrypted connections. SQL column encryption won't help with the 'transfer encrypted' as it would only do the encryption at the destination (the SQL DB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2012 at 10:37 am
GilaMonster (7/14/2012)
For the second, since you want to encrypt at source, you'll need some 3rd party encryption tool, or the use of encrypted connections. SQL column encryption won't help with the 'transfer encrypted' as it would only do the encryption at the destination (the SQL DB)
Hi Gail and thanks for responding.
When you say for the second do you mean that I would need a 3rd party tool for the SSIS Transfer of Data?
Have you used any 3rd party that you would recommend?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2012 at 12:29 pm
Welsh Corgi (7/14/2012)
When you say for the second do you mean that I would need a 3rd party tool for the SSIS Transfer of Data?
As I said, SQL column encryption would not meet your requirements as it would encrypt at the destination, not the source. Hence you need something else to encrypt. I'm not familiar with any such tools (and you would have to ensure that the data can be decrypted as well.
Maybe a custom written SSIS component and a CLR procedure/function (using the same encryption method and key), but that's going to be work.
Worth investigating if you can encrypt the traffic across the network rather than the columns, that would simplify matters and allow you to use SQL column encryption.
I have asked a security specialist to have a look at this thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2012 at 12:34 pm
Thanks a lot Gail!:cool:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2012 at 4:55 am
Let’s discuss on the flow of the data first. It will highlight the area of threats & we might need to tighten up security there.
•Your source database is Oracle. (Assume you don’t have any Encryption enabled there or it’s out-of-your-control).
•Now you query this data and pull into SQL Server or SSIS dataflow. As Gail said you can have secured / encrypted connection here to avoid any sneak peek.
•After some processing or cleansing you dump it into SQL Server database. You must have TDE enabled on this data to avoid database restore possible without a valid key / certificate.
•You can encrypt couple of key (not database keys, but important) columns in database. So any user won’t be able to see the original (encrypted) value unless he has access to decryption function.
•This encrypted data can be pulled into the reports as well and can be decrypted there (if required).
I assumed some items here. If it doesn’t match with exact scenario, please correct me. I will amend my suggestions accordingly.
July 15, 2012 at 5:33 am
GilaMonster (7/14/2012)
Welsh Corgi (7/14/2012)
When you say for the second do you mean that I would need a 3rd party tool for the SSIS Transfer of Data?As I said, SQL column encryption would not meet your requirements as it would encrypt at the destination, not the source. Hence you need something else to encrypt. I'm not familiar with any such tools (and you would have to ensure that the data can be decrypted as well.
Maybe a custom written SSIS component and a CLR procedure/function (using the same encryption method and key), but that's going to be work.
Worth investigating if you can encrypt the traffic across the network rather than the columns, that would simplify matters and allow you to use SQL column encryption.
I have asked a security specialist to have a look at this thread.
Use IPSec to encrypt communications to the SQL server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2012 at 8:45 am
1) If you don't need true ENCRYPTION, then using a hash will be much better since you can still get seeks against a NC index on the column. This obfuscates the value from the casual observer, which can often be good enough for many purposes.
2) If you DO need true encryption, then I would recommend column level encryption instead of TDE, which seems like overkill and which carries other issues with it such as encrypting tempdb on the server. I would also have a column that is the last 4 or 5 digits of each column that is indexed so you can narrow the rows you need to decrypt on a seek of that column. Otherwise you have to scan the entire NC index and do a decryption when you seek a single value - and that gets expensive from both a performance AND concurrency perspective.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2012 at 9:16 am
As an alternative to implementing TDE to protect the backups, you could use a third party backup utility to encrypt the backups. The disadvantage is that you would have to purchase the utility. I believe Quest SQL Litespeed, Redgate SQL Backup, and Idera SQL Backup all support AES 256 encryption, the current standard for top secret.
This would also give you better compression of the backups since databases encrypted with TDE are not compressible. If you use TDE, there would be no compression benefit with third party tools.
July 17, 2012 at 7:18 am
Does someone know of a good white paper on the hash option to encryption?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2012 at 11:26 am
I'm looking for a very simple example on Data hashing in SQL Server.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2012 at 12:05 pm
Expert SQL Server 2005 Development. Not the 2008 one, the 2005 one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2012 at 1:28 pm
GilaMonster (7/17/2012)
Expert SQL Server 2005 Development. Not the 2008 one, the 2005 one.
Oh shucks my Student free delivery for FedEx & UPS (United Parcel Service) for two day delivery just ran out the end of last month.
The USPS (United States Postal Service) stinks.
Thanks!:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply